Row total in a cross tab query

[Khalil Handal]

Hi to all,
I have a cross tab query that show totals per month.
The outcome of the query is:



The cross tab query is:

TRANSFORM NZ(Count(tblBorrowedBooks.DateBorrowed),0) AS CountOfDateBorrowed
SELECT Year([DateBorrowed]) AS SelectYear
FROM tblBorrowedBooks
GROUP BY Year([DateBorrowed])
ORDER BY Year([DateBorrowed]) DESC
PIVOT Format([DateBorrowed],"mmm") In (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec);

I want to add a new column after Dec to show the total for each year (example: horizontal total for the year 2024).


Can this be done? How?

Thank you
Khalil

[Welshgasman]

Like this you mean?

[CJ_London]

just include a ttlCount column

TRANSFORM NZ(Count(tblBorrowedBooks.DateBorrowed),0) AS CountOfDateBorrowed
SELECT Year([DateBorrowed]) AS SelectYear, count(dateBorrowed) as ttlCount
FROM tblBorrowedBooks
GROUP BY Year([DateBorrowed])
ORDER BY Year([DateBorrowed]) DESC
PIVOT Format([DateBorrowed],"mmm") In (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec);

it will appear before your Jan-Dec figures, but you can have it after as part of your visual formatting in a form or report

[Khalil Handal]

Hi,
Thank you very much.
Khalil