How to join results of 3 queries 1

[blyssz]

Hello All,

I need to join the results of 3 SQL queries in MS ACCESS.
I have several open cases of certain priorities (Emergency(1), High(2), Medium(3), Low(4), Very Low(5))and the date on which the case was opened date too.
I am trying to find out the total number of open cases by their age( time period) for each priority and the age group is
# of cases opened <30 days (from today)
# of cases opened between 30-60days
# of cases opened before 60 days

So I want a table similar to this :
Open case Aging by Priority

< 30 Days 30 - 60 60+
1 0 0 0
2 2 3 6
3 11 11 16
4 5 1 2
5 0 1 0


I wrote 3 queries for each of the case but not able to join their results in one table.
Query that I am writing is (where condition changes according to the requirement):
SELECT ([Table_name].Col_name) AS Priority, Count([Table_name].Col_name) AS <30-60
FROM [Table_name]
WHERE ((([Table_name].OpenDate)<=Date()-30 And ([Table_name].OpenDate)>=Date()-60))
GROUP BY [Table_name].PriorityText;

I am getting the following result for each query:
Agingbypriority

Priority <30

Emergency 1
High 28
Medium 53



agingbypriority1
Priority 30-60

Low 4
Medium 30
Very Low 1



agingbypriority2
Priority 60+

Emergency 1
High 2
Low 3
Medium 13
Very Low 1


Now I need to join these 3 into one to get the desired output.
I tried outer join but could not do it. Do I need to change the query ???
Any suggestions will be appreciated.

Thanks in advance,
Blyssz

 

[PHV]

Have a look at UNION query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blyssz]

Yes I did but that will concatenate the rows from second table to first table and will keep the same column name as first table.

Thanks,
Blyssz

 

[danvlas]

priority
Left join query1 on priority
Left join query2 on priority
Left join query3 on priority

should do the trick


Daniel Vlas
Systems Consultant

 

[blyssz]

Thanks a lot Daniel. It worked.

Blyssz