Query Using Calculated Fields

[psulions83]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2%[/TD]
[TD]= Cell B / 100[/TD]
[TD]= Cell A * (1 - Cell C)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[stumac]

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:

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

Where you have Fields A & B in the table Mytable.

 

[Marbles]

Are you just trying to get a percentage of the first column. eg. 2% of a hundred.

 

[xenou]

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

 

[psulions83]

Thanks all.

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