Displaying Details and SUM

[Jojo]

Hi.

I am currently trying to calculate the sum of all the purchases done by a
particular customer. I have no problem coming up with the sum. However, I am
not able to display the report with both details of a particular person, the
product purchased and the total sum.
The query will come up with an error that unable to join due to aggregate
function.

Any one can help me on this?

Thank You.

 

[Allen Browne]

Jojo, which of the following are you trying to do?

a) Show one total for each customer, regardless of what products they were.

b) Under each customer, show a row for each product with a subtotal, and
then a total for the customer.

c) Under each customer, show each product. Under that, show every purchase
of that product, with totals for the product and the customer.

d) Show a list of customers, with a total purchase value beside each one. At
the end of the list, show all products, with a total beside each one.

If (a), you won't be able to show any information about the product.

If (b), create a query.
Depress the Total button on the toolbar in query design.
Access adds a Total row to the design grid.
Group By CustomerID and ProductID.
Sum Amount.
Build the report based on this query.

If (c), create the query without any grouping.
In your report, use the Sorting And Grouping dialog (View menu) to create a
Group Header and Group Footer on the CustomerID, and also on the ProductID.
In both group footers, place a text box with Control Source of:
=Sum([Amount])

If (d), you already have the main report with customer totals.
Create a similar report for the product totals.
Then place the 2nd report in the Report Footer section of the first one as a
subreport.