Storing answers to expressions...

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
 
J

John Verhagen

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top