Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Unanswered: Subtotal Query on each change on a field

    I don't know if this is even possible. I have a query that pulls from a couple different queries and tables. What I'm trying to do is at each change in the year, I want to do a subtotal in the query. For example:
    2009 Nov 500
    2009 Dec 500
    2009 Total 1000
    2010 Jan 500
    2010 Feb 500
    .
    .
    2010 Total #
    Here is what my query currently looks like:
    SELECT qryActiveLeadsAllEmps.State, qryActiveLeadsAllEmps.Status, qryActiveLeadsAllEmps.Year, qryActiveLeadsAllEmps.Month, Sum(qryActiveLeadsAllEmps.[No of Emp]) AS [SumOfNo of Emp], Sum(qryActiveLeadsLT5.[Count of Emp]) AS [SumOfCount of Emp], Sum(qryActiveLeadsMTE5.[Count of Emp]) AS [SumOfCount of Emp1]
    FROM ((qryActiveLeadsAllEmps LEFT JOIN qryActiveLeadsLT5 ON (qryActiveLeadsAllEmps.Year = qryActiveLeadsLT5.Year) AND (qryActiveLeadsAllEmps.Month = qryActiveLeadsLT5.Month)) LEFT JOIN qryActiveLeadsMTE5 ON (qryActiveLeadsAllEmps.Year = qryActiveLeadsMTE5.Year) AND (qryActiveLeadsAllEmps.Month = qryActiveLeadsMTE5.Month)) LEFT JOIN tblMonth ON qryActiveLeadsAllEmps.Month = tblMonth.Month
    GROUP BY qryActiveLeadsAllEmps.State, qryActiveLeadsAllEmps.Status, qryActiveLeadsAllEmps.Year, qryActiveLeadsAllEmps.Month, tblMonth.MonthNumber
    ORDER BY qryActiveLeadsAllEmps.Year, tblMonth.MonthNumber;

    Please help!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Typically you would do that on a report, but it can be done in a query:

    QueryWithSubTotals.mdb - Roger's Access Library
    Paul

Posting Permissions

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