469,644 Members | 1,966 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,644 developers. It's quick & easy.

Creating a Perishable Goods Database

I am trying to create a perishible goods database. I have several items that have expiration dates and I wish to track these.

So far I have created the following tables:
-contains part name and stock number

Expiration Date
-the date of expiration

-where the item is located

Purchase Order
-the purchase order number that item was bought from

Now my problem that I'm having is getting access to form the relationships I need. I have same stock numbers with different expirations, same purchase orders with multiple parts. I think I am having problems with defining the proper key.
Any ideas??
Aug 15 '08 #1
2 1380
Stewart Ross
2,545 Expert Mod 2GB
Hi, and Welcome to Bytes!

It isn't any help with Access you need at this stage but with relational table design and normalisation. We have an article on this very topic in our howTo section linked here.

In essence, where a table is involved in a one-many relationship the primary key from the one-side table is carried forward as a foreign (or secondary) key in the many-side table. You have none of these 'carry-forward' relationships in your tables so far.

You may also benefit from reading a good introductory text on relational database design. You can also have a look at the relationships of the sample Northwind database which is supplied with Access, as there are not that many tables in the example application and all relationships are suitably defined.


Aug 15 '08 #2
Apart from a very good article posted by Msquared, There's a good article on this topic in the Microsoft Knowledge Database. Search for "Understanding Relational Database Design".
Aug 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Dim St Thomas | last post: by
7 posts views Thread by John Baker | last post: by
11 posts views Thread by Webster | last post: by
7 posts views Thread by Gladiator | last post: by
2 posts views Thread by neela | last post: by
reply views Thread by tradeinputian.005 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.