Option to merge query (using subquery)?

[bd528]

Hi,

I have 2 queries :-

SELECT tblBrokeragesNew.ID, tblBrokeragesNew.Brokerage_Name
FROM tblQuotesNew RIGHT JOIN tblBrokeragesNew ON tblQuotesNew.Brokerage_ID = tblBrokeragesNew.ID
GROUP BY tblBrokeragesNew.ID, tblBrokeragesNew.Brokerage_Name
HAVING (((Count(tblBrokeragesNew.DateTPIAgreementSigned))>0)) OR (((Count(tblQuotesNew.Date_sent_to_Registrations))>0));

SELECT [qryBDM/TPIReport1].Brokerage_Name, Format([Date_sent_to_Registrations],"mmm-yyyy") AS Expr1, Count(tblQuotesNew.Date_sent_to_Registrations) AS CountOfDate_sent_to_Registrations, Format([Date_sent_to_Registrations],"yyyymm") AS SortDate
FROM [qryBDM/TPIReport1] LEFT JOIN tblQuotesNew ON [qryBDM/TPIReport1].ID = tblQuotesNew.Brokerage_ID
GROUP BY [qryBDM/TPIReport1].Brokerage_Name, Format([Date_sent_to_Registrations],"mmm-yyyy"), Format([Date_sent_to_Registrations],"yyyymm");

The first produces a list of brokerage names based on 2 criteria, the second uses this list. Is it possible to produce this list within the second query (perhaps using a subquery??), therefore allowing me to get rid of a query>?

Thanks in advance.

 

[CJ_London]

yes. Substitute the query in the FROM clause

SELECT [qryBDM/TPIReport1].Brokerage_Name, Format([Date_sent_to_Registrations],"mmm-yyyy") AS Expr1, Count(tblQuotesNew.Date_sent_to_Registrations) AS CountOfDate_sent_to_Registrations, Format([Date_sent_to_Registrations],"yyyymm") AS SortDate
FROM[COLOR=red] (SELECT tblBrokeragesNew.ID, tblBrokeragesNew.Brokerage_Name
FROM tblQuotesNew RIGHT JOIN tblBrokeragesNew ON tblQuotesNew.Brokerage_ID = tblBrokeragesNew.ID
GROUP BY tblBrokeragesNew.ID, tblBrokeragesNew.Brokerage_Name
HAVING (((Count(tblBrokeragesNew.DateTPIAgreementSigned))>0)) OR (((Count(tblQuotesNew.Date_sent_to_Registrations))>0))) AS [/COLOR][COLOR=black][qryBDM/TPIReport1][/COLOR] LEFT JOIN tblQuotesNew ON [qryBDM/TPIReport1].ID = tblQuotesNew.Brokerage_ID
GROUP BY [qryBDM/TPIReport1].Brokerage_Name, Format([Date_sent_to_Registrations],"mmm-yyyy"), Format([Date_sent_to_Registrations],"yyyymm");

 

[bd528]

yes. Substitute the query in the FROM clause

Perfect - thank you!