Look at Prior Record to ID a change

[lakers8175]

I have query that shows 3 columns, I want to be able to ID a change in one new column, each time it changes. Can anyone help me? I want the query to look at the prior record, the query is sorted on time, and if it changes I want the new column to display "change" if there is not a change then, blank. Example below. I have a total query but it doesn't work because sometimes I go back to a prior [Sect], I want that to show as a change as well.

Number Sect Time newCol
12345 AA1 00.01
13214 AA1 00.99
13456 AA2 01.11 Change
12321 AA2 02.01
12322 AA1 12.01 Change

 

[dhookom]

Try to be more clear. You mention "it changes" a couple times without stating "the value of the field Sect changes". Also, you didn't give us any table or query names.

I would try something like:

SELECT [Number], [Sect], [Time], IIf([Sect] <> (SELECT TOP 1 SECT FROM aquery B WHERE B.[Time]<aquery.[Time] ORDER BY [Time] DESC), "Change",Null) as NewCol
FROM aquery

Do you understand that Time is a poor name since it is a function name?

Duane
Hook'D on Access
MS Access MVP