Get value from previous record Elaine16025[ at ]yahoo.com 29.09.2006 05:30:43 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

Re: Get value from previous record "Sprinks" <simon.gunning[ at ]ntlworld.com> 29.09.2006 21:26:21 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.

Re: Get value from previous record Elaine16025[ at ]yahoo.com 30.09.2006 02:40:38 Sprinks wrote: [Quoted Text] > 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