MS Access Query to return sum of two fields multiplied by third field

Hello,

I created the following query that returns a subset of data.  I'm having troubles extending it one step further.  The query should return a result of a calculation a single value.

 = (NumberOfDrivers + NumberClaims) X Result

SELECT LocationID, CarType, NumberDrivers, NumberClaims, Result FROM mydatabase WHERE CarType="Sedan";

Open in new window


Thanks
AndyC1000Asked:
Who is Participating?
 
Pratima PharandeCommented:
try this

SELECT LocationID, CarType, NumberDrivers, NumberClaims, Result ,
((NumberOfDrivers + NumberClaims) * Result ) as new_result
 FROM mydatabase WHERE CarType="Sedan";
0
 
AndyC1000Author Commented:
Thanks for your reply.

The calculation returns a value although I realised the where clause will need to be changed to produce the correct value I need.  The value being returned is 113.174633402367 instead of 81.6263502 for the first location ID 3146701.

I would like the query to query all location ids, where car type equals sedan returning the calculation in a new field.

The attached excel sheet will hopefully help explain the scenario.  The bottom table is the output of the query required.

Thanks.
0
 
AndyC1000Author Commented:
Note I updated the query with the correct field names NumberDrivers1 and NumberClaims0.
Example-Table.xlsx
0
 
Lukasz ChmielewskiCommented:
Your calculation is based not on
 = (NumberOfDrivers + NumberClaims) X Result
but rather
 = NumberOfDrivers + (NumberClaims X Result)

altering prtima's code:
SELECT LocationID, CarType, NumberDrivers, NumberClaims, Result ,
(NumberOfDrivers + NumberClaims  * Result ) as new_result
 FROM mydatabase WHERE CarType="Sedan";

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.