Aggregating Data

I have a table called Observations which has a field called JOBTITLE. In
this field we list various types of MDs, RNs, HAs, etc. Most of my reports
require that I report this out separately.

However, I have a new report that requires me to lump some job titles
together. For example: MD and ARNP will now be called Provider; RNs, MAs and
HAs will now have the delightful name RN-MA-HA.

I have written into the JOBTITLE some rules for combing MDs and ARNPs;
however I can't seem to get it to go further and lump the RN-MA-HAs.

Provider: IIf([JobTitle] Like "MD" Or [JobTitle] Like "ARNP","MD-ARNP",[Job
Title])

Finally, the third group is everyone else who doesn't fit into the first two
groups.

What is the best way to make these lump together so I can then pull up
percentages for these groupings?

Many thanks

Create a new table called something like JobLumps. In it have 3 fields minimum:

JL_ID - Autonumber
JOBTITLE: Text and the foreign key to your Observations table.
Lumped: Text

JL_ID JOBTITLE Lumped
1 MD Provider
2 ARNP Provider
3 RN RN-MA-HA
4 MA RN-MA-HA
5 HA RN-MA-HA
6 CLERK Other
and so on.

Now you can join these tables together on the JOBTITLE field and group by
the 'lumped' field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"randlesc" wrote:

I have a table called Observations which has a field called JOBTITLE. In
this field we list various types of MDs, RNs, HAs, etc. Most of my reports
require that I report this out separately.

However, I have a new report that requires me to lump some job titles
together. For example: MD and ARNP will now be called Provider; RNs, MAs and
HAs will now have the delightful name RN-MA-HA.

I have written into the JOBTITLE some rules for combing MDs and ARNPs;
however I can't seem to get it to go further and lump the RN-MA-HAs.

Provider: IIf([JobTitle] Like "MD" Or [JobTitle] Like "ARNP","MD-ARNP",[Job
Title])

Finally, the third group is everyone else who doesn't fit into the first two
groups.

What is the best way to make these lump together so I can then pull up
percentages for these groupings?

Many thanks

On Mon, 1 Mar 2010 12:16:01 -0800, randlesc
wrote:

I have a table called Observations which has a field called JOBTITLE. In
this field we list various types of MDs, RNs, HAs, etc. Most of my reports
require that I report this out separately.

However, I have a new report that requires me to lump some job titles
together. For example: MD and ARNP will now be called Provider; RNs, MAs and
HAs will now have the delightful name RN-MA-HA.

I have written into the JOBTITLE some rules for combing MDs and ARNPs;
however I can't seem to get it to go further and lump the RN-MA-HAs.

Provider: IIf([JobTitle] Like "MD" Or [JobTitle] Like "ARNP","MD-ARNP",[Job
Title])

Finally, the third group is everyone else who doesn't fit into the first two
groups.

What is the best way to make these lump together so I can then pull up
percentages for these groupings?

Many thanks

For flexibility and maintainability, I'd suggest that you have a table of
Jobtitles (you may already have one); add a new field to it, GenericTitle, and
manually enter each jobtitle's corresponding generic title.
--

John W. Vinson [MVP]

randlesc wrote:
I have a table called Observations which has a field called JOBTITLE.
In this field we list various types of MDs, RNs, HAs, etc. Most of
my reports require that I report this out separately.

However, I have a new report that requires me to lump some job titles
together. For example: MD and ARNP will now be called Provider; RNs,
MAs and HAs will now have the delightful name RN-MA-HA.

I have written into the JOBTITLE some rules for combing MDs and ARNPs;
however I can't seem to get it to go further and lump the RN-MA-HAs.

Provider: IIf([JobTitle] Like "MD" Or [JobTitle] Like
"ARNP","MD-ARNP",[Job Title])

Finally, the third group is everyone else who doesn't fit into the
first two groups.

What is the best way to make these lump together so I can then pull up
percentages for these groupings?


I would suggest making this data driven: create a table called JobGroups
with two fields: JobTitle and JobGroup, and the combination of the two
fields can be the primary key.
Add the data into the table:
JobTitle JobGroup
MD Provider
ARNP Provider
RN RN-MA-HA
MA RN-MA-HA
HA RN-MA-HA
etc.

Now join this table to Observations using the JobTitle field and group
by JobGroup;

select jobgroup, aggregated_data
from Observations as o join JobGroups as g
On o.JobTitle = g.JobTitle
Group By jobgroup




--
HTH,
Bob Barrows