Look for value in previous record and subtract

[Guest]

I need help with a query that will look up the value of the previous record
of the overall revision but with the same quote number. I would also like to
subtract the previous sell amount from the current amount. Can anyone help
me? Here is the sql of the query, your help is greatly appreciated.

SELECT tblQuotationDollars.[Quote Number], tblQuotationDollars.[Date Sent],
tblQuotationDollars.Sell, tblQuotationDollars.[Overall Revision],
DLookUp("[Sell]","tblQuotationDollars","[Overall Revision]=" & [Overall
Revision]-1) AS Expr1
FROM tblQuotationDollars
WHERE (((tblQuotationDollars.[Date Sent])>=#6/1/2007# And
(tblQuotationDollars.[Date Sent])<=#6/30/2007#));

 

[Allen Browne]

Use a subquery to get the value from the previous row.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need help with a query that will look up the value of the previous record
of the overall revision but with the same quote number. I would also like
to
subtract the previous sell amount from the current amount. Can anyone
help
me? Here is the sql of the query, your help is greatly appreciated.

SELECT tblQuotationDollars.[Quote Number], tblQuotationDollars.[Date
Sent],
tblQuotationDollars.Sell, tblQuotationDollars.[Overall Revision],
DLookUp("[Sell]","tblQuotationDollars","[Overall Revision]=" & [Overall
Revision]-1) AS Expr1
FROM tblQuotationDollars
WHERE (((tblQuotationDollars.[Date Sent])>=#6/1/2007# And
(tblQuotationDollars.[Date Sent])<=#6/30/2007#));

 

[Guest]

Allen,
This worked great! Thank you.
Now I am able to find the difference between the current and previous sell,
but any record that did not have a previous record will show a blank field
under the previous sell. How can I make it default to a 0?

Use a subquery to get the value from the previous row.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need help with a query that will look up the value of the previous record
of the overall revision but with the same quote number. I would also like
to
subtract the previous sell amount from the current amount. Can anyone
help
me? Here is the sql of the query, your help is greatly appreciated.

SELECT tblQuotationDollars.[Quote Number], tblQuotationDollars.[Date
Sent],
tblQuotationDollars.Sell, tblQuotationDollars.[Overall Revision],
DLookUp("[Sell]","tblQuotationDollars","[Overall Revision]=" & [Overall
Revision]-1) AS Expr1
FROM tblQuotationDollars
WHERE (((tblQuotationDollars.[Date Sent])>=#6/1/2007# And
(tblQuotationDollars.[Date Sent])<=#6/30/2007#));

 

[Guest]

Allen,
Thank you very much, I got it working!

Allen,
This worked great! Thank you.
Now I am able to find the difference between the current and previous sell,
but any record that did not have a previous record will show a blank field
under the previous sell. How can I make it default to a 0?

Use a subquery to get the value from the previous row.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need help with a query that will look up the value of the previous record
of the overall revision but with the same quote number. I would also like
to
subtract the previous sell amount from the current amount. Can anyone
help
me? Here is the sql of the query, your help is greatly appreciated.

SELECT tblQuotationDollars.[Quote Number], tblQuotationDollars.[Date
Sent],
tblQuotationDollars.Sell, tblQuotationDollars.[Overall Revision],
DLookUp("[Sell]","tblQuotationDollars","[Overall Revision]=" & [Overall
Revision]-1) AS Expr1
FROM tblQuotationDollars
WHERE (((tblQuotationDollars.[Date Sent])>=#6/1/2007# And
(tblQuotationDollars.[Date Sent])<=#6/30/2007#));