Combine 2 queries into 1

[Andy79]

Hi,

I have setup 2 separate queries, but would really rather show all the
data in a single query. However i am unsure how to specify a different
"where" clause for each item i want to count.

Is it possible to write this as 1 single query?

here are the queries

1
SELECT Format$([Conferences].[StartDate],'yyyy-mm') AS [StartDate By
Month], Count(Conferences.Conference_Title) AS CountOfConference_Title
FROM Conferences INNER JOIN [CR Commissioning] ON
Conferences.Conference_ID = [CR Commissioning].Conference_ID
WHERE (((Conferences.Commission) Is Not Null))
GROUP BY Format$([Conferences].[StartDate],'yyyy-mm');

2
SELECT Format$([Conferences].[StartDate],'yyyy-mm') AS [StartDate By
Month], Count([CR Commissioning].[AU Agreed]) AS [CountOfAU Agreed]
FROM Conferences INNER JOIN [CR Commissioning] ON
Conferences.Conference_ID = [CR Commissioning].Conference_ID
WHERE ((([CR Commissioning].[CM Status])="agreed"))
GROUP BY Format$([Conferences].[StartDate],'yyyy-mm');


Regards
Andy

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could include the Conference_ID in each query and then JOIN them
like this:

SELECT Q1.[StartDate ByMonth],
Q1.CountOfConference_Title,
Q2.[CountOfAU Agreed]
FROM
(SELECT C.Conference_ID, Format$(C.[StartDate],'yyyy-mm') AS [StartDate
ByMonth], Count(C.Conference_Title) AS CountOfConference_Title
FROM Conferences As C INNER JOIN [CR Commissioning] As CR ON
C.Conference_ID = CR.Conference_ID
WHERE C.Commission) Is Not Null))
GROUP BY C.Conference_ID, Format$(C.[StartDate],'yyyy-mm')) AS Q1

INNER JOIN

(SELECT C.Conference_ID, Format$(C.[StartDate],'yyyy-mm') AS [StartDate
ByMonth], Count(CR.[AU Agreed]) AS [CountOfAU Agreed]
FROM Conferences As C INNER JOIN [CR Commissioning] AS CR ON
C.Conference_ID = CR.Conference_ID
WHERE CR[CM Status]='agreed'
GROUP BY Format$(C.[StartDate],'yyyy-mm')) As Q2
ON Q1.Conference_ID = Q2.Conference_ID


The next way is slower 'cuz the IIf() function has to be evaluated for
each row (record), but...

SELECT Format$(C.[StartDate],'yyyy-mm') AS [StartDate By Month],

SUM(IIf(C.Commission IS NOT NULL And C.Conference_Title IS NOT
NULL,1,0)) AS CountOfConference_Title,

SUM(IIf(CR.[CM Status]='agreed' And CR.[AU Agreed] IS NOT NULL,1,0)) As
[CountOfAU Agreed]

FROM Conferences As C INNER JOIN [CR Commissioning] As CR ON
C.Conference_ID = CR.Conference_ID
GROUP BY Format$(C.[StartDate],'yyyy-mm');

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZHwv4echKqOuFEgEQLd9ACgkEjyS51gIY8zH9Ss+LbY3EszQk8An0Zs
akR8TsE3TksGS3DUWmvuO+eT
=kpMN
-----END PGP SIGNATURE-----

Hi,

I have setup 2 separate queries, but would really rather show all the
data in a single query. However i am unsure how to specify a different
"where" clause for each item i want to count.

Is it possible to write this as 1 single query?

here are the queries

1
SELECT Format$([Conferences].[StartDate],'yyyy-mm') AS [StartDate By
Month], Count(Conferences.Conference_Title) AS CountOfConference_Title
FROM Conferences INNER JOIN [CR Commissioning] ON
Conferences.Conference_ID = [CR Commissioning].Conference_ID
WHERE (((Conferences.Commission) Is Not Null))
GROUP BY Format$([Conferences].[StartDate],'yyyy-mm');

2
SELECT Format$([Conferences].[StartDate],'yyyy-mm') AS [StartDate By
Month], Count([CR Commissioning].[AU Agreed]) AS [CountOfAU Agreed]
FROM Conferences INNER JOIN [CR Commissioning] ON
Conferences.Conference_ID = [CR Commissioning].Conference_ID
WHERE ((([CR Commissioning].[CM Status])="agreed"))
GROUP BY Format$([Conferences].[StartDate],'yyyy-mm');

 

[Andy79]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could include the Conference_ID in each query and then JOIN them
like this:

SELECT Q1.[StartDate ByMonth],
        Q1.CountOfConference_Title,
        Q2.[CountOfAU Agreed]
FROM
(SELECT C.Conference_ID, Format$(C.[StartDate],'yyyy-mm') AS [StartDate
ByMonth], Count(C.Conference_Title) AS CountOfConference_Title
FROM Conferences As C INNER JOIN [CR Commissioning] As CR ON
C.Conference_ID = CR.Conference_ID
WHERE C.Commission) Is Not Null))
GROUP BY C.Conference_ID, Format$(C.[StartDate],'yyyy-mm')) AS Q1

INNER JOIN

(SELECT C.Conference_ID, Format$(C.[StartDate],'yyyy-mm') AS [StartDate
ByMonth], Count(CR.[AU Agreed]) AS [CountOfAU Agreed]
FROM Conferences As C INNER JOIN [CR Commissioning] AS CR ON
C.Conference_ID = CR.Conference_ID
WHERE CR[CM Status]='agreed'
GROUP BY Format$(C.[StartDate],'yyyy-mm')) As Q2
ON Q1.Conference_ID = Q2.Conference_ID

The next way is slower 'cuz the IIf() function has to be evaluated for
each row (record), but...

SELECT Format$(C.[StartDate],'yyyy-mm') AS [StartDate By Month],

SUM(IIf(C.Commission IS NOT NULL And C.Conference_Title IS NOT
NULL,1,0)) AS CountOfConference_Title,

SUM(IIf(CR.[CM Status]='agreed' And CR.[AU Agreed] IS NOT NULL,1,0)) As
[CountOfAU Agreed]

FROM Conferences As C INNER JOIN [CR Commissioning] As CR ON
C.Conference_ID = CR.Conference_ID
GROUP BY Format$(C.[StartDate],'yyyy-mm');

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZHwv4echKqOuFEgEQLd9ACgkEjyS51gIY8zH9Ss+LbY3EszQk8An0Zs
akR8TsE3TksGS3DUWmvuO+eT
=kpMN
-----END PGP SIGNATURE-----

Hi,

I have setup 2 separate queries, but would really rather show all the
data in a single query. However i am unsure how to specify a different
"where" clause for each item i want to count.

Is it possible to write this as 1 single query?

here are the queries

1
SELECT Format$([Conferences].[StartDate],'yyyy-mm') AS [StartDate By
Month], Count(Conferences.Conference_Title) AS CountOfConference_Title
FROM Conferences INNER JOIN [CR Commissioning] ON
Conferences.Conference_ID = [CR Commissioning].Conference_ID
WHERE (((Conferences.Commission) Is Not Null))
GROUP BY Format$([Conferences].[StartDate],'yyyy-mm');

2
SELECT Format$([Conferences].[StartDate],'yyyy-mm') AS [StartDate By
Month], Count([CR Commissioning].[AU Agreed]) AS [CountOfAU Agreed]
FROM Conferences INNER JOIN [CR Commissioning] ON
Conferences.Conference_ID = [CR Commissioning].Conference_ID
WHERE ((([CR Commissioning].[CM Status])="agreed"))
GROUP BY Format$([Conferences].[StartDate],'yyyy-mm');

Thanks this really helped my understand the issue here

Regards
Andy