[VIEWED 14394
TIMES]
|
SAVE! for ease of future access.
|
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-14-06 2:05
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!
|
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-15-06 11:01
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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....
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-15-06 11:15
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-15-06 11:38
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Getting Compile error while using the above SQL statement.
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-15-06 11:45
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Make sure the field names match ie. fruit and N
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-15-06 11:48
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Also make sure that your field N has datatype of 'number'
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-15-06 2:39
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-18-06 10:51
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-18-06 11:01
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
there is an extra comma after Process ='SPRD750',TransactionsCompleted*5, try to remove that last comma
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-18-06 11:05
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Sorry, that was a typo... I ran it after getting rid of that comma... still getting the compile error. Thanks
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-18-06 11:39
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
What are the datatypes of Process and TransactionsCompleted fields?
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-18-06 12:53
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Nepal lover, my guess is your data type must be screwed up! Here is what you should do:
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-18-06 12:54
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Original table
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-18-06 12:55
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Query
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-18-06 12:55
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Output
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-18-06 12:56
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Sorry for not resizing the big images.
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-18-06 1:37
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-18-06 1:43
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-18-06 1:47
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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)?
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-18-06 1:49
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-18-06 1:51
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
can u post the screen shots of your orignal table and SQL?
|
|