Drill down query on access form

[Guest]

Hi,

I have a ‘detailed’ dataset I want to be able to drill down the information
to interrogate. I want to avoid writing multiple queries and forms to give
'speific information'.

Think of an un-normalised table with the following fields….

Country State Town Value

The detailed dataset contains the full list of records to examine of which
the field ‘value’ is SUM’ed and the other fields are grouped.

Country, State and Town represent a relational level of tier 1, tier 2 tier
3 if they were normalised. Through a combobox I would like to select the
summation of these levels. If I was to do this via individual queries I would
go;

Country:
Select Country, Sum(Value)
From mytable
Group by Country;

State:
Select State, Sum(Value)
From mytable
Group by State;

Town:
Select Town, Sum(Value)
From mytable
Group by Town;

However on the form I am restricted to 1 control source of the textboxes in
the detail section of the form. I don’t want to create separate queries and
assign these to separate forms.

I am thinking that I can assign the grouping to a textbox as an alias like
mygroup; I can switch between each if I define a recordset using IF or Case
statements in the combobox code. Eg queries.

Select Country As myGroup, Sum(Value)
From mytable
Group by Country;

or

State:
Select State as my Group, Sum(Value)
From mytable
Group by State;

But I don’t know how to do this. I lack understanding of the power and usage
of recordsets. Any ideas appreciated…Hope I have explained well.

Bruce

 

[Guest]

Bruce,

I would think that you would do the following for the all of the combo boxes
in your form in the AfterUpdate event of each.

1. Set the .value of the other 2 combo boxes, the non-selected ones to NULL.
2. Run your query in the AfterUpdate event based upon the value selected
3. Load that value into Value text box

I hope that this gives you a direction.

Jeff