Counting and comparing to total in query 1

[officeguru]

I hope I am not offending your intellect but I can't figure this one out.

I am keeping track of writers/publications in Access 97 each publication has a due date, a completed date, an intermediate date and a yes/no variable.

I am trying to create a query that will count publications that are completed on or before the due date, and show what percent of all completed pubs that number is for a particular writer. This will be my "on-time" rate

I haven't even been able to construct that part.

I further wanted count the publications whose intermediate date was before the due date if the completion date was after the due date and the variable is yes. This is my "not-quite-on-time" number.

I'd then like to combine the on-time number and the not-quite on-time to give an overall percentage of that writer's timliness.

Help

Thanks. I have found this forum to be an excellent resource I hope someone can help me figure this out.

Brian

 

[jryan224]

Well maybe:
in the query create a calculated field using an immediate if statement that puts 1 if completed date before due date and 0 if not. Then in report you can use sum and % details.

ontime: iif(completed date <= duedate, 1, 0)

Same for the not quite on time.

notquite: iif(intermediate date <= duedate and completed date >= due date, 1, 0)