Okay, it sounds like you are storing the progressive total in your table?
If so, the table needs to be redesigned. Otherwise you can kiss the
integrity of your data goodbye. The whole thing about storing dependent
values will destroy the validity of your data.
I take it you have another table of projects, and tblProgress tracks the
payments associated with a project (based on the ProjNo foriegn key
field.) One of the fields stores the value for the current progress
payment: I've used Amount in the example below. I also used ProgDate for
the date field, as DATE is a reserved word and can cause you problems. (In
some contexts Access mistakes it for today's date.)
Now you want a query that shows the prior amount paid for this project, so
you can add on the amount of this entry and get a progressive total. The
subquery do to that would look like this:
SELECT tblProgress.ProgID
tblProgress.ProjNo,
tblProgress.ProgDate,
tblProgress.Amount
(SELECT Sum(Amount) AS SumOfAmount
FROM tblProgress AS Dupe
WHERE Dupe.ProjNo = tblProgress.ProjNo
AND Dupe.ProgDate < tblProgress.ProgDate) AS PriorAmount
FROM tblProgress;
For this to work reliably, you would need a unique index on the
combination of ProjNo + ProgDate.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Ivor Williams said:
I've been to the website and tried to no avail to get it to work, I can't
seem to get the syntax right. I've attached the SQL for the query I'm
working with. What I want to do is manually enter values in the fields
MatToDate and MatOnOrder, have the query calculate the value for the
field Mat, then subtract the previous value in the Mat field. If you'd be
so kind as to show me the proper way to write this, I'm sure your
explanation will become perfectly clear.
SELECT tblProgress.ProgID, tblProgress.ProjNo, tblProgress.Date,
tblProgress.MatToDate, tblProgress.MatOnOrder, [MatToDate]+[MatOnOrder]
AS Mat, tblProgress.LabToDate, tblProgress.LabToMonthEnd,
[LabToDate]+[LabToMonthEnd] AS Lab
FROM tblProgress;
Many thanks,
Ivor
Allen Browne said:
See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord
I have a query in which there is a date field and a field for a dollar
amount. I want to have a calculated field in which the dollar amount for
the date prior to the most recent date is subtracted from the dollar
amount for the most recent date. For example, if the amounts on the two
most recent dates are 10 dollars on Feb 20 and 7 dollars on Feb 15, the
calculated amount would be 3 dollars. Can someone help me with the
syntax for this, please.