Table Quandry

[DS]

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....

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

I tried this...

MenuID
MenuCatID

MenuCatID
ItemID

ItemID
ModCatID

ModCatID
ModID

....as tables, records were not matching up though.
Any help or suggestions appreciated.
Thank You
DS

 

[Vincent Johns]

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 Datasheet 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 <[email protected]>
Please feel free to quote anything I say here.

 

[DS]

Well, one suggestion I have is to get rid of all the Primary Keys except
one, and that one I suggest hiding in Table Datasheet 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!



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.



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.



-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks, I got it now....I set Up a couple of Queries and that did the trick.
Thanks
DS

 

[Vincent Johns]

DS wrote:

[...]

Thanks, I got it now....I set Up a couple of Queries and that did the
trick.
Thanks
DS

You're welcome ... Once your Tables are set up (with properly linking
keys), you can do all sorts of things with Queries; they're pretty
versatile.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.