How should I design this query?

[jonathanchye]

Hi all,

I want to run a query based on one table but with 2 different criterias...

For example here is my table structure (this is actually a query based on a table):
ID
Month No
Month Name
Date Requested
Status

In my query I want to group results into months of a specific year (which I have done fine)

I want then to run a Count of Date Requested where Status = Live vs. Status = Closed

I can't seem to do this in a single query. I have tried showing the table twice in Design view and changed the criteria for my second table (suffix _1) but that doesn't seem to work.

Can anyone perhaps point me in the correct direction?

Below is my SQL code for now:

SELECT DISTINCT qryTasksExtendedbyRequestDate.[Month no], qryTasksExtendedbyRequestDate.[Month Name], Count(qryTasksExtendedbyRequestDate.ID) AS CountOfID, Count(qryTasksExtendedbyRequestDate_1.ID) AS CountOfID1
FROM qryTasksExtendedbyRequestDate INNER JOIN qryTasksExtendedbyRequestDate AS qryTasksExtendedbyRequestDate_1 ON (qryTasksExtendedbyRequestDate.Year = qryTasksExtendedbyRequestDate_1.Year) AND (qryTasksExtendedbyRequestDate.[Month Name] = qryTasksExtendedbyRequestDate_1.[Month Name]) AND (qryTasksExtendedbyRequestDate.[Month no] = qryTasksExtendedbyRequestDate_1.[Month no])
WHERE (((qryTasksExtendedbyRequestDate.Year)=2011))
GROUP BY qryTasksExtendedbyRequestDate.[Month no], qryTasksExtendedbyRequestDate.[Month Name]
ORDER BY qryTasksExtendedbyRequestDate.[Month no];

 

[jzwp22]

The only way to do this within 1 query is to use subqueries. Subqueries can get kind of messy especially trying to explain them in a forum, so I would use a series of queries to build up to the final query.

First, create a query that extracts the yearly data from your main table. (I'll use a simple sample table called mytable for illustration purposes. This table only has the status field, the date requested field and a primary key)

query name: qryStatusAndMonth

SELECT myTable.Status, Month(myTable.DateRequested) AS MonthNo
FROM myTable
WHERE Year([mytable].[daterequested])=2011;

Now create two queries using the query above to get your counts for the two status conditions

query name: qryLiveByMonth

SELECT [qryStatusAndMonth].MonthNo, Count([qryStatusAndMonth].Status) AS CountOfLive
FROM qryStatusAndMonth
GROUP BY [qryStatusAndMonth].MonthNo, [qryStatusAndMonth].Status
HAVING ((([qryStatusAndMonth].Status)="live"));

query name: qryClosedByMonth

SELECT [qryStatusAndMonth].MonthNo, Count([qryStatusAndMonth].Status) AS CountOfClosed
FROM qryStatusAndMonth
GROUP BY [qryStatusAndMonth].MonthNo, [qryStatusAndMonth].Status
HAVING ((([qryStatusAndMonth].Status)="Closed"));


Since there is no guarantee that each month will have a count for both status types, simply joining the two queries above will not work. In fact, we need an additional table that holds all of the month numbers (1-12, so 12 records). We will then need to join the new month table to each of the two status count queries via left joins since we want to show all month numbers whether or not they have a count in either category.

query name: qryYearlySummary

SELECT tblMonths.longMonthNo, MonthName([longmonthno]) AS MonthName, IIf(IsNull(qryClosedByMonth.CountOfClosed),0,qryClosedByMonth.CountOfClosed) AS Closed, IIf(IsNull([qryLiveByMonth].CountOfLive),0,[qryLiveByMonth].CountOfLive) AS Live
FROM (tblMonths LEFT JOIN qryClosedByMonth ON tblMonths.longMonthNo = qryClosedByMonth.MonthNo) LEFT JOIN qryLiveByMonth ON tblMonths.longMonthNo=[qryLiveByMonth].MonthNo;


I've attached a sample database that illustrates the above.