OK Todd, here it goes. I'll explain how the query works. I would recommend
that you create a form where you capture the information you need to run the
query. You will need a text field formatted as a date, a combo box which
contains the periods you want to use (this combo should have two fields, the
first field will be the code used by the DateAdd function to represent a
period, the second field will contain the description:
Period_CD Period Desc
d Day
ww Week
m Month
q Quarter
y Year
You also need a field to indicate how many of these periods you want to
report on, textbox. Lastly, you need a table (tbl_Numbers) which contains a
single field [intValue] and contains records 0 - 9 (or however many periods
you are interested in.
Now, the SQL you need to put in your query will look like:
SELECT I.InspID
, Sum(IIf([PassOrFail]="Pass",1,0)) AS Pass
, Sum(IIf([PassOrFail]="Fail",1,0)) AS Fail
, N.intValue AS Period
, DateAdd("ww",-[intValue]-1,Date()) AS StartPeriod
, DateAdd("ww",-[intValue],Date()) AS EndPeriod
FROM tbl_Inspections AS I, tbl_Numbers AS N
WHERE I.InspDate>DateAdd("ww",-1*([intValue]+1),Date())
And (I.InspDate)<=DateAdd("ww",-1*[intValue],Date()))) + 1
AND N.IntValue < 6
GROUP BY I.InspID
, N.intValue
, DateAdd("ww",-[intValue]-1,Date())
, DateAdd("ww",-[intValue],Date());
Basically, what this does is create a Cartesian join between the inspections
table and the Numbers table. You use the numbers table to determine which
period that Inspection Date is in by testing to see whether the InspDate is
between the first day of the period and the last day. If it is, then
include it in the result set, then determine whether the inspection was a
pass or a fail, and sum those up.
This particular query groups inspections by week (ww), and gives you 7 weeks
worth of data (as long as there is an entry for each week). By using the
value in the first column of the combo box to replace "ww" with the
appropriate period, and replacing the 6 in the last line of the where clause
with a reference to the Periods text box, you should be able to get this to
return as many periods as you want. Once you get this part working, you
will have to use it as the basis of a union query which will give you the
totals row for each InspID.
HTH
Dale
Todd said:
Thanks for the help.
For the date range the end date of the most recent period
could just be the current date. As far as defining the
periods, ideally I'd like the user to be prompted to enter
a period (either in days or weeks or months).
Regards,
Todd
-----Original Message-----
Todd,
To answer the first part of your question (which would produce your totals
line). You didn't indicate what you are grouping this by, so I'll assume
you want to group by the inspector. If it is something else, you can just
change the field name in the select and the group by part of the query.
SELECT InspectorID
, SUM(IIF([PassOrFail] = "Pass", 1, 0)) as Pass
, SUM(IIF([PassOrFail] = "Fail", 1, 0)) as Fail
, SUM(IIF([PassOrFail] = "Fail", 1, 0))/SUM (1) as FailPct
FROM YourTable
GROUP BY InspectorID
Doing the date groupings will be a little more difficult. First, you need
to specify how you define the end date of the most recent period. Let me
know how you want to handle that and I'll look at the part about adding the
dates into the mix.
HTH
Dale
I have a table Called Insp History which has the following
fields: (among others)
PassOrFail [contains only Pass or Fail as data]
Date
What I would like to do is create a query which will
return the following:
Number of Passed Inspections
Number of Failed Inspections
Total number of inspections
Percent of failed inspections
Furthermore, I would like to be able to look at this data
in period blocks and as a total. So Ideally I could enter
a period, say 6-months, and I would get the following:
Period, NumPass, NumFail, Total, %Fail
1/1/04 to 6/30/04, 5,15,20,75%
7/1/03 to 12/31/04, 2,12,14,86%
1/1/03 to 6/30/03, 5,22,27,81%
1/1/03 to 6/30/04, 12,49,61,80% [This is the total row]
What would a query look like to do this (or would it be
multiple queries)?
Thanks in advance for any help.
.
