Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Totals Only for Report

Discussion in 'Information Technology' started by JB2010, Jul 28, 2006.

Tags:
  1. JB2010

    JB2010
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. Sheila D

    Sheila D
    Expand Collapse
    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

    "JB2010" wrote:

    > 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
    >
     
  4. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    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

    "JB2010" <JB2010@discussions.microsoft.com> wrote in message
    news:B586D483-298B-48EF-80A9-F42DF7AF8D13@microsoft.com...
    > 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
    >
     

Share This Page