Creating report with date counts
in [General]
|
From: Chipgiii on 10 Feb 2010 13:40 I need to create a weekly or monthly report (may need new query) that does the following: A date is entered for each light in the Greenhouse that is out. A second column is where the repair date is entered. If the repair date is blank, the work is outstanding. The report would need to count the number of blank repair dates for a given period (either week or month), and divide that by the total number of lights in the greenhouse. This would give me a weekly/monthly "lights out percentage." Any ideas would be terrific, just remember I am a novice user - so simpler is better! -- Thanks, Chip
From: KARL DEWEY on 10 Feb 2010 14:00
Try this with your table and field names -- SELECT Format([CheckDate], "mmmm yyyy") As Check_Date, (Sum(IIF([RepairDate] Is Null, 1, 0)) / Count([LightNumber])) * 100 AS [lights out percentage] FROM YourTable GROUP BY Format([CheckDate], "mmmm yyyy") ORDER BY Format([CheckDate], "yyyymm"); -- Build a little, test a little. "Chipgiii" wrote: > I need to create a weekly or monthly report (may need new query) that does > the following: A date is entered for each light in the Greenhouse that is > out. A second column is where the repair date is entered. If the repair > date is blank, the work is outstanding. The report would need to count the > number of blank repair dates for a given period (either week or month), and > divide that by the total number of lights in the greenhouse. This would give > me a weekly/monthly "lights out percentage." > > Any ideas would be terrific, just remember I am a novice user - so simpler > is better! > > -- > Thanks, > Chip |