Multiple record types into new custom group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
..
 
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!

John Spencer said:
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 said:
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?
Click to expand...
Click to expand...
 
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
..

Karen.Reedy said:
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!

John Spencer said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.

John Spencer said:
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
..

Karen.Reedy said:
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!

John Spencer said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
..

Karen.Reedy said:
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.

John Spencer said:
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
..

Karen.Reedy said:
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?
Click to expand...
Click to expand...
Click to expand...
 
That's exactly what I forgot to do. Thank you for your patient help, John!

John Spencer said:
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
..

Karen.Reedy said:
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.

John Spencer said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top