relation question

[Jessica]

Hello All

I have a main table which has records listed by a UPC number (primary
key). In that table a UPC may have more than one case sticker up to four
and many UPC can have the same case sticker. I set up four fields
labeled Case Sticker 1, Case Sticker 2, ..... in my UPC table. I have
another table with every case sticker item in stock. How do I relate
these two tables, or do I really need too. My goal is to have a report
show the case stickers that correlate to each UPC and if there is a
change with the sticker then I can change it in the case sticker table.

TIA,
Jess

 

[John Vinson]

Hello All

I have a main table which has records listed by a UPC number (primary
key). In that table a UPC may have more than one case sticker up to four
and many UPC can have the same case sticker. I set up four fields
labeled Case Sticker 1, Case Sticker 2, ..... in my UPC table. I have
another table with every case sticker item in stock. How do I relate
these two tables, or do I really need too. My goal is to have a report
show the case stickers that correlate to each UPC and if there is a
change with the sticker then I can change it in the case sticker table.

You need to reconsider your table design. Any time you have fields
named Foo1, Foo2, Foo3 and Foo4 you are violating relational design
principles! Someday you'll need a FIFTH case sticker, and then what do
you do - redesign your database?

If you have a one (UPC) to many (case sticker) relationship, model it
as a one to many relationship using two tables. If each case sticker
applies to one and only one UPC, then simply put the UPC into the case
sticker table as a foreign key. You can then use a Form based on the
main table, with a Subform (or Report/Subreport for printing) showing
all the stickers for each UPC.

John W. Vinson[MVP]

 

[Jessica]

John

Thank you for your reply. What if one case sticker applies to many UPCs
and vice versa then what are my options. Do I just make extra fields in
my UPC table and just list all the case stickers that apply to that UPC
and not have a relationship between the UPC and the case sticker tables.


TIA,
David

 

[Todd Shillam]

Jessica,

It appears like you need a many-to-many relationship. To do this in
Microsoft Access, you create what is called a "Junction" table--there should
be some information about junction tables in the help file within Access.

In short, you would have three tables: a table for UPCs, a table for case
stickers, and a junction table.

The junction table commonly has two fields (though you can have more), they
are used to store the ID number from each of the two tables.

tblLINK
======
UPC_ID - number
casestickerID - number

By using a junction table, you can have a case sticker related to many UPCs
or you can have a UPC related to many case stickers; provided you create the
relationships between the three tables.

Respectfully,

Todd

 

[Jessica]

Thanks so much Todd. I'll see if I can find out about junction tables
using Access help.

Thanks again,
Jess

 

[John Vinson]

John

Thank you for your reply. What if one case sticker applies to many UPCs
and vice versa then what are my options. Do I just make extra fields in
my UPC table and just list all the case stickers that apply to that UPC
and not have a relationship between the UPC and the case sticker tables.

Then you need ANOTHER TABLE to resolve the many to many relationship.
It should have fields for the UPC number and the Case number; you'ld
add a record to this table for every UPC in a case, and for every case
that a UPC is in.

John W. Vinson[MVP]