Mark a change in value?

[markbrum]

Hi,

In need to run a query to create 'newfield' below, basically there are a random number of people in each areacode - i'd like to insert a new field and mark with an x or whatever everytime the value changes to aid sorting.

id name areacode newfield
1 mark 1234 X
2 dave 1234
3 gary 1234
4 alan 5678 X
5 sue 5678
6 paul 9012 X
7 helen 4567 X
8 linda 4567

I was going to draw the first record out using DISTINCT on the areacode field but I don't think that's going to get me what I want.

Thanks, Mark.

 

[lynchg]

Are these records ordered by area code?

I would solve this in code by creating a recordset, holding the previous area code value in a variable as I step thru the recordset. Then update newfield with an X everytime that area code does not equal the last area code value.

dim rst as recordset
dim dbs as database
dim LastAreaCode as string

set dbs = CurrentDB
set rst = dbs.OpenRecordset("Select * from tblAreaCode")

rst.MoveFirst
LastAreaCode = 0

Do Until rst.EOF
If rst!areacode <> LastAreaCode Then
rst.Update
rst!newfield = "X"
rst.Edit
End If

LastAreaCode = rst!areacode
Loop