Count in a query

[DavidAC]

hi,
hope you can help. i have a query from which i want to produce 3 forms of counts using the same criteria. The counts are as follows
1) count order numbers where the date completed is null
2) count order numbers where the date completed is NOT null
3) count total order wheather the date is null or not.

the only fields i have in the query are order number and date_completed. any help would be appreciated as i am tearing what little hair i have left out. ray:

 

[Norie]

This can't be done with only one query.

You need at least two.

Create a new query add the order number field and the date completed field.

Right click and select totals.

Select Count from the Total dropdown for order number field.

For the date completed field set the criteria to Is Null and select Where from the Total dropdown.

Run the query and you should have a count of orders with no date completed.

To count total orders just remove the Is Null from the criteria, or alternately open up the orders table and look at the record number indicator in the bottom left corner.

 

[DavidAC]

thanks for your response, what i need to do next is to show these seperate queries in one form is there a way to do this?

 

[Norie]

If you want them in a form then what you probably want to use is the DCOUNT function.

eg

DCOUNT("[order number]", "Orders", "[date completed] Is Null")

 

[DavidAC]

thank you everything appeared to work great however i now have the problem that as soon as a second order is added to the list it returns a #error# value when it should return a number value. Also could you inform me how i only show just one order number rather than all the requests under that number as i only want a summary of the data