How to create Drill-down forms/reports

Hello!

My user wants to see a summary level display of some information (up to 4 rows , each with 5 fields) which I obtain from SQL Svr. With each row, he would like the ability to click something (button, or ??) that would allow him to drill down from that row to the details supporting that row. Then, he would again like to drill down further if possible.

The hierarchy would be:
Date (the main driver for the query)
>Location (here are the 4 rows max - 1 for each plant)
>>Project
>>>Shift
>>>>(Details) - project activity list (1 to maybe 5 or 6)

Then to complicate it, he would like the ability to switch the hierarchy from Date/Location/Project/Shift to Date/Project/Location/Shift, changing the drill-downs to support that.

I am too green to figure this out in VB/SQL, and would welcome your kind assistance, or direction to another website. Examples are gratefully received, too!

Thanks in advance!
Don

Continued private discussion is here (http://www.xtremevbtalk.com/showthread.php?p=873000#post873000) regarding your account.

I would suggest that, as a start, you have the 4 criteria as comboboxes, and based on the selections made in those comboboxes, construct the where clause of an SQL statement used to pull data into a flexgrid, listview, listbox, etc. Does this help as a place to start?

I would suggest that, as a start, you have the 4 criteria as comboboxes, and based on the selections made in those comboboxes, construct the where clause of an SQL statement used to pull data into a flexgrid, listview, listbox, etc. Does this help as a place to start?

That would work, but after discussing this with the user manager, his preference is to see the summary rows (location), then to select one of these from the display to bring up the second level, then to select one from that display to go to the third level, etc. I will see if I can create something like an Excel filter to sit above the grid. Doyou think I could use a hierarchy grid to help out?

Don

The idea intrigued me a bit, so I threw together a very simple project to demonstrate using a grid to filter (using a small Access database, included). It has no error handling and isn't the most efficient, since it continually requeries the database, but might give you some ideas.

Double-click a cell in the grid to filter on that value.

Thanks, Laura - that example works great - now for a little twist from the original - Is there any way to make a summary row (preferably somehow highlighted or bolded) that has the summary of the selected drill-down?

For example (in your db), if I have (shift 1) or (bldg a) filtered, to have a summary row (preferably at the top) with the sum of the details?

Don

Sure, just begin populating your grid at row 2 rather than row 1 (adding an extra row at the end, of course), then after you have it filled, loop back through (or else keep a running total as you are populating the grid) and accumulate your summary data.

Sure, just begin populating your grid at row 2 rather than row 1 (adding an extra row at the end, of course), then after you have it filled, loop back through (or else keep a running total as you are populating the grid) and accumulate your summary data.

Well, Laura, that's what I did, and here I am a month later, with a finished project going through user testing. Thanks for your help!

Don