Creating Runtime Queries..... 2

[DCBBB]

Hi All,
Hope someone can help.....
I am designing an application that will allow users to select different aggregate levels and display the results. For instance, sales at my company can be aggregated (grouped by) store, region, buyer, department, week, quarter, etc. I want to give my users the flexibility of choosing the desired level or levels at runtime. Data for this will be fed from a single table, which has all the proper aggregates in place.I have experience with query by form, but only where the aggregate is consistent (for instance, only at the store level). But what if they don't want to see the data at the level predetermined by my query? And because there are so many possible aggregations, I don't want to have to create a separate query for each level. Hmmm...it sounds like I have to bag the queries, huh? Will I have to foray into the land of querydefs and sql? My experience there is limited to on-the-job hacking. BTW - I am using Access 97. Any ideas? TIA.
D

 

[mpastore]

Yes, you will need to get into dynamic SQL via VBA modules. Another option is to export to MS Excell and use a pivot table ... I've found this to be a very powerful way to change agg levels on the fly.

Mike Pastore

Hats off to (Roy) Harper

 

[JonFer]

If you have a form with each level of grouping (e.g. Region, Buyer, and Department) indicated by checkboxes, then you can make a group by query with these fields:

Region: IIf(Forms!form_name.RegionCheck,Region,Null)
Buyer: IIf(Forms!form_name.BuyerCheck,Buyer,Null)
Dept: IIf(Forms!form_name.DepartmentCheck,Department,Null)

Group by all of the "level" fields and add your aggregates. It should allow you to produce any level of aggregation. You'll see all of the possible levels in the results but that should be okay.