Forum Discussion
Linking data on tables
Hi
I am very new to access, and watching lots of videos at the moment. I wondered though does anyone know how I can get two bits of information onto every table. I need the unique ID that access automatically assigns, as well as a unique code that the service assigns. Is there a way for this to appear on every table if I input it into one or into a form?
Thanks
Ros
3 Replies
In a relational database table, it's crucial to have a Unique ID field in every table. This field is the Primary Key. It is used only internally by Access. Your users should not see, or even be aware of its existence.
That is usually the AutoNumber which you mentioned, although other methods of assigning or creating that unique value for a Primary Key are available. I'd recommend sticking with the AutoNumber.
Your other unique code is for public consumption. Users see it. It is used outside the database application. You generate the values for it either externally or via a VBA function. Apparently you create and assign these unique codes externally. When you add them to the table, you can create a Unique Index on that field. It's not the same as the Primary Key, but giving it a Unique Index ensures that no two people will have the same code.
When you relate records in two different tables, you use only the Primary Key. It is stored in the related table, where it is designated as a Foreign Key. YOU DO NOT REPEAT THE OTHER, UNIQUE CODE IN THE RELATED TABLE. It's not necessary, and it could quite readily lead to other problems, so don't try.
In other words, you do not need, or want, "two bits of information in every table" in a properly designed relational table. You use only the Primary/Foreign Key relationship. If you need to display that Unique Code anywhere else, you include it in a query that joins the two tables.
Generally speaking, when working with one-to-many tables in Access, you use a main form/subform design. The one-side table is bound to the main form. The many-side table is bound to the subform. And the subform control has a property, Master/Child linking fields, which handles the foreign keys in the records in the child table in the subform.Again, repeating your UniqueCode in two different tables is not only unnecessary, it would be counter-productive in the long run.
- Tell us more about the service. What is it? How does it generate codes? Should every record in every table have a unique service code?
Gerrit_Viehmann yea everyone in the service gets a unique number which is always theirs and never changes. People can come in and out the service but that is always their number. So we need something else to represent point in time - which we think could be the automated access number which we would call a referral number. So we have a main table - but we need the two numbers (the first two columns) to be copied to each table when a new row is added. Does that make sense?
