Solved

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

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Attachment field in SQL 3 29
MS Access 2010 Close Form  Event - Stop Form Closing 4 28
access query to sql server 3 22
Progress bar in access form 11 25
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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