Multiple record types into new custom group

[Karen.Reedy]

I have a table of remodel records that lists different scopes of work for
different sites. I want run one report and have all the different scopes of
work show up in groups organized by start date. The problem is that I need
to combine several different scopes of work (carpet, paint, expansion,
reduction, etc) under one generic heading of REMODELS for a report my boss
wants. Short of creating a separate query for these and running multiple
reports, is there a way to say IF the scope of work = (various abbreviations
here) then the group header should be REMODELS in the report?

 

[John Spencer]

In the underlying query you might use the following

Field: GroupScope: IIF([Scope] in
("Carpet","Paint","Expansion","Reduction"), "Remodel", [Scope])

Then in the report you could use that to group by

IN the sorting and grouping dialog you could try Grouping on
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Karen.Reedy]

Clarify for me, please. Do I put the IIF statement below in the criteria of
the Scope field? I know the basics, but obviously stuff like this is more
complicated than my grasp. Thanks!

In the underlying query you might use the following

Field: GroupScope: IIF([Scope] in
("Carpet","Paint","Expansion","Reduction"), "Remodel", [Scope])

Then in the report you could use that to group by

IN the sorting and grouping dialog you could try Grouping on
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table of remodel records that lists different scopes of work for
different sites. I want run one report and have all the different scopes
of
work show up in groups organized by start date. The problem is that I
need
to combine several different scopes of work (carpet, paint, expansion,
reduction, etc) under one generic heading of REMODELS for a report my boss
wants. Short of creating a separate query for these and running multiple
reports, is there a way to say IF the scope of work = (various
abbreviations
here) then the group header should be REMODELS in the report?

 

[John Spencer]

No, you are adding a new field to the query that you are using for the
report. It is a calculated field. You can then use that cacluclated in the
report's Sorting and Grouping dialog instead of [Scope] and in the control
that is displaying [Scope].

Your other option is to
-- Open the report in design mode
-- Add a textbox control in the group area
-- Set its control source to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])

-- Select View: Sorting and Grouping from the menu
-- Set the Grouping to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])


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

Clarify for me, please. Do I put the IIF statement below in the criteria
of
the Scope field? I know the basics, but obviously stuff like this is more
complicated than my grasp. Thanks!

In the underlying query you might use the following

Field: GroupScope: IIF([Scope] in
("Carpet","Paint","Expansion","Reduction"), "Remodel", [Scope])

Then in the report you could use that to group by

IN the sorting and grouping dialog you could try Grouping on
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table of remodel records that lists different scopes of work
for
different sites. I want run one report and have all the different
scopes
of
work show up in groups organized by start date. The problem is that I
need
to combine several different scopes of work (carpet, paint, expansion,
reduction, etc) under one generic heading of REMODELS for a report my
boss
wants. Short of creating a separate query for these and running
multiple
reports, is there a way to say IF the scope of work = (various
abbreviations
here) then the group header should be REMODELS in the report?

 

[Karen.Reedy]

Is there a limit to the # of operands that can be specified? If I use this
syntax in the report places: =IIf([ScopeofWork] In
("Concept","CPRPC","EXP","Insure","Insure/PC","Music","Div"),"Remodel",[ScopeofWork])
it turns all the other types into Insure work on the report. Just to mess
around, if I changed Insure above to Remodel, it changes everything to CPRPC.

No, you are adding a new field to the query that you are using for the
report. It is a calculated field. You can then use that cacluclated in the
report's Sorting and Grouping dialog instead of [Scope] and in the control
that is displaying [Scope].

Your other option is to
-- Open the report in design mode
-- Add a textbox control in the group area
-- Set its control source to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])

-- Select View: Sorting and Grouping from the menu
-- Set the Grouping to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])


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

Clarify for me, please. Do I put the IIF statement below in the criteria
of
the Scope field? I know the basics, but obviously stuff like this is more
complicated than my grasp. Thanks!

In the underlying query you might use the following

Field: GroupScope: IIF([Scope] in
("Carpet","Paint","Expansion","Reduction"), "Remodel", [Scope])

Then in the report you could use that to group by

IN the sorting and grouping dialog you could try Grouping on
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table of remodel records that lists different scopes of work
for
different sites. I want run one report and have all the different
scopes
of
work show up in groups organized by start date. The problem is that I
need
to combine several different scopes of work (carpet, paint, expansion,
reduction, etc) under one generic heading of REMODELS for a report my
boss
wants. Short of creating a separate query for these and running
multiple
reports, is there a way to say IF the scope of work = (various
abbreviations
here) then the group header should be REMODELS in the report?

 

[John Spencer]

DId you change the control source for the ScopeOfWork Control to the
formula. Also make sure you rename the control source to something other
than ScopeOfWork.

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

Is there a limit to the # of operands that can be specified? If I use
this
syntax in the report places: =IIf([ScopeofWork] In
("Concept","CPRPC","EXP","Insure","Insure/PC","Music","Div"),"Remodel",[ScopeofWork])
it turns all the other types into Insure work on the report. Just to mess
around, if I changed Insure above to Remodel, it changes everything to
CPRPC.

No, you are adding a new field to the query that you are using for the
report. It is a calculated field. You can then use that cacluclated in
the
report's Sorting and Grouping dialog instead of [Scope] and in the
control
that is displaying [Scope].

Your other option is to
-- Open the report in design mode
-- Add a textbox control in the group area
-- Set its control source to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"),
"Remodel",
[Scope])

-- Select View: Sorting and Grouping from the menu
-- Set the Grouping to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"),
"Remodel",
[Scope])


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

Clarify for me, please. Do I put the IIF statement below in the
criteria
of
the Scope field? I know the basics, but obviously stuff like this is
more
complicated than my grasp. Thanks!

:

In the underlying query you might use the following

Field: GroupScope: IIF([Scope] in
("Carpet","Paint","Expansion","Reduction"), "Remodel", [Scope])

Then in the report you could use that to group by

IN the sorting and grouping dialog you could try Grouping on
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table of remodel records that lists different scopes of work
for
different sites. I want run one report and have all the different
scopes
of
work show up in groups organized by start date. The problem is that
I
need
to combine several different scopes of work (carpet, paint,
expansion,
reduction, etc) under one generic heading of REMODELS for a report
my
boss
wants. Short of creating a separate query for these and running
multiple
reports, is there a way to say IF the scope of work = (various
abbreviations
here) then the group header should be REMODELS in the report?

 

[Karen.Reedy]

That's exactly what I forgot to do. Thank you for your patient help, John!

DId you change the control source for the ScopeOfWork Control to the
formula. Also make sure you rename the control source to something other
than ScopeOfWork.

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

Is there a limit to the # of operands that can be specified? If I use
this
syntax in the report places: =IIf([ScopeofWork] In
("Concept","CPRPC","EXP","Insure","Insure/PC","Music","Div"),"Remodel",[ScopeofWork])
it turns all the other types into Insure work on the report. Just to mess
around, if I changed Insure above to Remodel, it changes everything to
CPRPC.

No, you are adding a new field to the query that you are using for the
report. It is a calculated field. You can then use that cacluclated in
the
report's Sorting and Grouping dialog instead of [Scope] and in the
control
that is displaying [Scope].

Your other option is to
-- Open the report in design mode
-- Add a textbox control in the group area
-- Set its control source to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"),
"Remodel",
[Scope])

-- Select View: Sorting and Grouping from the menu
-- Set the Grouping to
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"),
"Remodel",
[Scope])


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

Clarify for me, please. Do I put the IIF statement below in the
criteria
of
the Scope field? I know the basics, but obviously stuff like this is
more
complicated than my grasp. Thanks!

:

In the underlying query you might use the following

Field: GroupScope: IIF([Scope] in
("Carpet","Paint","Expansion","Reduction"), "Remodel", [Scope])

Then in the report you could use that to group by

IN the sorting and grouping dialog you could try Grouping on
=IIF([Scope] in ("Carpet","Paint","Expansion","Reduction"), "Remodel",
[Scope])
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table of remodel records that lists different scopes of work
for
different sites. I want run one report and have all the different
scopes
of
work show up in groups organized by start date. The problem is that
I
need
to combine several different scopes of work (carpet, paint,
expansion,
reduction, etc) under one generic heading of REMODELS for a report
my
boss
wants. Short of creating a separate query for these and running
multiple
reports, is there a way to say IF the scope of work = (various
abbreviations
here) then the group header should be REMODELS in the report?