Totals Only for Report


G

Guest

Hi

I am new to Access, but experienced in Excel & Business Objects, as such am
having some difficulty working out how to do standard things from those
applications in Access.

OK, i have a Table with 4 bits of information;

* Customer ID: an autonumber that doesnt allow duplicates
* Customer's Region: North, West, South, East
* Customer Type: A or B
* Customer Grading: 1, 2, 3, 4, 5


I then have Queries that break these down into the sort of Crosstabs / Pivot
Tables
i am used to.


e.g;


North West South East
Type A: 10 20 30 40
Type B: 40 30 20 10
Total: 50 50 50 50


.... and ...


North West South East
Grading 1: 10 20 30 40
Grading 2: 40 30 20 10
Grading 3: 30 40 10 20
Grading 4: 20 10 40 30
Grading 5: 10 10 10 10
Total: 100 100 100 100


My problem comes when i try to add this data to a Report.

I can get a Report that lists all of the ID numbers row by row in blocks
broken down by Type, Region & Then Grading.

eg;

Type: A
Region: North
Grading: 1: ID1
ID2
ID3
ID4
ID5
ID6


Type: A
Region: North
Grading: 2: ID7
ID8
ID9
ID10
ID11
ID12
etc



I cannot however get just a total Count of the ID for each of these categories

eg;

Type: A
Region: North
Grading 1: 98
Grading 2: 102
Grading 3: 75
Grading 4: 81
Grading 5: 34
Total: 400

Type: A
Region: West
Grading 1: 97
Grading 2: 101
Grading 3: 74
Grading 4: 80
Grading 5: 33
Total: 399

There is clearly something i am missing, because whilst i can effectively
'Fold' Or Pivot the info from the Table into a Query, i cant then get it from
the Query to the Report with losing some control of the variables & their
layout.


Does anybody have any suggestions? Please let me know if you require any
further information.


Many thanks


jb
 
Ad

Advertisements

G

Guest

If you create a Totals query and then base your report on that I think you
will get what you want - in query design view clcik the Totals button and
then use Group By and Count in the grid to get the results you need.

When you create the report you can include an unbound field in the Report
Footer and set it's Control Source to the field you need then change it to
=SUM([Field_Name]) Make sure you put it in the Report Footer and not the Page
Footer

HTH - Sheila
www.c-i-m-s.com
 
Ad

Advertisements

L

Larry Linson

Certainly, you create a Totals Query with the information you want, and
Report on the Totals Query. Create a Query in the Query Builder, with Type,
Region, and two copies of Grading. Then, in the menu, View | Totals. Then
under Type, Region, and the first copy of the Grading Field, set "GroupBy"
and under the second copy of the Grading Field, set Count. Run your Totals
Query to assure you are getting what you expect, then use it as the Record
Source for a Report.

Larry Linson
Microsoft Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top