Subquery Question 1

[Developer2U]

I'm trying to figure out if I can somehow use a subquery to provide a solution instead of using two separate queries and then joining them together.

What I'm trying to accomplish is to create a query that returns a count for a specified date range as well as return the fiscal year count for a business area.

I just have a feeling there is a way to do this in one query. I think it would be more efficient because then I would have less objects in the database to maintain. Below are text of both queries, which are working. Thanks in advance.

This is the date range query:

PARAMETERS [Forms]![frmDocumentReviewReports]![cboMonth] Text ( 25 ), [Forms]![frmDocumentReviewReports]![cboYear] Text ( 4 );
SELECT Count(*) AS DescriptionCount, Description
FROM qryDocument_Review_Stats_BusinessArea
WHERE (((Format([Date_Completed],"mmmm"))=[Forms]![frmDocumentReviewReports]![cboMonth]) AND ((Format([Date_Completed],"yyyy"))=[Forms]![frmDocumentReviewReports]![cboYear]))
GROUP BY Description
ORDER BY Description;

This is the fiscal year query:

PARAMETERS [Forms]![frmDocumentReviewReports]![cboYear] Text ( 25 );
SELECT Description, Count(*) AS FY_Count
FROM qryDocument_Review_Stats_BusinessArea
WHERE (((Date_Completed) Between DateSerial([Forms]![frmDocumentReviewReports]![cboYear]-1,10,1) And DateSerial([Forms]![frmDocumentReviewReports]![cboYear],9,30)))
GROUP BY Description
ORDER BY Description;

 

[PHV]

A starting point:
PARAMETERS [Forms]![frmDocumentReviewReports]![cboMonth] Text ( 25 ), [Forms]![frmDocumentReviewReports]![cboYear] Text ( 4 );
SELECT Description
, Sum(IIf(Format([Date_Completed],'mmmm')=[Forms]![frmDocumentReviewReports]![cboMonth] AND Format([Date_Completed],'yyyy')=[Forms]![frmDocumentReviewReports]![cboYear],1,0)) AS DescriptionCount
, Sum(IIf([Date_Completed] Between DateSerial([Forms]![frmDocumentReviewReports]![cboYear]-1,10,1) And DateSerial([Forms]![frmDocumentReviewReports]![cboYear],9,30),1,0)) AS FY_Count
FROM qryDocument_Review_Stats_BusinessArea
GROUP BY Description
ORDER BY Description

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Developer2U]

Thanks for your reply. I will give this a whirl tomorrow and see how it jives. I'll let you know how it works out or if I have any additional questions.

 

[Developer2U]

Thanks once again PHV. You're saving me a lot of headaches and time.