Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Mar 09, 2022

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 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");

access
    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Mar 11, 2022
      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.
      • arnel_gp's avatar
        arnel_gp
        Steel Contributor
        Mar 11, 2022

        Tony2021 

        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.

Resources