Using a grouped sum for a calculation

[Jeff]

I have a query in which I have different lengths that
make up a the total length for a street. I need to make a
calculation that will take each individual lenght for a
street segment and divide it by the sum of the segment
lengths for each street. Then multiply that total by
another field. See example:
100 1st St100 1st St length/ total length 1st St)* TN
200 1st St200 1st St length/ total length 1st St)* TN

800 Amy St800 1st St length/ total length Amy St)* TN
900 Amy St900 1st St length/ total length Amy St)* TN

My problem is having the formula only do a total for each
particular st.

I tried creating a separate query with the street totals
and then link it to other query my working query is based
on. The problem here is that when my working query is
based on two linked queries editing is locked.

 

[Michel Walsh]

Hi,

If the StreetID is a name rather than a number, then you could use:

Length / DSum( "Length", "tableNameHere", "StreetName=""" & StreetName & """" ) * Tons



Hoping it may help,
Vanderghast, Access MVP


==================
Hi Mike,
Thanks for your reply. I implemented your suggestion but the problem is still occurring. What is
occurring is that the sum is equalling the sum of all the streets in the query instead of summing
only the ones that relate to that partcular street. Please see an example below:

Streetname Address Length Tons
Amy St 100 Amy St 100 20
Amy St 200 Amy St 98 20
1st St 400 1st St 220 50
1st ST 500 1st St 130 50
What I need is for the formula to be set so that it only Sums the total Length for "Amy St" when
I am in a record that has "Amt St" as the Streetname and it will Sum the total Length for "1st St"
when I am in a record with "1st St" as the Streetname.

The formula would take the ('Length for the record' / 'Sum of Lengths where Streetname =
Streetname of the record you are in') * Tons.

Any ideas. Can it be done?
Thanks,
Jeff Black