AutoNumber Primary key as Foreign Key

Question

_{Thursday, August 19, 2010 7:56 PM}

Is there anyway to have an AutoNumber(Primary Key) field ie. list of Employe IDs populate  another List Say Employee Activities, where all entries entered by those Employees would be recorded by use of the Employee ID (as a foreign keys with Duplicates Allowed) using Access 2007  

All replies (5)

_{Thursday, August 19, 2010 8:03 PM ✅Answered}

Yes, it's possible; but it's a very bad idea to create a whole bunch of empty "placeholder" records.

When you have an activity to enter for an employee, just enter it - you can use a Form based on the Employees table, with a Subform based on an EmployeeActivities table. This latter table would have a Long Integer (not autonumber) EmployeeID, and you'ld use EmployeeID as the master/child link field of the subform; when there is data to enter into the activities table (but not before), it will inherit the currently chosen employee's ID.

John W. Vinson/MVP

_{Thursday, August 19, 2010 8:08 PM ✅Answered}

TopLight,

yes, of course you can do that - easily.

That relational database theory 101.

tblEmployees

EmpID  AutoNumber (primary Key)

EmpName  text(255)

tblEmpActivities

EmpActiveID   AutoNumber  (primary Key)

EmpID    Long Integer  (foreign Key into tblEmployees)

etc.,.

open the Relationships  view (Access 2003,  Tools->Relationships...)

and connect the tblEmployees.EmpID to tblEmpActivites.EmpID (I would recommend that you check the box to enforce reletional integrity also)  - Personally, I would also check cascade changes, but not cascade deletes, but that is up to you.

_{Friday, August 20, 2010 4:02 PM ✅Answered}

On Thu, 19 Aug 2010 20:08:03 +0000, MarkB_08109 wrote:

>Personally, I would also check cascade changes, but not cascade deletes, but that is up to you.

In this case, there is no need for Cascade Updates, because an

Autonumber value will not change.

Armen Stein

Microsoft Access MVP

www.JStreetTech.com

_{Thursday, August 26, 2010 1:58 PM ✅Answered}

You can then use a query involving the two tables, with a JOIN, and enter the data trough that query (as recordsource of a form). Access will perform the  required auto-lookup (if the query is updateable, that is), as for existing data as well as for data you newly append, dynamically. This is a Jet feature, right out of the box, and take note, for comparison, that MS SQL Server, as example, does not allow appending data in two different tables trough a view (unless you also add INSTEAD OF triggers).

_{Thursday, August 26, 2010 3:03 PM}

Armen,

That is true, generally, but I do it anyway because, well, of habit if for no other really good reason. (somewhere I seem to recall that I once had a situation where doing this actually saved me from a destroyed dataset headache where data was being shuffled around form one Db to another or something unusual like that was happening, and one of the target tables was not an Autonumber field...)