Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sums by field name (2002)

    Hi,

    I have a query that returns the sum of [TotalVisitCost] for each [SkillName]. There is a report that is bound to this query, which has a group heading by office. The data returned is fine. My goal is to take each of the sums by skill and do a grand total for each at the end of the report. Presently all that I've been able to do is get the total visits by skill name for all of the offices. Presently the report looks like this:

    Office1
    HHA #
    SNV #
    PT #
    Total Visits *

    This repeats for each office in the report. I need to have a grand total by [skillName] at the end of the report. I've tried =sum([skillname]="SNV") but get a data type mismatch in criteria each time. The control for the sum is formatted as a number so I'm not sure where to look next.

    Thanks,
    Leesha

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums by field name (2002)

    What would you like to have ?
    A total for each skill after each skill ? Are the sorted on skill ?
    Or a recapitulative listing at the end of the report ?
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sums by field name (2002)

    Hi,

    They are sorted on the office. So each office has up to 7 different skills under them that have been summed in the query. I need recapuitulative listing at the end of the report that shows the grand total for the offices - each skill would show its own grand total.

    Leesha

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums by field name (2002)

    One option would be to make a new query where you sum the records by skills, without looking at the offices.
    Use that query to create a subreport that you can put in the footer of the report.
    Francois

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sums by field name (2002)

    Well I have to say this opens up an whole new world of possibilities with reports! I had no idea that you could do subreports. I get so frustrated with Access reports as they limit me to what I can put into one report and I often find myself simply doing them in Excel because I have the control vs Access. I tried looking up subreports in help but didn't see anything. I need to know how to attatch the new query to footer section.

    Thanks,
    Leesha

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums by field name (2002)

    I can't believe you haven't find anything about subreports in the help file.
    Create your query.
    Create a new report based on the above query.
    Follow the instructions in the attachment. (this is a copy of the help file)
    Francois

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sums by field name (2002)

    Well I must be blind then because when I typed in subreport the items that came up were not what I needed. When I typed in the title of your attachment that didn't come either. I don't want you to think I'm lazy and just use this lounge for quick answers. Subreports, unlike subforms, does not come up in my help file.

    Thank you for the reference. I'm thrilled at the possibilities and now just may come to love Access the same way I do Excel!

    Leesha

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

    Re: Sums by field name (2002)

    Francois,

    I checked my help file for 2002 and I can't track down that help item either. The index has nothing on subreport in the UI, and the subreport topics in the IDE don't turn up any basic how-to instructions. I know that help item looks familiar, but I've tried every trick I could think of and I can't find it in the 2002 help. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Charlotte

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums by field name (2002)

    Sorry I wasn't meaning to be brutal. I use Access 2000 and 2003 and in both if I enter subreport in the index of the help file, that give me a link to the page I use as attachment in my previous post. I made the assumption that 2002 will do the same. I think I made a mistake by making assumption concerning a MS product.
    Please accept my apologize.
    Francois

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums by field name (2002)

    See my answer to Leesha
    Francois

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sums by field name (2002)

    No need to apologize! I just didn't want you to think that I don't try to solve these things myself. If you only knew the time I spend before sending a post.

    Thanks again,
    Leesha

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

    Re: Sums by field name (2002)

    Yes, I just found it easily in the help for my Access 2000, but it doesn't seem to be their in 2002. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums by field name (2002)

    It is there, but Microsoft has made it virtually impossible to find, since nothing you type in the Answer Wizard or Index will turn up this help item. This seems to be the case with many help subjects <img src=/S/sad.gif border=0 alt=sad width=15 height=15>.

    You can get to the equivalent of the page Francois showed from Contents: Microsoft Access Help > Reports and Report Snapshots > Advanced Reports > Subreports > Create a Subreport.

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

    Re: Sums by field name (2002)

    Good grief! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> It amazes me that something so basic is so hidden that you have to track it down in Contents because it isn't directly referenced in the index and can't be found from the answer wizard! <img src=/S/headthrob.gif border=0 alt=headthrob 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
  •