Results 1 to 9 of 9
  1. #1
    brian_1701
    Guest

    Create a Grand Total (Access XP)

    We have a simple Database that keeps track of how much newspaper coverage our work generates, eg, New York Times 5cm on 15 August 2001
    LA Times 10cm on 8 August 2001, etc

    We would like to create a Grand Total for ALL coverage by ALL newspaper for a particular year, but Access will only provide totals for each newspaper, eg:

    New York Times 2001 50cm

    How can we get Access to provide a Grand Total, eg:

    LA Times 100cm
    NY Times 50cm

    Grand Total for 2001 = 150 cm

    The Field Names in the Query are as follows:

    TABLE: Clippings, FIELD: Date
    TABLE: Clippings, FIELD: ColumnSpace(scc)
    TABLE: Newspaper, FIELD: Newspaper

    Any help would be *greatly appreciated*. I find the Access HELP very ineffectual. 8^(

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Create a Grand Total (Access XP)

    ??I find the Access HELP very ineffectual<<

    One of the biggest complaints about Access2000 was the Help system. I'm sorry to see it hasn't been improved. (I know of people who actually keep a copy of Access97 Help, and switch to it when they need help!)

    But to answer your question. You can't get both newspaper and grand totals in a query, you can get one or the other. You can, however, get both in a report. Create a report based on your table. Create a group/sorting level based on Newspaper, and specify you want a group footer. In group footer, place a textbox for the Newspaper name and another one in which you use as the controlsource: =Sum(Coverage).

    In your report footer, place another textbox that has the same =Sum(coverage) as its controlsource.

    You actually don't need anything in the detail section, so you can just shrink it to nothing.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create a Grand Total (Access XP)

    You can get multiple levels of totals with hierarchical recordsets, which I believe can be bound to reports in Access XP. I don't have it yet, so I haven't been able to test that.

    However, hierarchical recordsets are difficult to work with and to build (at least for me they are) and are only available with ADO 2.5 or higher. I wouldn't recommend you attempt them until you get the concept down pat and even then, keep something like the Visual Basic Developer's Guide to ADO at your fingertips. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Create a Grand Total (Access XP)

    >>You can get multiple levels of totals with hierarchical recordsets, which I believe can be bound to reports in Access XP. I don't have it yet, so I haven't been able to test that. <<

    How come the amount of things I don't know grows faster than the amount of things I know?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create a Grand Total (Access XP)

    You're just not running fast enough, Mark! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ADO is just chock full of new and wondrous things to drive us all crazy. It took me a week to figure out how to populate a form with a flexgrid based on a hierarchical recordset for an application I was building. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

  6. #6
    brian_1701
    Guest

    Re: Create a Grand Total (Access XP)

    Thank you so much for your help. I remember learning this (about 2 years ago), but I hadn't needed it for such a long time, I guess my memory wiped it!!!

    It's a pity that Access doesn't automatically ask you if you would like Totals on your Report; I would think this would be a very common feature in Reports?!?!

    Anyway, thanks again. Very much appreciated.

    God bless

    Brian

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create a Grand Total (Access XP)

    Well, unless the report wizard is very different in XP, it does ask you there. If you don't use a wizard, you can't really expect it to ask you much of anything because it thinks you know what you want to do already. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  8. #8
    brian_1701
    Guest

    Re: Create a Grand Total (Access XP)

    Really? I know there's an Option in the Query Wizard, but I don't ever recall seeing one in the Report Wizard. I actually just ran a Report Wizard after reading your reply, and I must be having a reverse-hallucination, because I can't see a Totals option?!?!

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create a Grand Total (Access XP)

    You're right, I was thinking of the query wizard. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •