Want to show data in separate columns for 3 previous years

[jmuirman1]

how can i show 3 or 4 columns of annual data for same category/field in same report?

main field is "Set-up Date"

so, Set-up Date 2017; Set-up Date 2018 and Set-up Date 2019?

Thanks,

John

[Minty]

Look into a Cross tab query, and format your date field something like

Format([Yourdate],"yyyy")

in the grouping.

[ArviLaanemets]

Maybe like this:

SELECT k.Key1, k.Key2, t17.Cnt17, t17.Sum17, t18.Cnt18, t18.Sum18, t19.Cnt19, t19.Sum19
FROM 
(((SELECT DISTINCT Key1, Key2 FROM YourTable WHERE Year(SetUpDate) BETWEEN 2017 AND 2019) AS k
LEFT JOIN (SELECT Key1, Key2, Count(Key1) AS Cnt17, SUM(SomeField) AS Sum17) FROM YourTable WHERE Year(SetupDate)=2017 GROUP BY Key1, Key2) AS t17 ON t17.Key1 = k.Key1 AND t17.Key2 = k.Key2)
LEFT JOIN (SELECT Key1, Key2, Count(Key1) AS Cnt18, SUM(SomeField) AS Sum18) FROM YourTable WHERE Year(SetupDate)=2018 GROUP BY Key1, Key2) AS t18 ON t18.Key1 = k.Key1 AND t18.Key2 = k.Key2)
LEFT JOIN (SELECT Key1, Key2, Count(Key1) AS Cnt19, SUM(SomeField) AS Sum19) FROM YourTable WHERE Year(SetupDate)=2019 GROUP BY Key1, Key2) AS t19 ON t19.Key1 = k.Key1 AND t19.Key2 = k.Key2

[jmuirman1]

Thanks - I think I'm heading in the right direction now.

Having trouble though with having 2017,2018 and 2019 columns - do i need 3 queries?

if I use -- Between #1/1/2018# And #1/1/2019# in one column and Between #1/1/2017# And #1/1/2018# in the second column of course no results show up. Do i need some type of "or" function?

Thanks,

John