Number incrementing (1 Viewer)

T

terry518

New member

Local time

Today, 05:35

Joined

Mar 26, 2004

Messages

9

• Apr 13, 2004

• #1

Okay I have a form that gathers employee information and I need to take down an employee ID that you just use the next available number. Is there any way that I can make it so that when you are entering a new employee the ID number automatically puts in the next available number. It can't be autonumber because the ID links the tables and I need to be able to have duplicates in the other table.

Thanks
Terry

 

dcx693

Registered User.

Local time

Today, 05:35

Joined

Apr 30, 2003

Messages

3,265

• Apr 13, 2004

• #2

terry518 said:

It can't be autonumber because the ID links the tables and I need to be able to have duplicates in the other table.

Click to expand...

Why are duplicates in the other table a problem? How would entering the next number get around that problem?

 

T

terry518

New member

Local time

Today, 05:35

Joined

Mar 26, 2004

Messages

9

• Apr 13, 2004

• #3

There are duplicates in the other table because the other table holds training information for the employee's entered. It uses those ID numbers to link the tables and people will have multiple types of training. So there are multiple entries in that table under the one employee ID number.

 

dcx693

Registered User.

Local time

Today, 05:35

Joined

Apr 30, 2003

Messages

3,265

• Apr 13, 2004

• #4

How is it a problem in your database?

 

T

terry518

New member

Local time

Today, 05:35

Joined

Mar 26, 2004

Messages

9

• Apr 13, 2004

• #5

It's to make it more convenient for the users here that arent very computer literate

 

dcx693

Registered User.

Local time

Today, 05:35

Joined

Apr 30, 2003

Messages

3,265

• Apr 13, 2004

• #6

I'm sorry, I'm missing the problem here. You're saying you want to grab the next available number for use as an employeeID. You're saying you can't or don't want to do this because the employees can have mutiple entries for training info.

Unless you've already got lots of information in the database (in which case, using an autonumber would entail changing pre-existing info), I don't see the design problem. You have a 1 to many relationship.

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 05:35

Joined

Feb 19, 2002

Messages

43,263

• Apr 14, 2004

• #7

The training table needs a multi-field primary key. The key should be EmployeeID, TrainingID, and TrainingDate. The date is included to allow a person to attend the same training class more than once if necessary.

To create a multi-field key -
Open the table in design view
Select the first key field
Hold the Cntl key and select each additional field (up to a total of 10)
When all three fields are highlighted, press the key icon on the toolbar.

Properly defining the key for the training table will allow you to use an autonumber as the EmployeeID.