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!

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?

I'm not able to upload the screenshot (word doc) here. What format did you use?

Do a search in google for 'compile error query microsoft access'

What it points to is that the Access database in your computer might not have some specific component/modules requered for the query.

Either try to reinstall access or use it on another computer.

I will try that. Thank you!

Batuwa,
I used the following query and it worked:

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;


But I also need to have the last column (total column) that should add Appleslice, OrangeSlice, BananaSlice and give the total numbers of all fruits. How do I add the statement to add the running total of those fruit slices and add them all together?

I appreciate your help and

Happy Bijaya Dashami!

Nepalover,
Sorry for not seeing this thread. Do you still need help with this? I guess you have to use SUM function within the SQL, and try to get running total, but I have to see how it exactly works.

Happy dashain:)

how about

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

Bhusan bro and Batuwa bro,

I've one more question in regards to this. If I want to add everything else other than the one mentioned, how do I do that. For e.g. If it is not apple, orange, banana, strawberry then I want to show them on the last column as miscellaneous.

I tried the following statement in addition to the SQL statements that I'm using:

IIf([Off Code]<> 'A1' or 'A2' or 'R1' or 'R2' or 'R3' or 'SP' or 'AN' or 'I1' or 'V1',[TransactionsCompleted]) AS Miscellaneous
FROM TBLSysTransTriageTG

However, the miscellaneous column is giving me the total from those fields. Basically, I need to know how to write a "not equal to" statement.

Thank you guys a lot for your help.

batuwa

I want copy the access page can you show how did you copy the access page