Using 2 Autonumbers in 1 table

[UnknownJoe]

I have 2 tables (Registrations and Courses), where each table has a Primary
Key with a data type of Autonumber (RegID and CourseID). At some point within
my DB, I need to add the PK from the Courses table to the Registration table
which causes problems (only 1 Autonumber per table).

In order to avoid this problem, I would like to change the Data Type for the
PK in the Courses table, but with allowing the user to automatically create
the value of each record (generating a custom CourseID - i.e. Course01,
Course02, Course03, etc).

Any ideas on how to create the custom field for data entry purposes?
Thanks.

 

[Jeff Boyce]

Access allows one Autonumber field per table.

If you want to use the number that uniquely identifies a Course in your
Registration table (so as to point back to the course-registered-for), you
need to brush up on "foreign keys". That is, a number in [Registration]
that points back to the number in [Course].

In Access, you do this by setting the datatype of that foreign key to Long
Integer (which is what an Autonumber is).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

 

[Klatuu]

What you are describing is a classic many to many relationship. This is
resolved with what is known as a Junctoion table.
It needs two fields, both Long Integer. One will carry they primary key of
the registration it belongs to and the other the primary key of the course it
belongs to. You then use this table in queries to join the correct course ot
its registration.