hi,
I have below query. It’s working fine .. i just need to group the results by the ID
i tried to create group by ID, datex, [under 1], [1-6]
It gives me error message (status = 'Open' And [table1].datex <=#3/31/2022#) is not part of the segregated group by
What is the best way to group the results by ID:
How many for each ID distributed between under 1 and 1-6
SELECT ID,Datex, status, Ilf(DateDiff("m",[datex],#3/31/2022#) < 1,1,0) AS [Under 1], IIf(DateDiff("m"[datex],#3/31/2022#)>= 1 And
DateDiff("m" [datex], #3/31/2022#) <=6,1,0) AS [1 - 6]
FROM table1
WHERE status = 'Open' And [table1].datex <=#3/31/2022#
I have below query. It’s working fine .. i just need to group the results by the ID
i tried to create group by ID, datex, [under 1], [1-6]
It gives me error message (status = 'Open' And [table1].datex <=#3/31/2022#) is not part of the segregated group by
What is the best way to group the results by ID:
How many for each ID distributed between under 1 and 1-6
SELECT ID,Datex, status, Ilf(DateDiff("m",[datex],#3/31/2022#) < 1,1,0) AS [Under 1], IIf(DateDiff("m"[datex],#3/31/2022#)>= 1 And
DateDiff("m" [datex], #3/31/2022#) <=6,1,0) AS [1 - 6]
FROM table1
WHERE status = 'Open' And [table1].datex <=#3/31/2022#