Need to do counts, but don't know how to start!

[NotVeryTechie]

Hi there

There is a report I need to create and I don't even know where to start. I tried creating a query, but am not getting that right.

Here is what I want to do:

I have a Due Date and a Completed Date field. I want to know the number of instances (records) where the:
Completed Date = or > Due Date
Completed Date < Due Date
Completed Date = Null

Then once I have that report, I need to do a second one where I want to have the exact same stats, but want to put in a date parameter based on Received Date (user puts in start and end date).

Hope you can help me!!

 

[khawar]

Download the attached sample

 

[Brianwarnock]

You can do Count s and Union but it is simpler to use Sum(IIF....

CountofNull:Sum(IIF(IsNull([Completed Date]),1,0)
Countoflessthan:Sum(IIF([Completed Date]< [Due Date],1,0) etc

all in 1 query

Brian

 

[NotVeryTechie]

Brilliant, thank you!

 

[NotVeryTechie]

Is there a way to graph this?

Also trying to do the following:

Department, Sub department - number of requests received, how many days to reply, how many late

Department and Sub department are in a table called department

I would again like to report and graph and would like only the departments that have had requests

Not sure if I am doing this right at all.