Count, If and greater than...

[doran_doran]

I would like to know if I can use count and if together.

Lets say I have two fields. Field1 and field2. field1 is where we have hard coded due dates and field2 is where end user put a receive date.

I would like to get a count where field2 > field1. That means we are receiving after the due dates. Is this possible?

 

[dcx693]

You can do this in one query, for example:

SELECT Count(*) AS Total
FROM tblData
WHERE (((tblData.Field2)>[tblData].[Field1]));

 

[dcx693]

I've attached a picture of the query in design view.

 

[doran_doran]

Thanks

Thank you all, it works. I have one more question.

I have four queries like this type and basically i am doing this to show summary on a report. Is there a way i can put all this query on one report.

Report goes like this.

Summary:
- Total Received after due date (which is we just did)
- Total Census for Plan Year End (count)
- Total Census Complete (count)
- Total Outstanding (count)

Thanks for helping me.

Regards / Dianna