Group By

[pushrodengine via AccessMonster.com]

How would I Group By all except for one possible entry.

Within the query “qryCallâ€, I need to group by all possibilities except for
“Canceled On-sceneâ€.

Thanks

 

[Bob Quintal]

How would I Group By all except for one possible entry.

Within the query “qryCallâ€, I need to group by all
possibilities except for “Canceled On-sceneâ€.

Thanks

Create a calculated field
CanceledOnScene: [fieldname] = "Canceled On-scene"

This will return true or false.

 

[rquintal]

How would I Group By all except for one possible entry.

Within the query "qryCall", I need to group by all possibilities except for
"Canceled On-scene".

Thanks

Create a calculated field
CanceledOnScene: [fieldname] = "Canceled On-scene"

This will return true or false.

Or you can create a calculated field using iif to change the true/
false to something else.

CanceledOnScene: iif([fieldname] = "Canceled On-scene", "Canceled on
scene", "All other codes")

 

[Arvin Meyer [MVP]]

Change the Group By Total Row to Expression, or one of the other choices in
the Totals Combo box.

 

[pushrodengine via AccessMonster.com]

Sorry, my question wasn’t really close to what I actually need.

Within the query “qryCallâ€, I need to Group By all the possibilities in the
field “Detailâ€, except for “Canceled On-sceneâ€.

Instead of trying to list all of the possibilities in the “Criteriaâ€, I would
just like to set the criteria to everything, but “Canceled On-sceneâ€.

Is this possible?

 

[Arvin Meyer [MVP]]

Yes:

SELECT ID, DateField, Address, City, Zip, State
FROM tblMyTable
WHERE Detail<>"Canceled On-scene"
GROUP BY ID, DateField, Address, City, Zip, State;

Notice that the Detail field is not in Group By. That's because after add
ing the criteria, I changed "Group By" to "Where". The query wizard then
automatically unchecked the Display check box.

 

[Michael Gramelspacher]

possible this would work:

SELECT a.col1, a.col.2, etc.
FROM (SELECT *
FROM YourTable
WHERE NOT (detail ='Canceled On-scene')) AS a
ORDER BY a.detail;

 

[pushrodengine via AccessMonster.com]

Thank you everyone for your help!