Retrieving one to many in different columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stan Leeds - 27 Apr 2008 02:21 GMT

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

Reply to this Message

Ken Snell (MVP) - 27 Apr 2008 20:04 GMT

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;

Signature

Ken Snell
<MS ACCESS MVP>

> Hi All,
>
[quoted text clipped - 8 lines]
> Thanks in advance,
> Stan

Reply to this Message

Stan Leeds - 27 Apr 2008 20:41 GMT

Ken,
Thanks, it retrieved the info I wanted but opened another small can of
worms. I think to reduce logic checking and such, I'll just APPEND the
one table twice to extract the two different locations and run simpler
queries. I did learn from this though and I appreciate the help on a
legacy system.

Stan

Subject:
Re: Retrieving one to many in different columns
From:
"Ken Snell \(MVP\)" <kthsneisllis9@ncoomcastt.renaetl>
Date:
Sun, 27 Apr 2008 15:04:12 -0400
Newsgroups:
microsoft.public.access.queries

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;

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

Reply to this Message