Access 2007 calculation help

[Ian Way]

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.

 

[NG]

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]

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.

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

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.

 

[John W. Vinson]

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.

 

[Ian Way]

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

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.

 

[Jeanette Cunningham]

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

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

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 Spencer]

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

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

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

On Wed, 20 Jan 2010 10:44:01 -0800, Ian Way
<[email protected]>
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.