Storing answers to expressions...

  • Thread starter Thread starter Jason Sizemore
  • Start date Start date
J

Jason Sizemore

Can I store the values calculated in a query back into
another table?
How can I calculate the difference between two values
from the same field but drawn from two different dates,
for example:
Date Value Difference
08/27/2003 1000
08/28/2003 1200 200

I would like to calculate this in a query and then store
it in a table. But I need to know how to reference the
value (ie 1000) from a previous date.

Thanks
 
In the SQL below, your Field Date is renamed DiffDt. Assuming the table is
called tblDiff, and that all dates are unique...
Here's one possibility: Define this SQL in a query as qDiff
SELECT tblDiff.DiffDt, tblDiff.Difference, tblDiff.Value, D.DiffDt, D.Value
AS Value1
FROM tblDiff, tblDiff AS D
WHERE (((D.DiffDt)=DMax("[DiffDt]","tblDiff","[DiffDt]<#" &
[tblDiff].[DiffDt] & "#")));
then use
UPDATE tblDiff INNER JOIN qDiff ON tblDiff.DiffDt = qDiff.tblDiff.DiffDt
SET tblDiff.Difference = [tblDiff].[Value]-[qDiff].[Value1];
 
Back
Top