Query result to table

[CoachBarkerOJPW]

I have a query based on two fields in a table, AmountInStock:[AmountBought] -
[AmountSold]. How do I return this value to the table, tblProducts. Do I need
a field in the table AmountInStock and assign it the value?

This is the query:
SELECT tblProducts.ProductTypeID, tblProducts.ProductType,
tblProducts.ProductName, tblProducts.PurchasePrice, tblProducts.SalePrice,
tblProducts.Size, tblProducts.AmountBought, tblProducts.AmountSold,
[AmountBought]-[AmountSold] AS AmountInStock
FROM tblProducts;

 

[Jeff Boyce]

It is rarely necessary to store a calculated value. Why not just use your
query to return it?

But yes, if you feel you must store it in a table, and you are willing to do
the extra coding needed to ensure that your three values ([AmountBought],
[AmountSold], [AmountInStock]) are always kept in synch, then you will need
a place to store it (a new field), and you will need to use something like
an Update query to set the value.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[David Cox]

In the real world Amount in stock is what can actually be physically found.
With all of the best intentions it often varies from what should be there
from the calculations. I would have amount in stock as a table field. If you
were going to update from a calculation as in that query I would want to
have the calculation as :
[AmountInStock] = [AmountInStock] + [AmountBought]-[AmountSold]
which would involve changing your query.

 

[CoachBarkerOJPW]

So if I have the field AmountInStock in my table already, and include that
field in the query, where do I put the calculation part of the query? Can
this be done in a select query or does it have to be an update query?

In the real world Amount in stock is what can actually be physically found.
With all of the best intentions it often varies from what should be there
from the calculations. I would have amount in stock as a table field. If you
were going to update from a calculation as in that query I would want to
have the calculation as :
[AmountInStock] = [AmountInStock] + [AmountBought]-[AmountSold]
which would involve changing your query.

I have a query based on two fields in a table,
AmountInStock:[AmountBought] -
[AmountSold]. How do I return this value to the table, tblProducts. Do I
need
a field in the table AmountInStock and assign it the value?

This is the query:
SELECT tblProducts.ProductTypeID, tblProducts.ProductType,
tblProducts.ProductName, tblProducts.PurchasePrice, tblProducts.SalePrice,
tblProducts.Size, tblProducts.AmountBought, tblProducts.AmountSold,
[AmountBought]-[AmountSold] AS AmountInStock
FROM tblProducts;

 

[David Cox]

If you want to update the amount in stock, which seems natural, you use an
update query with the expression [AmountInStock] +
[AmountBought]-[AmountSold] in the "update to:" row

So if I have the field AmountInStock in my table already, and include that
field in the query, where do I put the calculation part of the query? Can
this be done in a select query or does it have to be an update query?

In the real world Amount in stock is what can actually be physically
found.
With all of the best intentions it often varies from what should be there
from the calculations. I would have amount in stock as a table field. If
you
were going to update from a calculation as in that query I would want to
have the calculation as :
[AmountInStock] = [AmountInStock] + [AmountBought]-[AmountSold]
which would involve changing your query.

I have a query based on two fields in a table,
AmountInStock:[AmountBought] -
[AmountSold]. How do I return this value to the table, tblProducts. Do
I
need
a field in the table AmountInStock and assign it the value?

This is the query:
SELECT tblProducts.ProductTypeID, tblProducts.ProductType,
tblProducts.ProductName, tblProducts.PurchasePrice,
tblProducts.SalePrice,
tblProducts.Size, tblProducts.AmountBought, tblProducts.AmountSold,
[AmountBought]-[AmountSold] AS AmountInStock
FROM tblProducts;