Mar 09 2022 02:33 PM
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");
Mar 10 2022 03:38 AM
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.
Mar 10 2022 04:37 PM
Mar 10 2022 07:44 PM
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.
Mar 12 2022 08:54 AM
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.
Mar 12 2022 05:39 PM
Solutionon Query1, you add the field you want to Sum().
Mar 12 2022 06:43 PM
Mar 12 2022 05:39 PM
Solution