MS Access – Count of parent table items in a report

5 pts.
Access multi-table reports
Microsoft Access
Microsoft Access reports
I have an MS Access database with a parent table and a child table. Table A (the parent, an Order table) has a PartID field, an OrderDate field, and an CustomerID field. Table B (the child, Parts) has a PartID primary key field and a PartDescription field. A relationship joins the 2 tables on the PartID field. My report has a query defined for the Record source, and does a left outer join along with a search on the OrderDate field. My report lists all Orders with the requested date, and displays all of the parts associated with the Order. What I want to do is display (in the report footer) the count of Orders in the report. All I am able to count is all of the parts for all of the Orders in the report. Any ideas? (HINT: this is more difficult than it sounds!)

Answer Wiki

Thanks. We'll let you know when a new response is added.

It sounds like there is no “OrderID” field to count. With the current field list, an “Order” could be defined as Order Date combined with CustomerID, unless the same customer placed two Orders the same day. It would be possible to create Order:[OrderDate] & [CustomerID] as a calculated field in your report to group or count by.

You might consider Adding an Order ID field to the Orders table and possibly creating an Order Detail table that could contain Order ID, Part ID, Quantity, etc. which is a typical table structure used in many order systems. 3 tables (instead of 2) would then contain …

(Table A) OrderID, Customer ID, OrderDate, SpecialInstructions, etc…
(Table B) OrderID, PartID, Quantity, Price, etc…
(Table C) PartID, Description, OnHandQty, CurrentPrice, etc…

Hope this helps.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Squashjunkie
    I'd agree with Ttstudio on all of the above, only thing I would add is when creating the OrderID file in your Sales Order Header Table (in this case table A), make sure the field is the Primary key. Even better, set the field in the table design to AutoNumber so that access generates the number for you.
    325 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: