[VIEWED 14390
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!
|
|
|
|
Pink_floyd
Please log in to subscribe to Pink_floyd's postings.
Posted on 09-14-06 2:25
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-14-06 2:43
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
Icanfly
Please log in to subscribe to Icanfly's postings.
Posted on 09-14-06 2:53
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
select a / 2 , b * 2 , c * 25 from table
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-14-06 3:41
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-14-06 4:15
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Pink Floyd, Icanfly or anyone... does anyone have a solve for this?
|
|
|
redstone
Please log in to subscribe to redstone's postings.
Posted on 09-14-06 5:16
PM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
SELECT ROWNAME FROM TABLENAME WHERE ..(YOUR A B OR C VALUES ARE) DIVIDE? i forgot how to divide in SQL.
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-15-06 8:35
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-15-06 8:41
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-15-06 8:41
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-15-06 9:08
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-15-06 9:57
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-15-06 9:58
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Original table: FRUIT NUM ------- ------- Apple 50 Orange 25 Banana 20
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-15-06 10:08
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-15-06 10:10
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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!
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-15-06 10:14
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-15-06 10:24
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
Do I use this formula in the Query, in criteria field?
|
|
|
batuwa
Please log in to subscribe to batuwa's postings.
Posted on 09-15-06 10:32
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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.
|
|
|
bhusan
Please log in to subscribe to bhusan's postings.
Posted on 09-15-06 10:47
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
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
|
|
|
Nepalover
Please log in to subscribe to Nepalover's postings.
Posted on 09-15-06 10:49
AM
Reply
[Subscribe]
|
Login in to Rate this Post:
0
?
|
|
It worked.... thanks a bunch guys!
|
|