Group by Calculated Field?

[John]

I am using Access 2000.

One table in my database has a field called RankName.
Values inlcude: Officer, Sergeant, Lieutenant.
I need create a report that groups these three RankNames into two
groups: those that are Supervisors (Sergeant, Lieutenant) and those
that are Not Supervisors (Officers).

Is there a way I can create a calculated field in a query that does
something like this ...

If the value in the member's RankName field is Sergeant or Lieutenant
return a value of "Supervisor"
If not
Return a value of "Officer"

Then can I Group By the value in this calculated field: Supervisor or
Officer?

I am not sure that this is possible or how best to go about achieving
this grouping.

Any help would be appreciated.
Thanks
John

[Allen Browne]

How about adding another field to the table that has the RankNames field--a
yes/no field named IsSupervisor. Check the boxes for the ranks that are
supervisors.

Then add this field to your query, and in the report you can group by the
IsSupervisor field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <jo*******@sbcglobal.net> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

I am using Access 2000.

One table in my database has a field called RankName.
Values inlcude: Officer, Sergeant, Lieutenant.
I need create a report that groups these three RankNames into two
groups: those that are Supervisors (Sergeant, Lieutenant) and those
that are Not Supervisors (Officers).

Is there a way I can create a calculated field in a query that does
something like this ...

If the value in the member's RankName field is Sergeant or Lieutenant
return a value of "Supervisor"
If not
Return a value of "Officer"

Then can I Group By the value in this calculated field: Supervisor or
Officer?

I am not sure that this is possible or how best to go about achieving
this grouping.

Any help would be appreciated.
Thanks
John

[John]

Thanks for your response, Alllen but I wanted to avoid adding another
field to the DB.

I found a solution this morning in this forum...

Solution ...
In the Query, I added a new calculated field called JOB ...

Job: Switch([RankName] In ('Officer'),'Officers',[RankName] In
('Sergeant','Lieutenant','Inspector','Assistant
Inspector','Captain','Commander','Deputy Chief'),'Supervisors')

This "Switch" calculation created an alias for each of my two groups:
Officers and Supervisors.
My report can group on the alias.

The "Switch" expression/formula was new to me.
I found the original post in this forum.
To see it, search for Switch(

Thanks
John