|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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.
--
Jeff C
Live Well .. Be Happy In All You Do
"JB2010" wrote:
[Quoted Text] > 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
|
|
JB2010 wrote:
[Quoted Text] > 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.
|
|
|