How to concatenate two similar queries to form one [bit of a newbiequestion]

  • Thread starter Thread starter Andrew Magerman
  • Start date Start date
A

Andrew Magerman

Hi,

I was wondering if you could help me:

I have one large table for my addresses. It looks like this:


Name Land phone Mobile phone Office phone
Andrew 1111111 222222222 3333333333
Tom 5111111 522222222 5333333333
Harry 8111111 822222222 8333333333


And I want to import one file (csv)into my phone at home which only
understands Name and Phone Number. So I need this table:

Name Phone
Andrew 1111111
Andrew Mob 222222222
Andrew Office 3333333333
Tom 5111111
Tom Mob 522222222
Tom Office 5333333333

In VBA with excel, I would loop through the table and create a csv on
the fly, and I couldn't quite understand how to do it for Access.

Then I thought, OK, let's make three queries and just concatenate them.

Query 1:
Name phone
Andrew 1111111
Tom 5111111
Harry 8111111

Query 2:
Name phone
Andrew Mob 222222222
Tom Mob 522222222
Harry Mob 822222222

Query 3:
Name phone
Andrew Office 3333333333
Tom Office 5333333333
Harry Office 8333333333


I managed the three queries but couldnt get to stick them together. Can
you help or tell me of an even better way?

Thanks,
Andrew 8-)
 
Create a UNION query. It's basically 3 select queries, with the word UNION
(or preferably UNION ALL) between them.

UNION queries are not updatable, and cannot be represented graphically. To
create one, switch your query to SQL View (View menu in query design), and
paste the 3 of them together. You will end up with something like this:

SELECT [Name], [Land phone], 'Land' AS PhoneType
FROM [Table1] WHERE [Land phone] Is Not Null
UNION ALL
SELECT [Name], [Mobile phone], 'Mobile' AS PhoneType
FROM [Table1] WHERE [Mobile phone] Is Not Null
UNION ALL
SELECT [Name], [Office phone], 'Office' AS PhoneType
FROM [Table1] WHERE [Office phone] Is Not Null
ORDER BY [Name];
 
Back
Top