Grouping by a field that a user selects on a form

[Zontar]

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?

 

[VicRauch]

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.