Totals for Report

[JB2010]

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

 

[Jeff C]

Your solution lies with using the report sorting and grouping levels which
can take awhile to get right. Research help with that topic and also check
out this link:

http://www.fontstuff.com/mailbag/qaccess04.htm

Martin Green's website is full of good info and he has this "new" article on
counting that may help you.

 

[gls858]

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

Add Cust type, Region, and Grade to a new query. Select the totals Icon
it a sigma sign. Looks sort of like a M standing on it's end. Then under
the grade click in the row that says totals and from the drop down box
choose sum. You should get something that looks like this:

CustRegion CustType SumOfCustGrade
East B 6
North A 4
North B 5
South A 3
South B 6
West A 9

You should now be able to base a report on this query to get what you
want.