How do I union these queries into one?

jonathanchye · Apr 5, 2011

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.

DCrake · Apr 5, 2011

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]);

jonathanchye · Apr 5, 2011

DCrake said:

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?

DCrake · Apr 5, 2011

Does the result return totals for each month for each year? And yes you couldhave 2 columns for each year.

jonathanchye · Apr 5, 2011

DCrake said:
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]);

DCrake · Apr 5, 2011

Ok Save the query as it is and then do a crosstab query based on that query.

Months down Years Across

jonathanchye · Apr 5, 2011

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?

jonathanchye · Apr 6, 2011

/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

How do I union these queries into one?

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

DCrake

Remembered

jonathanchye

Registered User.

jonathanchye

Registered User.

Similar threads

Users who are viewing this thread