Solved Total each row in crosstab query?

[Gasman]

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

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;

 

[arnelgp]

you can drag the Total far right and save your query.

 

[ebs17]

...
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.

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

 

[Gasman]

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]

...
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.

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]

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]

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]

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]

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]

I think if you open the query, you can move the column and it will stay moved. But maybe not

 

[ebs17]

@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.

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

 

[Gasman]

Thanks. I will try that tomorrow. Out having a beer right now, as on holiday.

 

[Gasman]

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.

 

[Gasman]

you can drag the Total far right and save your query.

Even simpler. , however I am going to stick with the form option, for now ate least.