Can someone suggest an approach to this?

[andreas_udby]

I'm building an app that will let one of our execs drill down on employee data. He wants first to be able to select a particular group of employees, such as everyone in a certain department, pay band, performance code, function, etc. Some of these have many possibilities -- there are 21 departments, for example. All told, there are 54 different subgroups that he may want to view.

Then, he wants to be able to view each subgroup in one of eight different sort orders, such as by name alphabetically, by total compensation, by bonus, by pay band, etc.

So I have 54 possible groups times 8 possible orders = 432 possible combinations. Now, I really don't want to have to write a query for each of these possible combinations, but I'm not sure how else to proceed.

Since the same fields are being viewed each time, just with different records being displayed, I had hoped I could create a single "generic" query and then drop in the grouping and order using a macro or some complex VB module. I've got a pair of combo boves on my navigation screen, one with Grouping and the other with Order, just for that purpose. Now I just have to figure how to pass those parameters to a query.

Or am I barking up the wrong tree? Can someone suggest a better approach to this? Is there something in the Northwind database that might prove instructive?

Many thanks,
Andreas

 

[Jack Cowley]

Yikes!

I think this article will get you started. I have had good success with this approach but I have not used ORDER BY as part of the SQL code, but I don't see why you could not do that to get the sorting you want.

Query by form

Good luck!

Jack

 

[edtab]

I think you can use a combination of dropdown combobox and option box which will limit you to 8 queries only.

I've seen this done before. If you are interested for more details, let me know, I might be able to help.

edtab

 

[andreas_udby]

Absolutely! Any advice you can steer my way would be greatly, greatly appreciated. I'm pretty rusty on option buttons, but I can bone up on them in short order, especially if they'll help me work up a fix for this thing.

Thanks,
Andreas

 

[edtab]

On the same form, create a combo box which will allow you to isolate a group/category of employees. Next, create an option box and using "Case ...End Case", with 8 possible sort orders.

You would then create 8 queries based on the combined values of your dropdown combobox and optionbox.

Run your queries or reports (as you may require) in the "after update" event of your option group.

That's it in a nutshell. This should allow you are as many possible combination of departments and sort order as you need.

edtab