Query result to table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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;
 
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/
 
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.
 
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?

David Cox said:
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 said:
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;
Click to expand...
Click to expand...
 
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

CoachBarkerOJPW said:
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?

David Cox said:
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 said:
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;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top