If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using 2 Autonumbers in 1 table
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. |
#2
|
|||
|
|||
Using 2 Autonumbers in 1 table
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 "UnknownJoe" wrote in message ... 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. |
#3
|
|||
|
|||
Using 2 Autonumbers in 1 table
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. -- Dave Hargis, Microsoft Access MVP "UnknownJoe" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|