Re: Table Quandry

DS wrote:

I have a problem setting a Table(s)

I tried seperating things but it seems that this is all thats working or perhaps I'm missing something.

Heres what I have.....They are all Primary Keys escept the last 2 fields....

Well, one suggestion I have is to get rid of all the Primary Keys except one, and that one I suggest hiding in Table Data*** View so that you don't see it. Its main purpose should be to provide a value identifying the record so that you can use that same value in other Tables (as Foreign Keys) to refer to this record.

Access will allow you to specify a Primary Key that consists of several fields, but that does NOT mean that it's a good idea to do that!

MenuID
MenuCatID
ItemID
ModCatID
ModID
PrepID
PrintID


Each MenuID can have many MenuCatID's
Each MenuCatID can have many ItemID's
Each ItemID can have many ModCatID's
Each ModCatID can have many ModID's

Then there's not lots of sense in keeping them all in the same Table. Let each Table keep track of one kind of record.

I tried this...

MenuID
MenuCatID

MenuCatID
ItemID

ItemID
ModCatID

ModCatID
ModID

...as tables, records were not matching up though.

They won't automatically match up. You'll have to make them match (e.g., during data entry) by specifying (via a Foreign Key field) which the matching record is supposed to be, since Access doesn't yet possess a mind-reading function (though I wouldn't mind having such a feature). I usually link them by specifying Lookup properties in foreign-key fields that allow me to hide the key values, displaying instead something more meaningful, such as an item's name, perhaps sorted alphabetically.

Any help or suggestions appreciated.
Thank You
DS


  -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.
.