Impossible? Query to track changing records

[AUGuy]

I'm trying to fulfill a user's request to track a particular field. In this situation, a cumulative dailiy log table is appended to every day along and includes a datestamp. (i.e. acct#, accrualstatus, otherstuff, datestamp)

The accrualstatus is a text field that can only be an 'N' or 'Y'. The user needs to know the date and other information associated with that record when this accrualstatus field changes from N to Y or Y to N.

Is this possible with a straight-up query? If not, is there some other way to do it?

Thanks,
G

 

[Trevor G]

How do they change this, through a form or direct in the table?

If a form you can add an auditing feature to places the results direct into an audit table, then possibly narrow the findings down with a query to show a selected date range rather than the whole history of changes.

 

[AUGuy]

The individual record doesnt actually change, but new information is appended daily, for instance you'd have 3 days of data

Acct# AccrualStatus Date
123456 N 20101028
123456 Y 20101027
123456 N 20101026

User needs to know that on the 27th it changed from N to Y

 

[AUGuy]

I think i found a solution, Here it is in case anyone is curious:

SELECT [Daily Report Data Pool].[Account-Number], [Daily Report Data Pool].[Date of Data] AS Date1, [Daily Report Data Pool].[Non-Accr-Flag] AS NA1, [Daily Report Data Pool_1].[Date of Data] AS Date2, [Daily Report Data Pool_1].[Non-Accr-Flag] AS NA2
FROM [Daily Report Data Pool] INNER JOIN [Daily Report Data Pool] AS [Daily Report Data Pool_1] ON [Daily Report Data Pool].[Account-Number] = [Daily Report Data Pool_1].[Account-Number]
WHERE ((([Daily Report Data Pool_1].[Date of Data])=[Daily Report Data Pool]![Date of Data]+1) AND (([Daily Report Data Pool_1].[Non-Accr-Flag])<>[Daily Report Data Pool]![Non-Accr-Flag]));