Query Using Calculated Fields

psulions83

Board Regular
Joined
Nov 16, 2015
Messages
126
Hi,

I have a query that I am building where I have a calculated field (within the query) that I want to use to calculate a final value in another column. I cannot get this to work using to columns first would be the first calculation then the second would be the simple math equation. See below for example.

ABCD
1002%= Cell B / 100= Cell A * (1 - Cell C)

<tbody>
</tbody>

Hopefully the above makes sense. Do I have to perform an update query with cell C first then run another update query with cell D?

Right now I have tried to change data type from Short Text to Number. I continuously return no results for column D
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You seem to be referring to your table in Spreadsheet terms. A column would be referred to as a field and wouldn't typically be named Cell x. What you are asking to do should be doable:

Code:
SELECT A, B, [B]/100 AS C, [A]*(1-[C]) AS D
FROM MyTable

Where you have Fields A & B in the table Mytable.
 
Upvote 0
Are you just trying to get a percentage of the first column. eg. 2% of a hundred.
 
Upvote 0
Hi, Post the query you have tried (even if it isn't working). That should be a good place to start and we can also make sure of the syntax. A few rows of sample data would also be helpful. Note that you should be sure that the fields are double data types (decimals or floats) because integer division will probably give you results you don't want. Also you don't need to use a calculated field either: SELECT A, B, (/100) as C, [A]*(1-(/100)) AS D FROM MyTable
 
Last edited:
Upvote 0
Thanks all.

I was trying to get this done in Design View just totally ignoring using SQL. I got it to work with SQL.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top