Solved Total each row in crosstab query? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 04:39
Joined
Sep 21, 2011
Messages
14,528
Hi all,
I have just created this crosstab query to see my daily intake for my diabetes monitoring.
How would I get a total column at the end that sums each of the values per day?
Crosstabs have never been my thing :-( When I try to respond to one error message, it generates another that appears to contracdict the first, well to me at least. :-(

I would really like to have it at the far right.
I have managed to get a total, but always as the second column.

TIA
Code:
TRANSFORM Format(Sum(tblDaily.CarbsCalc),"Fixed") AS SumOfCarbsCalc
SELECT tblDaily.DailyDate
FROM tblDaily INNER JOIN tblLookUp ON tblDaily.MealTypeFK = tblLookUp.LookUpID
GROUP BY tblDaily.DailyDate
PIVOT tblLookUp.LookUpValue;
1707837831993.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:39
Joined
May 7, 2009
Messages
19,248
you can drag the Total far right and save your query.
 
Last edited:

ebs17

Well-known member
Local time
Today, 05:39
Joined
Feb 7, 2020
Messages
2,002
Code:
...
SELECT tblDaily.DailyDate, SUM(tblDaily.CarbsCalc) As DailyTotal
...
You can only swap to the left of the PIVOT columns within the crosstab query.

I would really like to have it at the far right.
This can only be achieved if you create the crosstab query without row totals, also create a query with the daily totals and join these two queries using the current date.
SQL:
SELECT
   CT.*,
   Q.DailyTotal
FROM
   (
TRANSFORM
   Format(SUM(tblDaily.CarbsCalc), "Fixed") AS SumOfCarbsCalc
SELECT
   tblDaily.DailyDate
FROM
   tblDaily
      INNER JOIN tblLookUp
      ON tblDaily.MealTypeFK = tblLookUp.LookUpID
GROUP BY
   tblDaily.DailyDate
PIVOT
   tblLookUp.LookUpValue) AS CT
      INNER JOIN
         (
            SELECT
               tblDaily.DailyDate,
               SUM(tblDaily.CarbsCalc) AS DailyTotal
            FROM
               tblDaily
                  INNER JOIN tblLookUp
                  ON tblDaily.MealTypeFK = tblLookUp.LookUpID
            GROUP BY
               tblDaily.DailyDate
         ) AS Q
         ON CT.DailyDate = Q.DailyDate
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 04:39
Joined
Sep 21, 2011
Messages
14,528
you can drag the Total far right and save your query.
Thank you Arnel, that would be the easiest option, use a form.

Do not know why I did not think of that. :(
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:39
Joined
Sep 21, 2011
Messages
14,528
Code:
...
SELECT tblDaily.DailyDate, SUM(tblDaily.CarbsCalc) As DailyTotal
...
You can only swap to the left of the PIVOT columns within the crosstab query.


This can only be achieved if you create the crosstab query without row totals, also create a query with the daily totals and join these two queries using the current date.
SQL:
SELECT
   CT.*,
   Q.DailyTotal
FROM
   (
TRANSFORM
   Format(SUM(tblDaily.CarbsCalc), "Fixed") AS SumOfCarbsCalc
SELECT
   tblDaily.DailyDate
FROM
   tblDaily
      INNER JOIN tblLookUp
      ON tblDaily.MealTypeFK = tblLookUp.LookUpID
GROUP BY
   tblDaily.DailyDate
PIVOT
   tblLookUp.LookUpValue) AS CT
      INNER JOIN
         (
            SELECT
               tblDaily.DailyDate,
               SUM(tblDaily.CarbsCalc) AS DailyTotal
            FROM
               tblDaily
                  INNER JOIN tblLookUp
                  ON tblDaily.MealTypeFK = tblLookUp.LookUpID
            GROUP BY
               tblDaily.DailyDate
         ) AS Q
         ON CT.DailyDate = Q.DailyDate
Thank you @ebs17
I got an error on the FROM as it is posted, and using a form is much easier anyway.
 

ebs17

Well-known member
Local time
Today, 05:39
Joined
Feb 7, 2020
Messages
2,002
Then you'll probably need to save the crosstab query as a saved query and then include it in the summary query. You should be able to do this independently.
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:39
Joined
Sep 21, 2011
Messages
14,528
Then you'll probably need to save the crosstab query as a saved query and then include it in the summary query. You should be able to do this independently.
Yes, I was hoping for a one query solution TBH, and the using a form method is much easier, tidier even.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:39
Joined
Feb 19, 2002
Messages
43,587
If you use the crosstab wizard, it automatically creates the column sum for you. Here is an example

TRANSFORM Sum(qAnnualRecap.AmtBilled) AS SumOfAmtBilled
SELECT qAnnualRecap.YrBilled, Sum(qAnnualRecap.AmtBilled) AS [Total Of AmtBilled]
FROM qAnnualRecap
GROUP BY qAnnualRecap.YrBilled
PIVOT Format([PeriodEndDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:39
Joined
Sep 21, 2011
Messages
14,528
Yes, Pat, I managed that manually, modifying the SQL, but I preferred to have the total at the far right of the result.
With the form method, I could also have Breakfast, Lunch, Dinner, Drink, Snack and Total order.

Much easier solution. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:39
Joined
Feb 19, 2002
Messages
43,587
I think if you open the query, you can move the column and it will stay moved. But maybe not
 
Last edited:

ebs17

Well-known member
Local time
Today, 05:39
Joined
Feb 7, 2020
Messages
2,002
@Gasman
The PIVOT columns are sorted. If necessary, you could also work with fixed columns. So if you modify the base query before creating the PIVOT view so that it already contains records with "Total" in the LookUpValue field and the total values in the CarbsCalc field, the right-aligned display could work.
SQL:
TRANSFORM
   Format(SUM(Q.CarbsCalc), "Fixed") AS SumOfCarbsCalc
SELECT
   Q.DailyDate
FROM
   (
      SELECT
         D.DailyDate,
         D.CarbsCalc,
         L.LookUpValue
      FROM
         tblDaily AS D
            INNER JOIN tblLookUp AS L
            ON D.MealTypeFK = L.LookUpID
      UNION ALL SELECT
         DailyDate,
         SUM(CarbsCalc) AS Total,
         "Total" AS LookUpValue
      FROM
         tblDaily
      GROUP BY
         DailyDate
   ) AS Q
GROUP BY
   Q.DailyDate
PIVOT
   Q.LookUpValue
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 04:39
Joined
Sep 21, 2011
Messages
14,528
Thanks. I will try that tomorrow. Out having a beer right now, as on holiday. :)
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:39
Joined
Sep 21, 2011
Messages
14,528
I could not wait. :)
Pleased to say that works out of the box. Thank you. Something to try and remember for a future issue, but for this problem, I am going to stick with the form option.

Thank you to all for the replies. Much appreciated.
 

Users who are viewing this thread

Top Bottom