Guys,
I have a little complicated access question, I would appreciate if any of you can help me. Here is the scenario:
I have a query that is pulling different boxes from the table, let say the boxes are A, B, C, D and E. In the query, I need to perform some calculation, however, those calculations are not the same for all the boxes. For box A - the calculation should be A/2, for B - calculation should be B*2, for C - C*25 etc. Is this even possible in access?

Thanks in advance for your help!

ok what you have to do is do an APPEND qyery. but thatwill change the value in your originating table as well.

or if you want to have one more query. Create a form that pulls table from the table and do calculation on the form and save the result on a new query so that will have the result.

Note: Do the calculation on the form text box not on the query.

Pink Floyd,
Thank you for your prompt response. However, I'm still not sure how do I perform the calculation in the form. I'm new to access and have been given this project. I can not change the original numbers in the table so I guess I can't do the append query. i would like to do it using the form if thats possible. Again, in the table I have fields called A, B, C, D, E etc. I need to manipulate the number of As, Bs, Cs ..... in a different ways. For e.g:
Table:
Field Name Number
A 7
B 25
C 15
D 99
E 105

The field names are not always constant, sometimes I may not have one of them in the table.

Now, I need to calculate the As by dividing it by 2 , so the calulated field must have A/2
multiply B by 2, multiply C by 5 etc.

Thanks again for your help.

select a / 2 , b * 2 , c * 25
from table

Thanks Icanfly. But I need to divide or multiply the number field for the A or B or C.. not the name A, B or C itself.

Pink Floyd, Icanfly or anyone... does anyone have a solve for this?

SELECT ROWNAME
FROM TABLENAME
WHERE ..(YOUR A B OR C VALUES ARE)
DIVIDE?

i forgot how to divide in SQL.

My table looks like following
Fruits N

Apple 5
Orange 6
Mangoes 20
Grapes 100

(Let say, we are cutting apples in 2 pieces, orange in 4 pieces, Mangoes in 3 pieces and divide grapes by 10)
I think I need to write a sql formula to do the following....

If this field is apple then N*2 (It should calculate 5*2)
If this field is Orange then N*4
If this field is Mangoes then N*3
If this field is grape then N/10
If this field is Banana then N*2
If the field is watemelon then N*10

Thanks for your help!

If apple, orange, mangoes and grapes are the column names then you can use the following query:

select apple*2 as getapple, orange*4 as getorange, mangoes*3 as getmangoes, grapes/10 as getgrapes, banada*2 as getbananas, watermelon*10 as getwatermelon

If apple, orange, mangoes and grapes are the column names then you can use the following query:

select apple*2 as getapple, orange*4 as getorange, mangoes*3 as getmangoes, grapes/10 as getgrapes, banada*2 as getbananas, watermelon*10 as getwatermelon
from TABLENAME

Nepalover,
I am not ACCESS guru... but I think you got the solution, but you still don't know how to it exactly in ACCESS. Do you have the concept of "variable"? Programming 101 concept! When you "name" a field in ACCESS table, that "name" becomes variable throughout the program (SQL). That means you can subtract, multiply or do any arithmetic operation with these names as long as it's a number. For example, when you name a filed "Banana", you have values stored in it.. you can do multiplication or subtraction..

Apple Orange Banana
40 30 25
45 80 10


SELECT Apple/3 as App, Orange*3 as Org, Mango/4, Banana+3 as Ban
FROM Table1;

when you execute this SQL, you will get
App Org Ban
13.33 90 28
15 240 13

It doesn't change your original table.

I hope this will help you. Everybody in here is right. Good luck!

Ok, I was not understanding your query...

here is what you need to do..

SELECT Fruit, Num, IIf([Fruit]='Apple',Num*3) AS AppleSlice, IIf([Fruit]='Orange',Num/2) AS OrangeSlice, IIf([Fruit]='Banana',Num+3) AS BananaSlice
FROM Table1;

Good luck!

Original table:
FRUIT NUM
------- -------
Apple 50
Orange 25
Banana 20

Batuwa,
Thanks for the response. However, I may not be explaining it correctly. The column names are not Apple, Orange and Banana. The column is fruits and it might have Apple, Orange, Banana, Grapes etc in hte fields. It may have one, two or many fruits in the table for that day. Then, it need to do calculations accordingly.

For 09/13/06
Fruits Fruits Qnty

Apple 5
Orange 8
Banana 10

So, my new calculated field should show
Apple 10 (5*2)
Orange 32 (8*4)

For 09/14/06, my table looks like:

Apple 18
Mango 20
Grape 100
Watemelon 25

so, my new calculated fields should show

Apple 36 (18*2)
Mango 60 (20*3)
Grape 10 (100/10)
Watermelon 2.5 (25/10)

Thanks!

Thanks Batuwa, I think this will work. I will be popping out more questions, if I run into any issues. Again, thanks for your help!

You can also use CASE WHEN Statements


eg.
select fruits, N,
CASE WHEN fruits='apple' THEN N*2
WHEN fruit='orange' THEN N/2
END as calculatedcolumn
from yourtable


you can add multiple When statements to include all other cases

Do I use this formula in the Query, in criteria field?

I hope you're in "Query design view". Right click on the blank, and then go to "SQL View". a small window will appear like a note pad. Type the whole SQL there, save it, and run it. Make sure your field names are correct.

Case When would be slightly better option since when you use the IIF statements it creates a new column for each fruit, whereas Case when would give you just one column called calculatedcolumn or you can name it anyway you want.

You should put that in the SQL view

It worked.... thanks a bunch guys!