Autonumbering and Foreign Keys

[equipe9]

I have several tables as follows.

Department
DepartmentID(Primary Key)
DepartmentName(Text)
MgrEmployeeNumber(Text)
Foreign Key from Manager Table.

Manager
MgrEmployeeNumber(PrimaryKey)
LastName(Text)
FirstName(Text)

Employee
UserEmployeeNumber(PrimaryKey)
MgrEmployeeNumber(ForeignKey)
DepartmentID(ForeignKey)

etc....

See

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=36418

for more details about the structure of the database.

I'm trying to redo the Department table so that DepartmentID will be an autonumber, field but when I went ahead and did so and tried to re-create the relationship I got the following error.

"Relationship must be on the same number of fields with the same data types. (Error 3368)"

It seems like Access wants me to make the foreign key DepartmentID in the Employee table an autonumber field to match data types.

But, I don't really think this is possible.

Any ideas on how I can make DepartmentID an autonumber field in the Department Table while still maintaining my relationship with the employee table?

Thanks.

 

[pono1]

E,

Assuming the DepartmentID has been successfully converted to autonumber in the Dept Table, then the DepartmentID in the Employee Table should be a Long Number. If the Employee table resists this change, remove the FK field entirely, close the table, break the relationship, re-open the table and add the DepartmentID field (number, long), then close again and re-establish the relationship. (WARNING: I am assuming there is no data in your tables.)

Also, off point, you could, I think, combine the manager and employee tables into one table if you wish, creating a field in the newly merged table called, say, Status, with entries like "Manager," "Co-manager," "Peon," etc. (Another table, called, say, TblStatus, could be set up to hold the various positions and used to fill a status combo box on an Employee form.) If you have the Northwind database on your PC, see its Employee table for an example.

Regards,
Tim

 

[equipe9]

Thanks for your help Tim. I moved the data to a backup table, made a new table with the Autonumber field, imported the data into my new table, changed the foreign keys from text to number, and reset the relationships and everything works perfectly now.

Thanks again for your help.

 

[pono1]

Glad to hear you worked it out.

Tim