syntax errors when tryng to use results of a sum query in second calcualtion

[jonken]

Looking for some assistance as an access beginner - I have set up a query which sums the results of a calculation in a table. In the same query I then want to apply a second calaculation to those sum results. This is a risk management database - where I have table which calculates the impacts of actions taken to mitigate a risk - the sum query adds up those risk mitgation results and produces those in the query perfectly - the output is a value between 0 and 1 to two decimal places. Also in the query I capture the orginal risk score prior to mitgation from another linke tabe. The third step I want to do is basically calcualte a revised risk score once the rawrisk score is discounted by the impact of the risk mitigtions - the formula should be [rawriskscore]*(1-[sumofmitgationresults]) - however i keep getting a synta error or asked for parameter values whenever i run the query with this calculation column added. Can anyone advise why this happening as the data is there and the formula looks sound, and I am using querybuilder to build the forumla so not getting any of the field names wrong.



thanks for any assistace

jon

[ranman256]

you can get errors if any value is NULL.

[knarfreppep]

Have you thought about doing one thing at a time? e.g. -

MyQuery
MyQuery01
MyQuery02

where MyQuery01 does the first step, MyQuery02 works on MyQuery01's results, and MyQuery works on MyQuery02's results to render the final result.

[knarfreppep]

Any you can often speed things up with something a lot more complicated than this simple example -

MyQuery
MyQuery01
MyQuery02
MyTable

where MyQuery01 does the first step, MyQuery02 works on MyQuery01's results and 'makes' MyTable, and MyQuery works on MyTable to render the final result.

[jonken]

Thanks I broke it down into steps like you suggested and the whole calaculation is now working perfectly.
Thanks for taking the time, I appreciate it.

Any you can often speed things up with something a lot more complicated than this simple example -

MyQuery
MyQuery01
MyQuery02
MyTable

where MyQuery01 does the first step, MyQuery02 works on MyQuery01's results and 'makes' MyTable, and MyQuery works on MyTable to render the final result.