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

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