Possibility of consolidating 2 date based queries into 1

[bd528]

Hi,

I have 2 queries as per below that count 3 sets of data from one table by date, and then group by month. The queries are :-

SELECT Format([Date_sent_to_Registrations],"mmm-yy") AS Period, Count(tblQuotesNew.Date_sent_to_Registrations) AS xCount, Format([Date_sent_to_Registrations],"yyyy-mm") AS xmonth, "DateToReg" AS Code
FROM tblQuotesNew
GROUP BY Format([Date_sent_to_Registrations],"mmm-yy"), Format([Date_sent_to_Registrations],"yyyy-mm"), "DateToReg", tblQuotesNew.Quote_Status
HAVING (((Count(tblQuotesNew.Date_sent_to_Registrations)) Is Not Null) AND ((tblQuotesNew.Quote_Status)="Accepted"));
union
SELECT Format([Requested_SSD],"mmm-yy") AS Period, Count(tblQuotesNew.Requested_SSD) AS CountOfRequested_SSD, Format([Requested_SSD],"yyyy-mm") AS xmonth, "ReqSSD" AS Code
FROM tblQuotesNew
GROUP BY Format([Requested_SSD],"mmm-yy"), Format([Requested_SSD],"yyyy-mm"), "ReqSSD", tblQuotesNew.Quote_Status
HAVING (((Count(tblQuotesNew.Requested_SSD)) Is Not Null) AND ((tblQuotesNew.Quote_Status)="Accepted"));
UNION 
SELECT Format([Actual_SSD],"mmm-yy") AS Period, Count(tblQuotesNew.Actual_SSD) AS CountOfActual_SSD, Format([Actual_SSD],"yyyy-mm") AS xmonth, "AccSSD" AS Code
FROM tblQuotesNew
GROUP BY Format([Actual_SSD],"mmm-yy"), Format([Actual_SSD],"yyyy-mm"), "AccSSD", tblQuotesNew.Quote_Status
HAVING (((Count(tblQuotesNew.Actual_SSD)) Is Not Null) AND ((tblQuotesNew.Quote_Status)="Accepted"));

and

TRANSFORM Sum(qryAllo1.xCount) AS SumOfxCount
SELECT qryAllo1.Period, qryAllo1.xmonth
FROM qryAllo1
WHERE (((qryAllo1.Period) Is Not Null) AND ((qryAllo1.Code) Is Not Null))
GROUP BY qryAllo1.Period, qryAllo1.xmonth
ORDER BY qryAllo1.xmonth
PIVOT qryAllo1.Code;

Is it possible to get the same results but from just one query. The way I see it, the first query is there just to generate a list of dates when each instance occurs. It may not be possible, but gut instance tells me there may be a way to simplify what I'm doing.

Thanks in advance.

 

[JHB]

It may be missing normalizing of the data!
If [Date_sent_to_Registrations], [Requested_SSD] and [Actual_SSD] are field in the same table, then you can't improve the queries.

 

[bd528]

It may be missing normalizing of the data!
If [Date_sent_to_Registrations], [Requested_SSD] and [Actual_SSD] are field in the same table, then you can't improve the queries.

They are from the same table, so that's good enough for me. Thank you.