How do I handle several look ups to same table

[Teddy]

I have a table [ShirtColorTbl] and it looks up from [InkTbl] 3 differnt fields [tone], [twrap], and [contrast]. They all look at the same ink table but pull different colors in each field. I want to handle referential integrity and cascade update and delete. Can I do this?

 

[Duane Hookom]

Can you provide more information on your table structures and their
relationships? I believe you can do this. It would probably take you less
than 15 minutes to create a couple sample tables and do your own testing.
Then you could report your results back to us ;-)

--
Duane Hookom
MS Access MVP

I have a table [ShirtColorTbl] and it looks up from [InkTbl] 3 differnt

fields [tone], [twrap], and [contrast]. They all look at the same ink table
but pull different colors in each field. I want to handle referential
integrity and cascade update and delete. Can I do this?

 

[Teddy]

ShirtColorTbl has
Style (key), ColorCode (Key), ColorDescription, ToneOnTone (Look up InkTbl), ContrastColor (Look up InkTbl) , TwrapColor (Look up InkTbl)

InkTbl has
InkColor (Key) -> basically a look up table only so that i only have to enter the ink colors once

InkColor has a 1 to Many relation and as you can see it is looked up by three different columns. everything works fine until I try to establish referential integrity. At that time it tells me it cannot inforce it in this relationship

Thanks

 

[Duane Hookom]

I don't have a handle on the printing business but I would consider removing
the color fields and placing them in a related table. The related table
would have a field for values like "ToneOnTone", "Contrast", "Twrap".

--
Duane Hookom
MS Access MVP

ShirtColorTbl has:
Style (key), ColorCode (Key), ColorDescription, ToneOnTone (Look up

InkTbl), ContrastColor (Look up InkTbl) , TwrapColor (Look up InkTbl).

InkTbl has:
InkColor (Key) -> basically a look up table only so that i only have to enter the ink colors once.

InkColor has a 1 to Many relation and as you can see it is looked up by

three different columns. everything works fine until I try to establish
referential integrity. At that time it tells me it cannot inforce it in
this relationship.

 

[DDM]

Teddy, the short answer to your original question (can you do three
one-to-many relationships) is yes, you can. You would set them up exactly
the same way you would set up a single one-to-many relationship. If Access
is not letting you enforce referential integrity, I suspect it is because
there are records in the ShirtColorTbl that violate referential integrity;
that is, there are color values in some records that do not exist in the Ink
table. As an exercise, you might try setting up the relationships in new,
empty tables.
--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

ShirtColorTbl has:
Style (key), ColorCode (Key), ColorDescription, ToneOnTone (Look up

InkTbl), ContrastColor (Look up InkTbl) , TwrapColor (Look up InkTbl).

InkTbl has:
InkColor (Key) -> basically a look up table only so that i only have to enter the ink colors once.

InkColor has a 1 to Many relation and as you can see it is looked up by

three different columns. everything works fine until I try to establish
referential integrity. At that time it tells me it cannot inforce it in
this relationship.

 

[Teddy]

DDM, thanks that is exactly why I am asking. My table does not have any data and it won't let me set it up. I'm sure it is something I am doing...but i cant figure out why. If you want to set it up with the same fields and try it I would appreciate it. I belive there is enough information to easily try. Please let me know what you find or else I will just break those three items out of the shirt color table and put them all into thier own table using a look up to Ink. It just seems like it should be easier. Thanks, Teddy

 

[tina]

i tested proposed your setup last evening after reading the thread; had no
trouble setting up the relationships. if you want to compact, zip and email
an empty (no proprietary data) copy of your db to me, i'll look at it and
get back to you, today if possible.
reference the newsgroups in the subject line, paste the entire thread in the
body (so i remember what this is about, and what's been said/done) and email
to ttacc_kill_allspam_ess1 at yahoo dot com, removing the
underscores and all text *between* them.

DDM, thanks that is exactly why I am asking. My table does not have any

data and it won't let me set it up. I'm sure it is something I am
doing...but i cant figure out why. If you want to set it up with the same
fields and try it I would appreciate it. I belive there is enough
information to easily try. Please let me know what you find or else I will
just break those three items out of the shirt color table and put them all
into thier own table using a look up to Ink. It just seems like it should
be easier. Thanks, Teddy