Querying

[pleg12345]

I want to count the amount of records that have been delivered and not delivered in the same query.

I have tried putting:
no of queries delivered : isCleared
Total: Count
then in the criteria defining whether its true or false

But it doesnt seem to recogise the fact whether is been delievered or not?

can somebody help?

 

[BSman]

There must be a column in your source table that identifies whether or not the record has been delivered. Let's assume it's a date column.

I'll do this in two steps for clarity.

First, build a query on the table that includes a newly defined column (where Delivery is the column with the delivery date):

IsDelivered: IIf(IsNull(Delivery],"No","Yes&quot

This is based on the idea that Delivery will be null unless there is a delivery.

The build a grouping query that has the following:

SELECT [IsDelivered], Count([IsDelivered]) FROM MyQuery Group by IsDelivered

This should give you the results you are looking for.

And you can probably just make them one grouping query instead of the two I showed for clarity.