Create a report that groups based on what user selects

  • Thread starter Thread starter Heidi
  • Start date Start date
H

Heidi

I have a form that lists 8 items. The user will rank
from 1 to as many groups as they would like to see on the
report by use of a check box beside each item. I would
like to create a dynamic report that based off of what
they have selected will create the groups within the
report. I also need it to subtotal counters in each
group. Does anyone have any ideas?? This would save me
having to create tons of hard coded grouped reports.
 
How are the 8 items listed? Are these text boxes, check boxes, combo
boxes,...? Are there values for each of the 8 items? Are there field names
stored anywhere?
 
I was going to do a 2 list boxes. 1 to show what fields
they could choose to group by and the other box to store
what they have choosen once they have double clicked on
the item. By doing this the number of items they could
choose wouldn't be hardcoded on the form itself. They
would be field names.
 
I would create as many single select list boxes (or combo boxes) that you
want to allow users to group by. Make sure your row source of each contains
an integer such as (you can set the number column width to 0 so only the
field name shows).
1 FieldA Title
2 FieldB Title
3 FieldC Title
4 FieldD Title
Name you list boxes lboGrp1, lboGrp2, lboGrp3,... Assuming your form name is
"frmRptSlct"
Then in your query, create calculated columns
Grp1: Choose(Forms!frmRptSlct!lboGrp1, [FieldA], [FieldB], [FieldC]...)
Do the same for other groupings. This allows you to set your report sorting
and grouping to Grp1, Grp2,...
 
Thank you for the advice. It made me think of a couple
of things. The only problem I have is that the groupings
are based on categories like:
Company
Program
County

There may be 32 companies, 40 programs, and 50 counties.
Your option to use the choose function in the query would
be good but there are to many of each to hard code.

I would like to have it group by these categories. So if
the user chooses to group on Program then Company..they
are not choosing the specific company or program but the
overall category. Sorry I hope I am not confusing you.
I appreciate the help.

-----Original Message-----
I would create as many single select list boxes (or combo boxes) that you
want to allow users to group by. Make sure your row source of each contains
an integer such as (you can set the number column width to 0 so only the
field name shows).
1 FieldA Title
2 FieldB Title
3 FieldC Title
4 FieldD Title
Name you list boxes lboGrp1, lboGrp2, lboGrp3,... Assuming your form name is
"frmRptSlct"
Then in your query, create calculated columns
Grp1: Choose(Forms!frmRptSlct!lboGrp1, [FieldA], [FieldB], [FieldC]...)
Do the same for other groupings. This allows you to set your report sorting
and grouping to Grp1, Grp2,...
--
Duane Hookom
MS Access MVP


I was going to do a 2 list boxes. 1 to show what fields
they could choose to group by and the other box to store
what they have choosen once they have double clicked on
the item. By doing this the number of items they could
choose wouldn't be hardcoded on the form itself. They
would be field names. check
boxes, combo Are
there field names on
the save
me
Click to expand...


.
Click to expand...
 
Did you try my solution? It doesn't choose a specific company or program.
All it does is allow the user to select which field values are displayed in
the column Grp1, Grp2,.. These values are not filtered.

--
Duane Hookom
MS Access MVP


Heidi said:
Thank you for the advice. It made me think of a couple
of things. The only problem I have is that the groupings
are based on categories like:
Company
Program
County

There may be 32 companies, 40 programs, and 50 counties.
Your option to use the choose function in the query would
be good but there are to many of each to hard code.

I would like to have it group by these categories. So if
the user chooses to group on Program then Company..they
are not choosing the specific company or program but the
overall category. Sorry I hope I am not confusing you.
I appreciate the help.

-----Original Message-----
I would create as many single select list boxes (or combo boxes) that you
want to allow users to group by. Make sure your row source of each contains
an integer such as (you can set the number column width to 0 so only the
field name shows).
1 FieldA Title
2 FieldB Title
3 FieldC Title
4 FieldD Title
Name you list boxes lboGrp1, lboGrp2, lboGrp3,... Assuming your form name is
"frmRptSlct"
Then in your query, create calculated columns
Grp1: Choose(Forms!frmRptSlct!lboGrp1, [FieldA], [FieldB], [FieldC]...)
Do the same for other groupings. This allows you to set your report sorting
and grouping to Grp1, Grp2,...
--
Duane Hookom
MS Access MVP


I was going to do a 2 list boxes. 1 to show what fields
they could choose to group by and the other box to store
what they have choosen once they have double clicked on
the item. By doing this the number of items they could
choose wouldn't be hardcoded on the form itself. They
would be field names.
-----Original Message-----
How are the 8 items listed? Are these text boxes, check
boxes, combo
boxes,...? Are there values for each of the 8 items? Are
there field names
stored anywhere?

--
Duane Hookom
MS Access MVP


message
I have a form that lists 8 items. The user will rank
from 1 to as many groups as they would like to see on
the
report by use of a check box beside each item. I would
like to create a dynamic report that based off of what
they have selected will create the groups within the
report. I also need it to subtotal counters in each
group. Does anyone have any ideas?? This would save
me
having to create tons of hard coded grouped reports.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Duane
I did try your example but I am having trouble
understanding the query end. when I put in the field
grp1: choose(forms!frmRptSlct!cmbgrp1, ?? into the query,
I don't know what your [fieldA] is referring to. When i
looked it up the choose function said you must list all
of the choices thus the reason for my previous post. I
am using combo boxes.

If I list the actual field name, the query bombs and
gives me a parameter box.
-----Original Message-----
Did you try my solution? It doesn't choose a specific company or program.
All it does is allow the user to select which field values are displayed in
the column Grp1, Grp2,.. These values are not filtered.

--
Duane Hookom
MS Access MVP


Thank you for the advice. It made me think of a couple
of things. The only problem I have is that the groupings
are based on categories like:
Company
Program
County

There may be 32 companies, 40 programs, and 50 counties.
Your option to use the choose function in the query would
be good but there are to many of each to hard code.

I would like to have it group by these categories. So if
the user chooses to group on Program then Company..they
are not choosing the specific company or program but the
overall category. Sorry I hope I am not confusing you.
I appreciate the help.

-----Original Message-----
I would create as many single select list boxes (or combo boxes) that you
want to allow users to group by. Make sure your row source of each contains
an integer such as (you can set the number column
Click to expand...
width
to 0 so only the
field name shows).
1 FieldA Title
2 FieldB Title
3 FieldC Title
4 FieldD Title
Name you list boxes lboGrp1, lboGrp2, lboGrp3,... Assuming your form name is
"frmRptSlct"
Then in your query, create calculated columns
Grp1: Choose(Forms!frmRptSlct!lboGrp1, [FieldA], [FieldB], [FieldC]...)
Do the same for other groupings. This allows you to
Click to expand...
set
your report sorting
and grouping to Grp1, Grp2,...
--
Duane Hookom
MS Access MVP


I was going to do a 2 list boxes. 1 to show what fields
they could choose to group by and the other box to store
what they have choosen once they have double clicked on
the item. By doing this the number of items they could
choose wouldn't be hardcoded on the form itself. They
would be field names.
-----Original Message-----
How are the 8 items listed? Are these text boxes, check
boxes, combo
boxes,...? Are there values for each of the 8
Click to expand...
items?
Are
there field names
stored anywhere?

--
Duane Hookom
MS Access MVP


message
I have a form that lists 8 items. The user will rank
from 1 to as many groups as they would like to
Click to expand...
see
on
the
report by use of a check box beside each item. I would
like to create a dynamic report that based off of what
they have selected will create the groups within the
report. I also need it to subtotal counters in each
group. Does anyone have any ideas?? This would save
me
having to create tons of hard coded grouped reports.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
FieldA is a representative of your first field name possibly Company. I
expected that since you didn't provide actual field names until a later
reply, that you would understand to substitute your field names for FieldA,
FieldB, FieldC,...

--
Duane Hookom
MS Access MVP


Heidi said:
Duane
I did try your example but I am having trouble
understanding the query end. when I put in the field
grp1: choose(forms!frmRptSlct!cmbgrp1, ?? into the query,
I don't know what your [fieldA] is referring to. When i
looked it up the choose function said you must list all
of the choices thus the reason for my previous post. I
am using combo boxes.

If I list the actual field name, the query bombs and
gives me a parameter box.
-----Original Message-----
Did you try my solution? It doesn't choose a specific company or program.
All it does is allow the user to select which field values are displayed in
the column Grp1, Grp2,.. These values are not filtered.

--
Duane Hookom
MS Access MVP


Thank you for the advice. It made me think of a couple
of things. The only problem I have is that the groupings
are based on categories like:
Company
Program
County

There may be 32 companies, 40 programs, and 50 counties.
Your option to use the choose function in the query would
be good but there are to many of each to hard code.

I would like to have it group by these categories. So if
the user chooses to group on Program then Company..they
are not choosing the specific company or program but the
overall category. Sorry I hope I am not confusing you.
I appreciate the help.


-----Original Message-----
I would create as many single select list boxes (or
combo boxes) that you
want to allow users to group by. Make sure your row
source of each contains
an integer such as (you can set the number column width
to 0 so only the
field name shows).
1 FieldA Title
2 FieldB Title
3 FieldC Title
4 FieldD Title
Name you list boxes lboGrp1, lboGrp2, lboGrp3,...
Assuming your form name is
"frmRptSlct"
Then in your query, create calculated columns
Grp1: Choose(Forms!frmRptSlct!lboGrp1, [FieldA],
[FieldB], [FieldC]...)
Do the same for other groupings. This allows you to set
your report sorting
and grouping to Grp1, Grp2,...
--
Duane Hookom
MS Access MVP


message
I was going to do a 2 list boxes. 1 to show what
fields
they could choose to group by and the other box to
store
what they have choosen once they have double clicked on
the item. By doing this the number of items they could
choose wouldn't be hardcoded on the form itself. They
would be field names.
-----Original Message-----
How are the 8 items listed? Are these text boxes,
check
boxes, combo
boxes,...? Are there values for each of the 8 items?
Are
there field names
stored anywhere?

--
Duane Hookom
MS Access MVP


message
I have a form that lists 8 items. The user will
rank
from 1 to as many groups as they would like to see
on
the
report by use of a check box beside each item. I
would
like to create a dynamic report that based off of
what
they have selected will create the groups within the
report. I also need it to subtotal counters in each
group. Does anyone have any ideas?? This would
save
me
having to create tons of hard coded grouped reports.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top