Total Query results in a report

[tsuedesu]

Hi,

I am trying to create a report that will show the totals of a query. I have a table as follows

Table :Sales
Fields:
CustName, Product, $Value, VolumeOrdered, DeliveredQuanti ty, Period

I've made a query that retrieves records with in a certain period (aug-08,Sept-08,etc), grouping by CustName and displaying the list of individual products purchased. So the report shows the same information, list of products purchased, grouped by CustName.

I now need to create a section on the report to show the totals of $Value, VolumeOrdered, DeliveredQuanti ty, and some how still show the list of individual products as a list, grouped by CustName.

My ideal report would look something like

Sales Report for Period :
Customer Name :
Product1 $Value VolumeOrdered DeliveredQuanti ty
Product2 $Value VolumeOrdered DeliveredQuanti ty
Product3 $Value VolumeOrdered DeliveredQuanti ty

Totals $Value VolumeOrdered DeliveredQuanti ty


Any help would be appreciated. I've used Access to build databases before, I'm just very rusty since its been years, please treat me like a novice.


Thanks Very Much

EMil

[NeoPa]

Have a look at Sorting & Grouping within an Access report.

Using a section header &/or footer, you can handle this quite easily. It's designed to work that way naturally.

Let us know how you get on with this.

[tsuedesu]

The problem isnt the grouping, its adding up the result per group. a Totals section. Now I knw how to build a query that will sum all the results per company, but I need to print a report with the list of products too.

EMil

[NeoPa]

EMil,

I know you're confused, but please try the advice first before you dismiss it.

If you do you'll find that Sorting & Grouping in an Access report can, indeed, help with your problem.

If, after looking into that you are still confused, please come back requesting further clarification.

[tsuedesu]

Hi NeoPa,

I figured it out, but the grouping and sorting stuff didnt help at all. I already had my data grouped in the right way. My solution is as follows, I personally think its cheating, but I guess it works.

I created two queries, Query1 to retrieve the list of products sold in a period, and then Query2 that totals the results; both are based on the Sales table.


Query1
Fields : CustName, Product, $Value, VolumeOrdered, DeliveredQuanti ty, Period

Query2
Fields CustName, SumoOf$Value, SumOfVolumeOrde red, SumOfDeliveredQ uantity, Period

I then created Query3, linked the CustName and Period from query1 and query2. I selected all the fields for display.

I then create a report using the wizard. I shuffled the data around until the report looked something like:

-----Report Header----------

Sales report for (Query3.Period)

-----Page Header------------

-----CustName Header----

CustName

SumoOf$Value, SumOfVolumeOrde red, SumOfDeliveredQ uantity
(Heading:) Product, Volume ordered, Rand Value, Delivered Quantity

-----Detail Header-----------

Product, $Value, VolumeOrdered, DeliveredQuanti ty

-----Page Footer------------

-----Report Footer----------


I'm not sure if this is the correct way to do it, it seems to be working. However, I will have to see what happens, especially because I need to create other similiar reports.


Any thoughts on this would be appreciated.

Thanks

EMil

[NeoPa]

Your solution, though intelligent, is more complex than is needed.

I'm sorry to bring you back to the Sorting and Grouping issue, but if I lead you through it somewhat, hopefully it will become clearer.

Firstly, the underlying table or query need only be in the standard format. It just needs to list all the items.

Within the report, you need to select Sorting and Grouping and add a group for your Product Code. The important thing then is to select (in the group properties underneath) Group Footer = Yes. This provides an extra section on the report where you can add TextBox controls (and various others of course) where you can set the Control Source to "=Sum([VolumeOrdered])" for instance.

You can also add a group for your customer if you will, setting the Group Header to Yes, and include a TextBox in that header to display the customer name and details.

Showing a Report Header and/or Footer, is controlled from the View menu.

As you have already found, it is possible to do very clever things with SQL, but reports were DESIGNED to help with this sort of thing.

I hope this is all clearer now, and if there's anything still troubling you please don't hesitate to ask.

[tsuedesu]

I understand now...Thanks for the patience. I didnt know such a function existed.

It looks good so far, just some adjustments to the layout and it would be just right.

Thanks again.

[NeoPa]

No worries. I'm just glad that's helped :)

Welcome to Bytes!