Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub totals in query (Access 2002 -Sp-2)

    I have a set of data like
    Location, Month,Revenue
    Here, July, 100
    Here, Aug, 150
    Here, Sep, 120
    There, Jul, 80
    There, Aug, 70
    There, Sep, 80
    and what I'd like to be able to see/do in a query in separate columns is subtotals for each location, and in another colum the total for the data and a running total.
    eg.
    Location, Month,Revenue, Location Subtotal, Running Total,Grand Total
    Here, July, 100, 370, 100, 600
    Here, Aug, 150, 370, 250, 600
    Here, Sep, 120, 370, 370, 600
    There, Jul, 80, 230, 80, 600
    There, Aug, 70, 230, 150, 600
    There, Sep, 80, 230, 230, 600

    Any advice gratefully accepted.
    Zinger

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

    Re: Sub totals in query (Access 2002 -Sp-2)

    Doing running sums in a query is possible, but it's much easier in a report. You can put another text box bound to Revenue in the detail section and set its Running Sum property to Over Groups. You can put a text box in the group footer for Location and in the report footer with Control Source =Sum([Revenue]). Will that do for you?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub totals in query (Access 2002 -Sp-2)

    Thanks Hans.
    I was aware that I could do it in a report, and thanks for the explanation, but I really need to do it in a query, as I need to write the running totals to a table for subsequent analysis and further processing.

    I tried using the dsum function but I couldn't get the desired result.

    Thanks in advance

    Zinger

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

    Re: Sub totals in query (Access 2002 -Sp-2)

    How are the dates/months stored in your table (i.e. what is the field name, what is the field type, what does it contain)?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub totals in query (Access 2002 -Sp-2)

    Hi Hans,

    Location and month are character fields with the rest being double as they need to contain financial revenue, expenditure type data eg. 1234.56, 56.67, -34.89
    the months are stored as JUL-04, AUG-04,SEP-04 ... JAN-05, FEB-05.... JUN-05
    which correspond to our financial year July to June.
    regards
    Zinger

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

    Re: Sub totals in query (Access 2002 -Sp-2)

    Let's say that your table is named tblRevenue. First, create a query based on this table that calculates a real date from the text value:

    SELECT Location, Month, CDate("1-" & [Month]) AS TheDate, Revenue
    FROM tblRevenue;

    Save this as - say - qryRevenue. Next, create a new qeury based on qryRevenue:

    SELECT Location, Month, Revenue, CDbl(DSum("Revenue","qryRevenue","Location='" & [Location] & "'")) AS LocationSubTotal, CDbl(DSum("Revenue","qryRevenue","Location='" & [Location] & "' AND TheDate<=#" & Format([TheDate],"mm/dd/yyyy") & "#")) AS RunningSubtotal, CDbl(DSum("Revenue","tblRevenue")) AS GrandTotal
    FROM qryRevenue
    ORDER BY Location, TheDate;

    Note: some browsers mess up the "less than or equal" used in the SQL. The screenshot below shows what was intended.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub totals in query (Access 2002 -Sp-2)

    Thanks Hans,
    I get the gist of the code and I'll try it out over this coming weekend.
    It is a far more elegant solution, and I hope the calcs in the query don't slow it down too much.

    Regards
    Zinger

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub totals in query (Access 2002 -Sp-2)

    Hi Hans,
    Finally got a chance to try out the code, and yes it does work and produces what I'm after.
    The only downside is that it runs so slow against the table (300k records).
    I'll definitely use it on smaller tables.
    Thanks
    Zinger

Posting Permissions

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