Retrieving one to many in different columns

[Stan Leeds]

Hi All,

I have an access database were one table stores the idnum and names of
individuals. Then I have a table were column idnum1 references to
column location1 and column idnum2 references column location2.

I don't know which idnum1 or idnum2 would store the information to make
them link to the idnum. How can I make a query to either link correctly
or create a make table to retrieve what location an idnum may be?

Thanks in advance,
Stan

 

[Ken Snell \(MVP\)]

You're storing your data in an unnormalized structure, which is why you're
having a problem querying your data. You should have a single field for
IDNum and a field for column number; instead of two fields for IDNum (one
for IDNum1 and one for IDNum2).

To query your exsiting data, a union query would be one approach:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN
Table2 ON Table1.IDNum = Table2.IDNum1
UNION
SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN
Table2 ON Table1.IDNum = Table2.IDNum2;