E
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
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