If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2007 calculation help
i have 2 fields in a table, 'qty on order' and 'qty rxd', In a query I
subtract 'qty rxd' from 'qty on order' how can i store the result of this calculation, so the next time the calculation is carried out it subtracts the 'qty rxd' entered by the user from the result of the previous calculation. The result is also to be shown on a form. |
#2
|
|||
|
|||
Access 2007 calculation help
Hi,
you can use an update query to put the calculated result in the field [qty on order], but I think you'd better consider to create a history table, related 1 to many to the table with the 2 qty fields. Then you can make a totals query to give you the total amount [qty rxd] and substract this from the [qty on order]. This will give you a more correct view on the case. By the way: I never use spaces in a field name, saves me a lot of type work. succes NG "Ian Way" wrote: i have 2 fields in a table, 'qty on order' and 'qty rxd', In a query I subtract 'qty rxd' from 'qty on order' how can i store the result of this calculation, so the next time the calculation is carried out it subtracts the 'qty rxd' entered by the user from the result of the previous calculation. The result is also to be shown on a form. |
#3
|
|||
|
|||
Access 2007 calculation help
Many thanks for your assistance 'NG' it is greatly appreciated
however i still think i am doing something wrong, when i try to create an update query i always get error messages questioning my spelling. is it possible that you could post an example of what i should be doing. Ian Way ps. spaces only for ease of reading post. "NG" wrote: Hi, you can use an update query to put the calculated result in the field [qty on order], but I think you'd better consider to create a history table, related 1 to many to the table with the 2 qty fields. Then you can make a totals query to give you the total amount [qty rxd] and substract this from the [qty on order]. This will give you a more correct view on the case. By the way: I never use spaces in a field name, saves me a lot of type work. succes NG "Ian Way" wrote: i have 2 fields in a table, 'qty on order' and 'qty rxd', In a query I subtract 'qty rxd' from 'qty on order' how can i store the result of this calculation, so the next time the calculation is carried out it subtracts the 'qty rxd' entered by the user from the result of the previous calculation. The result is also to be shown on a form. |
#4
|
|||
|
|||
Access 2007 calculation help
On Wed, 20 Jan 2010 10:44:01 -0800, Ian Way
wrote: however i still think i am doing something wrong, when i try to create an update query i always get error messages questioning my spelling. is it possible that you could post an example of what i should be doing. Please open the query in SQL view (View... SQL from the menu) and post the entire SQL text here. Also indicate the exact text of the error message. Do note that if you have blanks or any special characters (parentheses, hyphens, punctuation, anything but underscore basically) in any field or table name you must enclose the name in [square brackets]; it never hurts to do so, and it may just be a good habit to get into. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Access 2007 calculation help
ty john for your patience,
however while it shows the error i am unable to get SQL view. This problem seems easy on paper, as all i want to do is to subtract two numbers, and use the result as part of a repeatable calculation. eg, [Qty_on_Order]-[Qty Rxd]=[Qty_on_Order] I realise this is not a correct way of doing it in access, however i am lost and can not find a way of doing this, so any assistance would be greatly appreciated. Ian Way "John W. Vinson" wrote: On Wed, 20 Jan 2010 10:44:01 -0800, Ian Way wrote: however i still think i am doing something wrong, when i try to create an update query i always get error messages questioning my spelling. is it possible that you could post an example of what i should be doing. Please open the query in SQL view (View... SQL from the menu) and post the entire SQL text here. Also indicate the exact text of the error message. Do note that if you have blanks or any special characters (parentheses, hyphens, punctuation, anything but underscore basically) in any field or table name you must enclose the name in [square brackets]; it never hurts to do so, and it may just be a good habit to get into. -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
Access 2007 calculation help
In your query in design view, remove the column/s that is/are causing the
error, until you can switch to sql view, then do as John suggests. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Ian Way" wrote in message ... ty john for your patience, however while it shows the error i am unable to get SQL view. This problem seems easy on paper, as all i want to do is to subtract two numbers, and use the result as part of a repeatable calculation. eg, [Qty_on_Order]-[Qty Rxd]=[Qty_on_Order] I realise this is not a correct way of doing it in access, however i am lost and can not find a way of doing this, so any assistance would be greatly appreciated. Ian Way "John W. Vinson" wrote: On Wed, 20 Jan 2010 10:44:01 -0800, Ian Way wrote: however i still think i am doing something wrong, when i try to create an update query i always get error messages questioning my spelling. is it possible that you could post an example of what i should be doing. Please open the query in SQL view (View... SQL from the menu) and post the entire SQL text here. Also indicate the exact text of the error message. Do note that if you have blanks or any special characters (parentheses, hyphens, punctuation, anything but underscore basically) in any field or table name you must enclose the name in [square brackets]; it never hurts to do so, and it may just be a good habit to get into. -- John W. Vinson [MVP] . |
#7
|
|||
|
|||
Access 2007 calculation help
Another way to get the SQL is to use the VBA immediate window and enter
?Currentdb().QueryDefs("NameOfTheQuery").SQL And then copy and paste the result. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Jeanette Cunningham wrote: In your query in design view, remove the column/s that is/are causing the error, until you can switch to sql view, then do as John suggests. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Ian Way" wrote in message ... ty john for your patience, however while it shows the error i am unable to get SQL view. This problem seems easy on paper, as all i want to do is to subtract two numbers, and use the result as part of a repeatable calculation. eg, [Qty_on_Order]-[Qty Rxd]=[Qty_on_Order] I realise this is not a correct way of doing it in access, however i am lost and can not find a way of doing this, so any assistance would be greatly appreciated. Ian Way "John W. Vinson" wrote: On Wed, 20 Jan 2010 10:44:01 -0800, Ian Way wrote: however i still think i am doing something wrong, when i try to create an update query i always get error messages questioning my spelling. is it possible that you could post an example of what i should be doing. Please open the query in SQL view (View... SQL from the menu) and post the entire SQL text here. Also indicate the exact text of the error message. Do note that if you have blanks or any special characters (parentheses, hyphens, punctuation, anything but underscore basically) in any field or table name you must enclose the name in [square brackets]; it never hurts to do so, and it may just be a good habit to get into. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|