Calculating Average Weight - Broken SUM

[redekopp]

I have a database that creates a Total Loss Percent (columnT) on counts for adjusting that needs to be a weighted average based on these rules:



if columnT is <5 then = 0%
if columnT is >=90 then = 100%

If either of those scenarios is true then they should not be part of the Weighted Average as well.

Right now I have 3 created fields in the query:

Weight: ([acresCounted]/[Forms]![frmCanolaCounts]![ActualAcres])
SW: (IIf([columnT]<5 Or [columnt]>=90,0,[columnt])*[Weight])

POLAcres: (IIf([columnT]<5 Or [columnt]>=90,0,[acresCounted])) - This is used to determine the number of acres to use to create the Weight where any count that has a Toal loss as per the rules is NOT included.

On the form, in the detail, I have 2 created fields that are CRUCIAL to the formula to work. Everything works great but its just the last problem I can't figure out:

Weight2 - Control Source: =([acresCounted]/(Sum([POLAcres]))) - Used to create the proper weight based on the rules
SW1 - Control Source: =(IIf([columnT]<5 Or [columnt]>=90,0,[columnt])*[Weight2]) - used to create the proper Score x Weight value.

Then in the form header, i have the Weighted Average field to determine the overall weight.

AvgWeight - Control Source: =Sum([SW1])

All in all the key to it working is being able to Sum([SW1]) But it breaks everything when trying to do that. Anyone have any ideas how I can sum ([SW1])???

Thanks for the help, sorry if it comes off a bit confusing.

[jwhite]

You say it breaks everything when trying to Sum([SW1]). How so is it breaking? Error codes? #Error? What?

[redekopp]

yeah, between sw1, weight2, and avgweight all get #Error etc.

[jwhite]

I think we would need a demo of your database where we can replicate the issue and walk the code with sample data.

[redekopp]

I think we would need a demo of your database where we can replicate the issue and walk the code with sample data.

Thats what happens when i sum SW1
When i sum SW there is no issues, my guess is because SW is created in the query, and SW1 is just a field on the detail.

[andy49]

Could it be that....


You can only use sum([sw1]) if sw1 is a field.

From your description it is unclear whether sw is a field in your query and whether sw1 is a calculated text box.


Sent from my iPad using Tapatalk

[redekopp]

sw1 is just a text box with a control source that is: =(IIf([columnT]<5 Or [columnt]>=90,0,[columnt])*[Weight2])

sw is a field i created in the query.

I am feeling as though you just arent allowed to sum the text box if it isnt a field, any workaround?

[andy49]

Could you use the query to create a field which has the values you need to add up?


Sent from my iPad using Tapatalk

[redekopp]

Tried that, but apparently if you SUM in a query you also break everything, it appears it isnt allowed either. Hence why Weight2 had to be an unbound field as i couldn't SUM POL Acres in the query. This is turning into a seriously testing task.

[andy49]

How about still using the weight2: in a query but using Dsum function.

Then you can use the sw1 in your query too. These can then be summed.


Sent from my iPhone using Tapatalk

[redekopp]

Maybe I dont understand DSum, but POLAcres is being created in the query and a DSum is asking for a field in a table?

[andy49]

So long as the query has a name like qryname then

you should be able to use

weight:([acresCounted]/(dsum("[POLAcres]","qryname"))

in the query

[redekopp]

I made the query qryAvgWeight, when I run the query it says it cannot find the name 'qryAvgWeight'

[andy49]

Ok. [emoji848] do you have a version we can look at


Sent from my iPhone using Tapatalk