Get value from previous record

[Elaine16025]

Hi everyone:

I appreciate if someone can help me to figure out a way to solve this
problem, it has bothered me for quite a few days now...

I have four fields in "MyTable":RecID, CustID,InDate and Comments that
document each record ID, customer ID, the date they checked in to the
motel and the comment they had regarding their stay. The comment is
either "positive" or "negative". What I want to do is to create a
report for each customer, list their check-in date and comment, and
calculate the days from last check-in till this check-in, and I want to
flag the current check-in, if the comment for the last check-in is
"Negative". I managed to get the last check-in value carried over so I
can calculate the days, but I can't get the last comment carried over
to decide if this check-in needs to be flagged. Here is my SQL for the
query:

SELECT MyTable.CustID, MyTable.InDate, MyTable.Comments, (SELECT
MAX([InDate]) FROM [MyTable] as Dupe WHERE [Dupe].[CustID] =
[MyTable].[CustID] AND [Dupe].[InDate] < [MyTable].[InDate]) AS
LastInDate
FROM MyTable
ORDER BY MyTable.CustID, MyTable.InDate;

Any help would be greatly appreciated!

Elaine

 

[Sprinks]

How about:

SELECT MyTable.recID, MyTable.CustID, MyTable.InDate, MyTable.Comments,

(Select Comments From MyTable as MyDupe Where RecID =
(SELECT Max([recID]) AS LastRec
FROM MyTable as dupe
WHERE dupe.CustID=MyTable.Custid and dupe.RecId<MyTable.RecID) ) AS
LastComment
FROM MyTable;

This should give a list of all entries with a LastComment field
containing that customer's previous comment. This assumes that RecID is
a sequence number and that entries are made in date order.

 

[Elaine16025]

How about:

SELECT MyTable.recID, MyTable.CustID, MyTable.InDate, MyTable.Comments,

(Select Comments From MyTable as MyDupe Where RecID =
(SELECT Max([recID]) AS LastRec
FROM MyTable as dupe
WHERE dupe.CustID=MyTable.Custid and dupe.RecId<MyTable.RecID) ) AS
LastComment
FROM MyTable;

This should give a list of all entries with a LastComment field
containing that customer's previous comment. This assumes that RecID is
a sequence number and that entries are made in date order.

Thank you Sprinks, I appreciate your help. I copied your code to my
query, but received error message regarding the syntax. Since I am not
good at the SQL, I don't know how to fix it.

Still I want to thank you!

Elaine