Help with duplication query

[khinester]

Hello,
I am trying to write an update query which will flag a record if it meets a certain condition.

Here is what I have so far, we have a list with adressees, and we have more then one addressee per address. I've done a count procedure and its showing me that i have 2,368 that only have one match record, 518 that have 2 addressees, 194 - 3, 89 -4 and so on and on.

My question is based on this, is there a way to flag all the once upto say 4 and leave out anything with more then 4 addressees?

Or can I pickup only one record out. from each?

I've tried using the Deduplication procedure, but is not working for me! If someone can guide me on how to do this by an sql it will be most welcomed.

Thanks

 

[hopper]

you could do something like this, but the time taken to run this will be high. If it is a one time shot or something you only run occasionally it might not be an issue.
Something like this:


dim MyArray as variant
dim conn as adodb.connection
set conn = currentproject.connection
dim rs as adodb.recordset
set rs = new adodb.recordset
rs.open("SELECT DISTINCT Address FROM tblYourTableName&quot, conn
MyArray = rs.getrows()
rs.close
rs.open("SELECT * FROM tblYourTableName ORDER BY Address&quot, conn
with rs
for i = 0 to ubound(MyArray, 2)
if MyArray(0,i) = !Address then
!FieldToFlag = 0
.update
end if
.movenext
next
end with
rs.close
set rs = nothing
conn.close
set conn = nothing


This is assuming that you have a field in your table that you are using as a flag (FieldToFlag)

Hope this helps.