Specific Record Count

[FormerJarHead]

Hi all,



I have what I thought was a simple problem. I have a report based on a select query. Everything is working as it should as far as the results I'm getting. What the query returns is an order ID in one field and then in another field it displays the details for that order and there are a few other fields that do not pertain to the problem. Very basic query. What I would like to do is get a total for the number of orders and the number of details, i.e. 17 Orders with 117 records. I prefer to display them at the bottom of the report so what I've done is in the form footer I've created 2 unbound text boxes. In one, I've entered =Count([OrderID]). In the other =Count([Description]). The second one works...and it works so well that it also including the same results in the OrderID box.

Is there a way in a query expression or in the control source that I can count just the orderID so when the reader of the report looks at the data, at the bottom is will give a synopsis of the number of orders and the number of details?

Thank you for any suggestions

[June7]

It doesn't matter which field you reference in the Count function, it will count all records of the report.

Options for count of the orders:

1. report/subreport arrangement and each can have their own count calcs

2. DCount() expression to count orders, the DCount() references the Orders table, not the report

3. An aggregate query that counts the orders and that query is included by join in the report RecordSource, this can be a nested subquery

Options 2 and 3 will have to replicate the filter/grouping criteria used in the final report output.

[FormerJarHead]

Thanks June7. No access to db today but will give option 3 a shot on Monday. Option 1 and 2 do work but the DCount function counted every detail in the entire db, and Option one returned the same count as the details count within the date range.

[June7]

Then you must not have included filter criteria in the DCount. See last line of my previous post.