B
Bruce
I have setup a form returning query data that has the following fields.
Product, Region, Subregion, Market and Value. I am using controls input to
the where clause to filter records and the Value field is set to SUM.
Interchangably I want the ability on the one form to filer by either Region,
Subregion or Market as they represent various hierarchical levels of the same
thing. However if I bring all these fields into my forms query the SUM or
Value will only do so for the lowest common denominator, Market. Here is some
sample data;
Region Subregion Market Sum(Value)
1 1 1
1 1 2
1 2 3
1 2 3
2 3 4
2 3 5
If I was to filter on Region, then there would still be multple records of
subregion and Market. From a SQL perspective I understand why this is the
case....
The most logical way I can think of is to write a SQL string in VBA and
return this to the form.
Is this the right way to go aobut it or are there other options
Am I making the situation clearly.
Bruce
Product, Region, Subregion, Market and Value. I am using controls input to
the where clause to filter records and the Value field is set to SUM.
Interchangably I want the ability on the one form to filer by either Region,
Subregion or Market as they represent various hierarchical levels of the same
thing. However if I bring all these fields into my forms query the SUM or
Value will only do so for the lowest common denominator, Market. Here is some
sample data;
Region Subregion Market Sum(Value)
1 1 1
1 1 2
1 2 3
1 2 3
2 3 4
2 3 5
If I was to filter on Region, then there would still be multple records of
subregion and Market. From a SQL perspective I understand why this is the
case....
The most logical way I can think of is to write a SQL string in VBA and
return this to the form.
Is this the right way to go aobut it or are there other options
Am I making the situation clearly.
Bruce