Grouping by a field that a user selects on a form

Zontar

New Member
Joined
Sep 9, 2006
Messages
1
Basically, I would like to design a query that will Group By multiple fields depending on the fields a user selects in 2 Combo Boxes. I would like to try to avoid doing this in VBA code, if at all possible.

So I have a form that has 2 combo boxes. The user can select a field from one combo box (i.e. Name, Account#, Source, Location, PaymentType, etc.), and another field from an identical combo box.

I would like two levels of grouping in my query based on what the user selects, but it just isn't feasible to have hundreds of queries for every possible combination of groupings.


I was hoping to have 2 "FieldName" tables that would be populated by the combo box. Then the query, based on what the user selects from those combo boxes, will group by those 2 "fields".

My FieldName Table1 looks like this:

Field: TransactionFieldName
Row1: [TransactionTable].[Name]

Instead of "[TransactionTable].[Name]", the [TransactionFieldName] field could also have the values

[TransactionTable].[Owner]
[TransactionTable].[Company]
[TransactionTable].[Facility]
[TransactionTable].[Location]

- depending on what the user selects in the Combo Box.


I originally tried doing nested IIF statements in my query, like

SelectedGroup1: IIF([Forms]![Viewer]![Group1]="No Grouping", "No Grouping", IIF([Forms]![Viewer]![Group1]="Name", [TransactionTable].[Name], IIF([Forms]![Viewer]![Group1]="Facility", [TransactionTable].[Facility], IIF([Forms]![Viewer]![Group1]="Location", [TransactionTable].[Location], IIF([Forms]![Viewer]![Group1]="Company", [TransactionTable].[Company], IIF([Forms]![Viewer]![Group1]="Month", [TransactionTable].[Month].......

and so on and so on...

However, I get an error message saying that my expression is too long. Even if I shorten it to only have a limited number of fields, the query is still so much slower than if I were grouping by the same field every time, - and I was testing this against a new database.

Instead of usine the nested IIF statements, I tried this calculated field:

SelectedGroup1: [FieldName].[TransactionFieldName]

which I hoped would equal "[TransactionTable].[Name]" and therefore reference that table in the query, but my results just looked like this:

[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
[TransactionTable].[Name]
..

When I wanted them to say:

Bob Parker
Mary Williams
John Smith
Randy Jones
Lana Maxwell

In other words, the "Name" field from the Transaction Table.

Is there an easy way to do this, and if not, what's the hard way?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I do not know of any way to do what you want to do without using VBA. Sorry.

I have do this type of thing before, and I found that by creating a work table to put the data into that needs to be sorted, and constructing this table specifically for sorting multiple ways, it worked great. The work table I created was a duplicate of the data I needed for the report, except that I preceeded all the fields with enough "sort" fields to handle any sort necessary. I named these fields Sort1, Sort2, Sort3, etc. That way, I could build the Append query to fill the work table with data, and all I needed to do was put the top level sort field, per user request for this report, into the "Sort1" field. Same for Sort2, etc. That way, the report that will print the necessary data can do it's sorting and level breaking on whatever data I put into the "Sort" fields. If I put CustomerName into Sort1, the report will break on CustomerName. But if I put Region into the Sort1 field, the report will break on Region. The only problem is that this way requires the use of VBA and you stated you did not want to use VBA.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,807
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top