Creating report with date counts

  • Thread starter Thread starter Chipgiii
  • Start date Start date
C

Chipgiii

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!
 
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");
 
Back
Top