Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Reports / Printing / November 2006

Tip: Looking for answers? Try searching our database.

Displaying Details and SUM

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jojo - 27 Nov 2006 03:28 GMT
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 - 27 Nov 2006 03:47 GMT
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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 4 lines]
> The query will come up with an error that unable to join due to aggregate
> function.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.