By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,779 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

Total Query results in a report

P: 4
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, DeliveredQuantity, 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, DeliveredQuantity, 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 DeliveredQuantity
Product2 $Value VolumeOrdered DeliveredQuantity
Product3 $Value VolumeOrdered DeliveredQuantity

Totals $Value VolumeOrdered DeliveredQuantity


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
Sep 11 '08 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 11 '08 #2

P: 4
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
Sep 12 '08 #3

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 12 '08 #4

P: 4
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, DeliveredQuantity, Period

Query2
Fields CustName, SumoOf$Value, SumOfVolumeOrdered, SumOfDeliveredQuantity, 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, SumOfVolumeOrdered, SumOfDeliveredQuantity
(Heading:) Product, Volume ordered, Rand Value, Delivered Quantity

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

Product, $Value, VolumeOrdered, DeliveredQuantity

-----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
Sep 12 '08 #5

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 13 '08 #6

P: 4
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.
Sep 15 '08 #7

NeoPa
Expert Mod 15k+
P: 31,342
No worries. I'm just glad that's helped :)

Welcome to Bytes!
Sep 15 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.