How do I union these queries into one?

jonathanchye

Registered User.
Local time
Today, 23:09
Joined
Mar 8, 2011
Messages
448
Hi all,

I have two queries which basically is the same (from same table) but having different criterias. How do I merge them into one single query which uses Month as the common field?

Below are the SQL code for both my queries :

Query 1 :

Code:
SELECT Format([EnquiryRec],"mmmm") AS [Month], Format([EnquiryRec],"yyyy") AS 2008, Count(tblTrack.EnquiryRec) AS CountOfEnquiryRec
FROM tblTrack
GROUP BY Format([EnquiryRec],"mmmm"), Format([EnquiryRec],"yyyy"), Month([EnquiryRec])
HAVING (((Format([EnquiryRec],"yyyy"))='2008'))
ORDER BY Month([EnquiryRec]);
Query 2 :

Code:
SELECT Format([EnquiryRec],"mmmm") AS [Month], Format([EnquiryRec],"yyyy") AS 2009, Count(tblTrack.EnquiryRec) AS CountOfEnquiryRec
FROM tblTrack
GROUP BY Format([EnquiryRec],"mmmm"), Format([EnquiryRec],"yyyy"), Month([EnquiryRec])
HAVING (((Format([EnquiryRec],"yyyy"))='2009'))
ORDER BY Month([EnquiryRec]);
Many thanks.
 
You don't need a union query

Code:
SELECT Format([EnquiryRec],"mmmm") AS [Month], Format([EnquiryRec],"yyyy") AS [B]TheYear[/B], Count(tblTrack.EnquiryRec) AS CountOfEnquiryRec
FROM tblTrack
GROUP BY Format([EnquiryRec],"mmmm"), Format([EnquiryRec],"yyyy"), Month([EnquiryRec])
HAVING (((Format([EnquiryRec],"yyyy")) [B]In('2008','2009')[/B]))
ORDER BY Month([EnquiryRec]);
 
You don't need a union query

Code:
SELECT Format([EnquiryRec],"mmmm") AS [Month], Format([EnquiryRec],"yyyy") AS [B]TheYear[/B], Count(tblTrack.EnquiryRec) AS CountOfEnquiryRec
FROM tblTrack
GROUP BY Format([EnquiryRec],"mmmm"), Format([EnquiryRec],"yyyy"), Month([EnquiryRec])
HAVING (((Format([EnquiryRec],"yyyy")) [B]In('2008','2009')[/B]))
ORDER BY Month([EnquiryRec]);

Thank you! This did improve things however I am still getting double month values ..

Is there a way to change "TheYear" to 2008 and add another column called 2009?
So I guess you would also need 2 counts, one for 2008 and one for 2009?
 
Does the result return totals for each month for each year? And yes you couldhave 2 columns for each year.
 
Does the result return totals for each month for each year? And yes you couldhave 2 columns for each year.

Yes. The results look something like :

January 100
January 250
February 80
February 90

and so on but I would like it to look like :
--------08------09
January-100----250
February-80-----90

This is the closest I've got so far but no idea how to add 09 counts..

Code:
SELECT Format([EnquiryRec],"mmmm") AS [Month], Count(tblTrack.EnquiryRec) AS 2008
FROM tblTrack
GROUP BY Format([EnquiryRec],"mmmm"), Format([EnquiryRec],"yyyy"), Month([EnquiryRec])
HAVING (((Format([EnquiryRec],"yyyy"))='2008'))
ORDER BY Month([EnquiryRec]);
 
Ok Save the query as it is and then do a crosstab query based on that query.

Months down Years Across
 
Edit : Never mind found a way to do it! :) Thank you.

Is there a possible way to add an extra criteria? For example, I want the query to perhaps now only show values where the field [Live] from the table is True?
 
Last edited:
/bump

Really hope someone could help me out here :(

Right now I have a crosstab query with the following format :

Month ---- TheYear1---TheYear2---TheYear3
Month1
Month2
Month3

Can I have something like :

Month ---- TheYear1---TheYear2---TheYear3
-------------Yes---No---Yes-No-----Yes-No
Month1
Month2
Month3
 

Users who are viewing this thread

Back
Top Bottom