Results 1 to 9 of 9
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Column Totals in a crosstab query

    Hello gurus! I have read about a hundred different posts on this topic and I cannot get it to work. I have a crosstab query that shows totals per month as well as a row totals but I am trying to figure out how to add column Totals to it...basically total each month as well as each row.

    This is my crosstab query as it stands right now



    Code:
    TRANSFORM Nz(Sum(Outlook_all_data.Revenue))+0 AS Expr1
    SELECT Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency, Sum([Outlook_all_data].[Revenue])+0 AS Total
    FROM Outlook_all_data
    GROUP BY Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency
    PIVOT Outlook_all_data.[OL Month] In ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC');
    Any help you could provide would be very much appreciated

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create another query with totals and join both queries using UNION

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I have seen that before but can't get it to work.. This is basically my 2 queries unioned together but it throws a Syntax Error in From Clause Error

    Code:
    SELECT Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency, Sum([Outlook_all_data].[Revenue])+0 AS Total
    FROM Outlook_all_data
    GROUP BY Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency
    PIVOT Outlook_all_data.[OL Month] In ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
    UNION ALL
    SELECT "" AS Market_Center, "" AS [Rep Name], "" AS Agency, Sum(Outlook_Summary.JAN) AS SumOfJAN, Sum(Outlook_Summary.FEB) AS SumOfFEB, Sum(Outlook_Summary.MAR) AS SumOfMAR, Sum(Outlook_Summary.APR) AS SumOfAPR, Sum(Outlook_Summary.MAY) AS SumOfMAY, Sum(Outlook_Summary.JUN) AS SumOfJUN, Sum(Outlook_Summary.JUL) AS SumOfJUL, Sum(Outlook_Summary.AUG) AS SumOfAUG, Sum(Outlook_Summary.SEP) AS SumOfSEP, Sum(Outlook_Summary.Oct) AS SumOfOCT, Sum(Outlook_Summary.NOV) AS SumOfNOV, Sum(Outlook_Summary.DEC) AS SumOfDEC, Sum(Outlook_Summary.Total) AS SumOfTotal
    FROM Outlook_Summary
    GROUP BY "", "", "";

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Save the crosstab as a query, then use that instead.

  5. #5
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I had tried that but it was the crosstab format that gave me the 12 months and the total column and when I save it as a query the number of columns do not match. Is there a way to get the JAN-DEC + the total column using the basic select query?

  6. #6
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I am trying this but getting an error . It wants me to include the field OL Month which when I do cause the groups bys not to work properly

    Code:
    SELECT Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency, IIf([Outlook_all_data].[OL Month]='JAN',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS JAN, IIf([Outlook_all_data].[OL Month]='FEB',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS FEB, IIf([Outlook_all_data].[OL Month]='MAR',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS MAR, IIf([Outlook_all_data].[OL Month]='APR',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS APR, IIf([Outlook_all_data].[OL Month]='MAY',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS MAY, IIf([Outlook_all_data].[OL Month]='JUN',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS JUN, IIf([Outlook_all_data].[OL Month]='JUL',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS JUL, IIf([Outlook_all_data].[OL Month]='AUG',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS AUG, IIf([Outlook_all_data].[OL Month]='SEP',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS SEP, IIf([Outlook_all_data].[OL Month]='OCT',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS OCT, IIf([Outlook_all_data].[OL Month]='NOV',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS NOV, IIf([Outlook_all_data].[OL Month]='DEC',Nz(Sum([Outlook_all_data].[Revenue]))+0,0) AS [DEC], Sum([Outlook_all_data].[Revenue])+0 AS Total, Outlook_all_data.[OL Month]
    FROM Outlook_all_data
    GROUP BY Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency, Outlook_all_data.[OL Month];

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The way that I got it working was:
    - query 1 crosstab
    - query 2 - input query 1, sum all amount
    - query 3 - union query 1 and query 2

  8. #8
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I finally got it to work

    here is how I did it. Thanks for the point in the right direction

    Code:
    SELECT Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency, Sum(IIf([Outlook_all_data].[OL Month]='JAN',[Outlook_all_data].[Revenue],0)) AS JAN, Sum(IIf([Outlook_all_data].[OL Month]='FEB',[Outlook_all_data].[Revenue],0)) AS FEB, Sum(IIf([Outlook_all_data].[OL Month]='MAR',[Outlook_all_data].[Revenue],0)) AS MAR, Sum(IIf([Outlook_all_data].[OL Month]='APR',[Outlook_all_data].[Revenue],0)) AS APR, Sum(IIf([Outlook_all_data].[OL Month]='MAY',[Outlook_all_data].[Revenue],0)) AS MAY, Sum(IIf([Outlook_all_data].[OL Month]='JUN',[Outlook_all_data].[Revenue],0)) AS JUN, Sum(IIf([Outlook_all_data].[OL Month]='JUL',[Outlook_all_data].[Revenue],0)) AS JUL, Sum(IIf([Outlook_all_data].[OL Month]='AUG',[Outlook_all_data].[Revenue],0)) AS AUG, Sum(IIf([Outlook_all_data].[OL Month]='SEP',[Outlook_all_data].[Revenue],0)) AS SEP, Sum(IIf([Outlook_all_data].[OL Month]='OCT',[Outlook_all_data].[Revenue],0)) AS OCT, Sum(IIf([Outlook_all_data].[OL Month]='NOV',[Outlook_all_data].[Revenue],0)) AS NOV, Sum(IIf([Outlook_all_data].[OL Month]='DEC',[Outlook_all_data].[Revenue],0)) AS [DEC], Sum([Outlook_all_data].[Revenue])+0 AS Total
    FROM Outlook_all_data
    GROUP BY Outlook_all_data.Market_Center, Outlook_all_data.[Rep Name], Outlook_all_data.Agency;
    UNION ALL
    SELECT "" AS Market_Center, "" AS [Rep Name], "" AS Agency, Sum(Outlook_Summary.JAN) AS SumOfJAN, Sum(Outlook_Summary.FEB) AS SumOfFEB, Sum(Outlook_Summary.MAR) AS SumOfMAR, Sum(Outlook_Summary.APR) AS SumOfAPR, Sum(Outlook_Summary.MAY) AS SumOfMAY, Sum(Outlook_Summary.JUN) AS SumOfJUN, Sum(Outlook_Summary.JUL) AS SumOfJUL, Sum(Outlook_Summary.AUG) AS SumOfAUG, Sum(Outlook_Summary.SEP) AS SumOfSEP, Sum(Outlook_Summary.Oct) AS SumOfOCT, Sum(Outlook_Summary.NOV) AS SumOfNOV, Sum(Outlook_Summary.DEC) AS SumOfDEC, Sum(Outlook_Summary.Total) AS SumOfTotal
    FROM Outlook_Summary
    GROUP BY "", "", "";

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Well done!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Crosstab query of monthly account totals
    By chasew in forum Queries
    Replies: 2
    Last Post: 08-11-2015, 11:12 PM
  2. Replies: 5
    Last Post: 07-12-2015, 09:33 PM
  3. Crosstab query totals row
    By cbende2 in forum Access
    Replies: 7
    Last Post: 06-22-2015, 10:06 AM
  4. Replies: 2
    Last Post: 01-08-2015, 12:41 AM
  5. Create a "Totals" row for each column in crosstab
    By accessnewbie352 in forum Queries
    Replies: 13
    Last Post: 01-06-2015, 12:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums