I need to show the number of valid inspectors we have by month over the last five years. Inspectors are considered valid when the expiration date on their certification has not yet passed, recorded as the month end date. The below SQL code is text of the query to count valid inspectors for January 2017:

SELECT Count(*) AS RecordCount
FROM dbo_Insp_Type
WHERE (dbo_Insp_Type.CERT_EXP_DTE)>=#2/1/2017#);

Rather than designing 60 queries, one for each month, and compiling the results in a final table (or, err, query) are there other methods I can use that call for less manual input?

CodePudding user response：

A UNION query could calculate a record for each of 50 months but since you want 60, UNION is out.

Or a query with 60 calculated fields using IIf() and Count() referencing a textbox on form for start date:

SELECT Count(IIf(CERT_EXP_DTE>=Forms!formname!tbxDate,1,Null)) AS Dt1,
Count(IIf(CERT_EXP_DTE>=DateAdd("m", 1, Forms!formname!tbxDate),1,Null)) AS Dt2,
...
FROM dbo_Insp_Type

Or a report with 60 textboxes and each calls a DCount() expression with criteria referencing a textbox on form for start date:
=DCount("*", "dbo_Insp_Type", "CERT_EXP_DTE >= #" & Forms!formname!tbxDate & "#")
=DCount("*", "dbo_Insp_Type", "CERT_EXP_DTE >= #" & DateAdd("m", 1, Forms!formname!tbxDate) & "#")

Or a VBA procedure that writes data to a 'temp' table.

CodePudding user response：

From this sample:

this single query:

SELECT 
    Format([CERT_EXP_DTE],"yyyy/mm") AS YearMonth, 
    Count(*) AS AllInspectors, 
    Sum(Abs([CERT_EXP_DTE] >= DateSerial(Year([CERT_EXP_DTE]), Month([CERT_EXP_DTE]), 2))) AS ValidInspectors
FROM 
    dbo_Insp_Type
GROUP BY 
    Format([CERT_EXP_DTE],"yyyy/mm");

will return: