Group by Calculated Field?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • John
    #1

    Group by Calculated Field?

    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

    Tags: None
    • Allen Browne
      #2
      Re: Group by Calculated Field?

      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" <johnfrani@sbcg lobal.net> wrote in message
      news:1143042047 .357182.168570@ v46g2000cwv.goo glegroups.com.. .[color=blue]
      >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
      >[/color]


        Comment

        • John
          #3
          Re: Group by Calculated Field?

          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'),'Of ficers',[RankName] In
          ('Sergeant','Li eutenant','Insp ector','Assista nt
          Inspector','Cap tain','Commande r','Deputy Chief'),'Superv isors')

          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

            Comment

            Previous template Next
            Working...