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!

One more question, when I use the SQL query:

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;

The calculated column are different where the results are stored, if I try to use the following query, it tells me the duplicate field.

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

I need to put all of the calculated numbers in one column, it it possible using the CASE WHEN query? I tried it but I'm getting the syntax errors....

Nepalover,

The CASE WHEN seems to be for SQL server, for Access use the Switch statement like following:

SELECT fruit, N,
Switch(
fruit='apple',N*2,
fruit='orange',N/3
) AS calculatedcolumn
FROM yourtable;

you can use multiple fruit = 'xxx', and calculations on the above statement to get your result

Getting Compile error while using the above SQL statement.

Make sure the field names match ie. fruit and N

Also make sure that your field N has datatype of 'number'

Nepalover,
I hope you solved your problem. Bhusan gave you good suggestions. Yes, use switch statement instead of if/then/else to produce a single column result. When you get syntax error, check for any missing quotes as well.

Here is the SQL Switch statement I'm using:

SELECT Process, TransactionsCompleted,
Switch(
Process ='SPRD100',TransactionsCompleted*2,
Process ='SPRD500',TransactionsCompleted*3,
Process ='SPRD750',TransactionsCompleted*5,
) AS calculatedcolumn
FROM TBLSystemTransactions;

(Process=Fruit, TransactionsCompleted = N)

However, I'm still getting the compile error. Can you check the statement and see any errors?

Thanks

there is an extra comma after

Process ='SPRD750',TransactionsCompleted*5,

try to remove that last comma

Sorry, that was a typo... I ran it after getting rid of that comma... still getting the compile error.

Thanks

What are the datatypes of Process and TransactionsCompleted fields?

Nepal lover, my guess is your data type must be screwed up! Here is what you should do:

Original table

Query

Output

Sorry for not resizing the big images.

Batuwa,
That is exactly what I have. I have Transactionscompleted as a numeric (number) field. I have the SQL typed in exactly as you have.

Either your install of Access is faulty, try to reinstall access , or perhaps your computer doesn't have enuff juice for the query, try it on a different computer.

One more thing, I have 4 fields in the table:

UserID Process TransactionsCompleted Date

Would that be a problem that I'm not including the 2 other column names in the SQL (UserID and Date)?

First of all, check your code again. Carefully check for any missing or unnecessary commas or quotes. Sometimes you need second set of eyes. Try in some other computers than yours if you're in lab.

can u post the screen shots of your orignal table and SQL?