using information in multiple rows 2

[netooi25]

Hi, I have a little problem i need some help with...

Ok, I have a table (tblSite for example)that has a site and then a bunch of id's for that site...

example tblSite

Site ID1 ID2 ID3 ID4
home 001 002 003 004
work 005 003 007
other 007 008 010 025
(there can be more rows, its not a fixed number of rows)

I need to build a query or temporary table or something
that gets all the id numbers listed in this table..
and stores them in a single column w/out duplicates.

ex. tblTemp

ID
001
002
003
004
005
007
008
010
025

Is there an easy way to do this and does anyone have any suggestions or alternatives? It doesnt have to be stored in a table. I just think it would be easy to work with that way. I need access to all the possible Id's though.
Thanks for any help
Netooi25

 

[SkipVought]

Netooi25,

Your basic problem is that your Table is NOT Normalized (id data in multiple columns)

You Table ought to be structured
[tt]
Site
Id
[/tt]
A way to accomplish this is to do a UNION query...

Select Site, ID1
From tblSite
Where ID1<>""
UNION
Select Site, ID2
From tblSite
Where ID2<>""
UNION
Select Site, ID3
From tblSite
Where ID3<>""
UNION
Select Site, ID4
From tblSite
Where ID4<>""

Use these results to Make Table

From there, its a snap to get the results that you want.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

gets all the id numbers listed
Something like this ?
SELECT ID1 As ID FROM tblSite WHERE Len(Trim(Nz(ID1,"")))>0
UNION SELECT ID2 FROM tblSite WHERE Len(Trim(Nz(ID2,"")))>0
UNION SELECT ID3 FROM tblSite WHERE Len(Trim(Nz(ID3,"")))>0
UNION SELECT ID4 FROM tblSite WHERE Len(Trim(Nz(ID4,"")))>0
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[netooi25]

Thanks for the union tips guys. I appreaciate it and it helped a bit, giving me some direction to start. I ended up just using vb code to take care of it becuase i have 40 id fields out there and the union query was giving me problems, but its a good technique to know. I realize that its not normalized and that makes things a pain a little, but i'm not the one responsible for changing it so i gotta work with this. Thanks again for all the help.
Netooi