Forum Discussion
Textbox to Filter Listbox
George_Hepworth,
Thank you so much for your detailed and concise answer.
I understand Primary keys insomuch as they are a unique value that is used to create relationships between tables. When I initially started fooling around with Access database I began by breaking down databases that my company was already using, none of which have ever used Primary keys. So, while I understand their functionality as a theory, I'm not entirely sure how to utilize them in practice.
I understand what you're saying as far as creating different tables and that will definitely be something I change with the database I had started. I guess my confusion with utilizing a primary key is with how that connects to other tables. Forgetting about the dates, let's say:
Item# = 123ABC
UPC = 123456789
Order# = 987654
Tracking# = 4561239
With Table1 containing both the Order# and the Tracking# and Table2 containing the Item# and UPC. I could use the Order# as the primary key, but how does that relate to Table2? I will have multiple items on a single order but your primary key cannot repeat. I could also use either the Item# or UPC as the Primary Key, but we have instances where an item will be ordered and sent out on more than one occasion, and again, I can't have a repeating primary key.
A field in a table which is identified as the Primary Key uniquely identifies each record in that table.
If, for example, you are looking at the "Items" table, and if you have designated "ItemNumber" as the Primary Key, then 123ABC can appear once and only once in the Item table.
On the other hand, when you want to ORDER an item, that primary key becomes the Foreign Key in the OrderDetail table. One or more orders can include Item 123ABC. One item is included in many orders. Primary Key 123ABC from the Item table relates to several instance of Foreign Key 123ABC in all of those order details.
The reason I refer to an Order table as well as an Order Detail table is that each order, I assume, can include one or more items. If that is not the case, if each order is for one and only one item, then the ItemNumber can be in the Order table. However, it is far more common to have multiple items in an order.
The OrderDetail table has two foreign keys. One of those is the OrderNumber, as discussed above,the other is the ItemNumber, as discussed above. This is a many-too-many relationship. One item can be in many orders. One order can contain many items.
You will not repeat Primary Keys in any table, as you note. However the same VALUE (e.g. 123ABC) which is the Primary Key in the item table appears as Foreign Keys multiple times in an OrderDetail table.
Again, I would recommend using the Northwind Sample database as a sort of template, or study guide.