Reference previous record

[Ivor Williams]

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.

Ivor

 

[Allen Browne]

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Ivor Williams]

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]

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.

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

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Ivor Williams]

Many thanks, Allen. As usual, you've ben very helpful. Your solution works
perfectly.

Ivor

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.

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

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.

 

[Ivor Williams]

Works great! Thanks for all the help.

Ivor

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.

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

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.