Solved

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

Posted on 2013-01-14
4
427 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
  • 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now