Merging/Eliminating Redundant Columns

[Housing Intern]

Hello,
I am creating a query from several different tables containing housing data.
My final goal is to create one query that contains a single column with the
"housing ID number" along with all of the other housing information in each
row (address, owner, management...) Due to poor data management, this
"housing ID number" is missing from several of my tables.
By running a series of queries from all these different tables, a have a
datasheet that has three columns with ID numbers. Some rows have all the
data, some do not. As an example of my columns:

ID1 ID2 ID3 Property_Name
321 321 321 Greystone
244 244 Buena Vista
545 Moore

Since sometimes the original tables DID have the ID number, it shows up two
or three times, like in the example above. Other times, this ID number was
missing in one or more tables. I want a final table that gets rid of these
redundant ID numbers, so the final product looks like:

ID Property_Name
321 Greystone
244 Buena Vista
545 Moore

Any help will be greatly appreciated. Thank you.

 

[Jerry Whittle]

SELECT ID1 AS ID, Property_Name
FROM TABLE_NAME
WHERE ID1 IS NOT NULL
UNION
SELECT ID2 AS ID, Property_Name
FROM TABLE_NAME
WHERE ID2 IS NOT NULL
UNION
SELECT ID3 AS ID, Property_Name
FROM TABLE_NAME
WHERE ID3 IS NOT NULL ;

 

[Housing Intern]

Thank you, that worked beautifully. One more problem...Some of the
properties did not have an ID number in any of the three columns. When I ran
the SQL, it eliminated these properties. What can I add to the SQL so that
it will still keep these properties even though they do not have an ID
number? Thanks again.

 

[John Spencer]

Add one more query to the end
SELECT ...

UNION
SELECT ID1, Property_Name
FROM TABLE_NAME
WHERE ID1 is Null and ID2 is Null and ID3 Is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..