Combine 2 queries

[Ielamrani]

I already posted this question once but I do not think it went through, so I am trying a second time.


I have 2 queries. I called them qrySend and qryFailed

qrySent has the following

Fax Jan Feb Mar
212-547-6547 5 6 2
516-547-6053 5 1 20
212-200-6547 78 20 15


qryFailed has the following

Fax Jan Feb Mar
212-547-6547 4 0 1
516-547-6053 1 1 0
212-200-6547 7 11 3

As you can see the fax is the same in both tables so I am using it as a unique id.

What I would like to do is make the 2 queries (qrySend and qryFailed) into one (qryfinal) and the result should be:

qryfinal

Fax Jan Feb Mar
212-547-6547 5 6 2
4 0 1

516-547-6053 5 1 20
1 1 0

212-200-6547 78 20 15
7 11 3

The idea is to put the numbers from qrySent on top of those from qryFailed in qryfinal

I hope this is not confusing

Thanks in advance

Ismail

 

[PHV]

SELECT Fax, 'A' AS SortOrder, Jan, Feb, Mar FROM qrySent
UNION ALL SELECT Fax, 'B', Jan, Feb, Mar FROM qryFailed
ORDER BY 1,2

Hidding the 2nd fax number and not showing the SortOrder field is a presentation problem solved for example by a report.

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

 

[Ielamrani]

I posted the same question in Section General database discusion but I tought it did not go through so I posted it twice. Remou gave me the following answer:

SELECT qryFailed.Fax,"Failed" As SentFailed, qryFailed.Jan, qryFailed.Feb, qryFailed.Mar
FROM qryFailed
Union
SELECT qrySent.Fax,"Sent", qrySent.Jan, qrySent.Feb, qrySent.Mar
FROM qrySent

And it did exactly what I was looking for. I am also going to try your way so I learn a different way.
Thank you very much.
You guys are the best
Ismail