Group by not working in this sql (1 Viewer)

Joy83

Member
Local time
Today, 14:16
Joined
Jan 9, 2020
Messages
116
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#
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:16
Joined
Feb 19, 2013
Messages
16,553
If you just want to group by ID then remove Datex and Status from the SELECT part - In the query window, change Group By to Where

[1 - 6] is about the worst choice you can have for a field name and is likely to cause problems, Starting a name with a number, use of non alphanumeric characters and to a lesser extent use of spaces are all no-noes
 

Joy83

Member
Local time
Today, 14:16
Joined
Jan 9, 2020
Messages
116
Thanks for ur reply
What do u mean by:
In the query window, change Group By to Where
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:16
Joined
May 7, 2009
Messages
19,169
if ID is autonumber, then you cannot Group it on that field.
 

plog

Banishment Pending
Local time
Today, 16:16
Joined
May 11, 2011
Messages
11,611
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]

To GROUP BY, every field in the SELECT must be in the GROUP BY or be part of an aggregate function (e.g. MAX, SUM, COUNT, etc.). If you GROUP BY ID then what do you want to do with the other 3 fields?
 

Joy83

Member
Local time
Today, 14:16
Joined
Jan 9, 2020
Messages
116
My ID is not autonumber

So I built sql to get me the following table
Similar to above except that I added the unit

IDUnitUnder 11-6
34Unit a01
34Unit b10
34Unit a01
60Unit c10
60Unit c10

I want to modify it to be like this;

IDUnitUnder 11-6
34Unit a02
34Unit b10
60Unit c20
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:16
Joined
May 7, 2009
Messages
19,169
create a Total query from your first Query.

select ID, Unit, DCount("[Under 1]", "Query1", "ID = " & [ID] & " AND [UNIT] = '" & [UNIT]) AS [Under 1],
DCount("[1-6]", "Query1", "ID = " & [ID] & " AND [UNIT] = '" & [UNIT]) AS [1-6] from Query1
GROUP BY ID, UNIT, DCount("[Under 1]", "Query1", "ID = " & [ID] & " AND [UNIT] = '" & [UNIT]), DCount("[1-6]", "Query1", "ID = " & [ID] & " AND [UNIT] = '" & [UNIT])
 
Last edited:

plog

Banishment Pending
Local time
Today, 16:16
Joined
May 11, 2011
Messages
11,611
In design view of your existing query, change it to a totals query by clicking the sigma/summation sign in the ribbon. Then down below on each field, uncheck the status field so it does not show, change the Group By under [1] and [1-6] to Sum. That's it, run your query and you have your results.
 

Joy83

Member
Local time
Today, 14:16
Joined
Jan 9, 2020
Messages
116
In design view of your existing query, change it to a totals query by clicking the sigma/summation sign in the ribbon. Then down below on each field, uncheck the status field so it does not show, change the Group By under [1] and [1-6] to Sum. That's it, run your query and you have your results.
Perfect
It worked except one small problem
When I run it, it askes me to enter a a parameter for (under 1) and for (1-6)
When I click ok without entering any value for both it runs the query perfectly

how to avoid these parameters messages?
 

plog

Banishment Pending
Local time
Today, 16:16
Joined
May 11, 2011
Messages
11,611
Can you post your SQL? In design view, top left of the ribbon, switch to SQL view, copy and paste.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:16
Joined
Feb 19, 2002
Messages
42,976
If you are being prompted, you have a typo. Make sure your illegal column names are enclosed in square brackets.
 

Joy83

Member
Local time
Today, 14:16
Joined
Jan 9, 2020
Messages
116
it worked now
Thanks everyone for all your help
Much appreciated
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:16
Joined
Jan 23, 2006
Messages
15,364
Can you tell readers what you changed to make it work? It may help someone else.
Glad you have a solution.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:16
Joined
Feb 28, 2001
Messages
27,001
Ah, it makes sense. It means that at least one of the table names contained blanks or symbols that confused Access or SQL. Which is why we suggest that embedded spaces and special characters in object names are NOT your friends.
 

Users who are viewing this thread

Top Bottom