Counting Unreported Data

  • Thread starter Thread starter knowshowrosegrows
  • Start date Start date
K

knowshowrosegrows

Here is my quandary.

I have 72 different reporters that should report data on every weekday.

Each time one of them reports it is called a census event with a unique ID.

My table just tells me the census events.

I need to know how many weekdays in the last 3 months EACH reporter DID NOT
report data.

Is that clear enough for someone to make a suggestion?
 
Have the total number of week days (it is independent of the reporter, it
just depends of the 3 months you consider). From that constant, subtract the
number of day you have for each reporter. That will give, for each reporter,
the number of week day without a report.


SELECT reporter, COUNT(*)
FROM ( SELECT DISTINCT reporter, theDate
FROM yourTable
WHERE theDate BETWEEN startingDate AND endingDate)
GROUP BY reporter


gives, by reporter, the number of day with a report, for that reporter.



If there is 60 opening days, then your initial problem is solve with:

SELECT reporter, 60 - COUNT(*)
FROM ( SELECT DISTINCT reporter, theDate
FROM yourTable
WHERE theDate BETWEEN startingDate AND endingDate)
GROUP BY reporter
HAVING 60 - COUNT(*) > 0



Vanderghast, Access MVP
 
Back
Top