Solved

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

Posted on 2013-01-14
4
432 Views
Last Modified: 2013-01-16
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
0
Comment
Question by:AndyC1000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 125 total points
ID: 38777140
try this

SELECT LocationID, CarType, NumberDrivers, NumberClaims, Result ,
((NumberOfDrivers + NumberClaims) * Result ) as new_result
 FROM mydatabase WHERE CarType="Sedan";
0
 

Author Comment

by:AndyC1000
ID: 38781347
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
 

Author Comment

by:AndyC1000
ID: 38781354
Note I updated the query with the correct field names NumberDrivers1 and NumberClaims0.
Example-Table.xlsx
0
 
LVL 27

Assisted Solution

by:Lukasz Chmielewski
Lukasz Chmielewski earned 125 total points
ID: 38782059
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question