Forum Discussion
Solved
Crosstab Query - display more than 1 year
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 Date...
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.