Controlling sum totals for queries and reports generated from heirarchal tables.

[BruceWaller]

I have a "Sales Order" table from which I have queried "Sales Order Number", "Customer", "Total Sales Amount", "Total Cost", and "Total Profit".

However... in a relational table for "Sales Order Line Items" I need to pull in the field for "Item Description" so that whoever is viewing the query/report will see the line items.

When I run this query it pulls a new record for every line item. So if I have 3 line items on an order... there are 3 individual records for that sales order... and each of these records include the Sales...Cost...Profit fields. This prevents me from providing Sales Order sum totals.

How can I create a query or report that will pull the "Item Description" into view without affecting the Sales Order financials?

 

[revver]

Have you set up the relationship between the two tables as 1-many?

Have you set up grouping on your report?

One way (less efficient) is to use DLookup if your requirement is that simple.