Grouping Together

[Guest]

Hi, Please help...

I have a query that looks at Customer Details and in this query is a field
with the Job Type, There are a number of Job Types and what I am trying to do
is group them together..

example if the Job Types are AS or IB then call it S&R or if the Job is AI
then call it CHI and if its anything else then call it OTHER. How can this be
done in a query?

Thanks,
Jez

 

[Nikos Yannacopoulos]

Jez,

You could use nested IIf's in a calculated field in the query to do it,
but it's not a neat solution. I would suggest the use of an extra table,
like:

JobType JobTypeGroup
AS S&R
IB S&R
AI CHI
xyz OTHER
jkl OTHER
etc.

Which you can include in your query, joining the JobType field to the
corresponding field in the main table, and utilizing the JobTypeGroup
field from tis table in your query results.

HTH,
Nikos

 

[John Spencer]

The best way would be to have a job types table in it that has this coding
in it. Then you join that table to the query. The advantage to this is
that all you need to do is to modify the table when the coding changes (add
new codes, change old codes, etc.)

You can also do this with a nested IIF expression in the query. Assuming
you are using the query grid

Field: CodeGroup: IIF([Job Type] = "AS" or [Job Type] = "IB","S & R",
IIF([Job Type]="AI","CHI","Other"))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..