relationship question

[Jessica]

Hello All,

I have two tables my main table has a primary key called UPC and other
fields called cap pk number, container pk number, label pk number etc...
my second table called materials has a primary key called pk number and
a field called description. My report needs the pk number for the
container, cap, and label along with their description, is this possible
with it setup like this or should I separate each material into their
own table and setup a relationship using the pk number from each table
to the main table.

Thank you,
Jess

 

[Ken Snell [MVP]]

Assuming that your second table contains the data you need, you would use a
query that has copies of the second table as sources:

SELECT Table1.UPC, T2_1.Description, T2_2.Description,
T2_3.Description FROM ((Table1 INNER JOIN Table2 AS T2_1
ON Table1.[cap pk number]=T2_1.[pk number]) INNER JOIN
Table2 AS T2-2 ON Table1.[container pk number]=T2_2.[pk number])
INNER JOIN Table2 AS T2_3 ON Table1.[label pk number]=
T2_3.[pk number];

 

[Jessica]

As embarrassing as it sounds I spent two hours today trying to figure
that out and was oh so close. Thank you soooooooo much Ken.

Jess

 

[Ken Snell [MVP]]

You're welcome... the good point is we really remember the things on which
we worked hard... it's the answers that we're given that tend to "slip away
into memory fog", so your work is not going to go to waste!
< g >

--

Ken Snell
<MS ACCESS MVP>

As embarrassing as it sounds I spent two hours today trying to figure that
out and was oh so close. Thank you soooooooo much Ken.

Jess

Assuming that your second table contains the data you need, you would use
a query that has copies of the second table as sources:

SELECT Table1.UPC, T2_1.Description, T2_2.Description,
T2_3.Description FROM ((Table1 INNER JOIN Table2 AS T2_1
ON Table1.[cap pk number]=T2_1.[pk number]) INNER JOIN
Table2 AS T2-2 ON Table1.[container pk number]=T2_2.[pk number])
INNER JOIN Table2 AS T2_3 ON Table1.[label pk number]=
T2_3.[pk number];