Query Question

[Bob Vance]

If I have a table with say 10 Fields, Client1--->Client10, How can I create
a query to list all clients in to one column from all 10 fields?

 

[Tom Wickerath]

Hi Bob,

Use a Union query. Something like this example, which covers your first five
columns. You need to substitute the proper table name:

SELECT Client1 As Client
FROM MyTable
WHERE Client1 Is Not Null

UNION

SELECT Client2 As Client
FROM MyTable
WHERE Client2 Is Not Null

UNION

SELECT Client3 As Client
FROM MyTable
WHERE Client3 Is Not Null

UNION

SELECT Client4 As Client
FROM MyTable
WHERE Client4 Is Not Null

UNION

SELECT Client5 As Client
FROM MyTable
WHERE Client5 Is Not Null

ORDER BY Client


Note that the ORDER BY clause goes at the very end. Here is a mini tutorial
that you can download on Union queries:

http://www.accessmvp.com/TWickerath/downloads/unionqueries.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Bob Vance]

Thanks Tom for your effort BRILLIANT
Regards Bob

 

[Tom Wickerath]

Hi Bob,

Glad it worked for you. Now you can be the brilliant person who helps the
next person that posts a similar question. <smile>

I didn't mention this, but another alternative to using just UNION is to use
UNION ALL. The difference in the two forms is that using UNION only will
eliminate duplicates, whereas UNION ALL will literally union ALL of the data
together. So, you may or may not need to use UNION ALL to get the recordset
you really need.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________