Crosstab Query - display more than 1 year

Super Contributor

hello, I have a crosstab query I made with the wizard.  It looks good but I want a year to be appended to the month.  I tried to adjust the format from the current DateAmendSentToBank],"mmm"  to DateAmendSentToBank],"mmm, yyyy" but it didnt seem to work. I only see months. how can i add the year to the month?  Please see pic below. 





TRANSFORM Sum(qryLCPendingIssue.AmountOfChange) AS SumOfAmountOfChange
SELECT tblProjectNames.ProjName, qryLCPendingIssue.letterofcreditID, qryLCPendingIssue.tblLCAmendHistory.AmendType, qryLCPendingIssue.LCName
FROM qryLCPendingIssue INNER JOIN tblProjectNames ON qryLCPendingIssue.ProjectName = tblProjectNames.IDProjName
GROUP BY tblProjectNames.ProjName, qryLCPendingIssue.letterofcreditID, qryLCPendingIssue.tblLCAmendHistory.AmendType, qryLCPendingIssue.LCName
PIVOT Format([DateAmendSentToBank],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

6 Replies


i think you need VBA to accomplish what you want and a form to show your pivot.

see Query1 (crosstab, with some months missing).

open form1 and see the code on Open event.

HI Arnel, thank you very much. I do have a question though. Does the output include more than 1 year? I added a few records in the table in 2022 and 2023 and I refreshed the form but I only see records for one of the years. if I had to choose a limit I would say 2 years if that helps. thank you. Looking forward to your response.


see the code on the Load event of the form.

the crosstab will display 2 years (starting year is this year).

you can adjust it as to How many years you like, but remember that

the query can only output maximum of 255 columns.



Hi Arnel, I am copying over to my db and have a follow up. When you have a sec, how could I also add as value the [AmountOfChange] so basically the display is with dates along the header but then within the data it shows the value of the [AmountOfChange]. I dont think I made that clear in my initial question.  Simply put I need to show the value of [AmountOfChange] instead of the count.  I hope that makes sense.  Let me know if any other questions. thank you very much.  




best response confirmed by Tony2021 (Super Contributor)


on Query1, you add the field you want to Sum().

I see. I didnt notice you attached a sample. I was checking from my phone. I actually did it myself. It was a little tricky since I could not modify the queryz. I was adding other ID's and such and once I ran the form it removed what I added. I did same as I did in query1 as I did in queryz and that worked. i am good now. Looks really nice. thanks again for the help. Have a good night!