Duplicate Records

[jonp]

Hi there!

I have a table that consists of 3 fields. An account nbr field, a serial nbr field, and a date field. I have a query that will tell me which serial numbers are duplicated. However, I would like the query to only look for duplicate serial numbers for each account number. Is there anyway of accomplishing this?

Any suggestions would be greatly appreciated!!

jonp

 

[pdx_man]

Group By the AcctNum and check the records. If you're using VBA:

set dbs = currentdb
set rst = dbs.openrecordset("Select AcctNum, SerialNum from MyTable group by AcctNum, SerialNum")
rst.movefirst
TempSer = rst!SerialNum
rst.movenext
do
If TempSer = rst!SerialNum then MsgBox rst!SerialNum
TempSer = rst!SerialNum
rst.movenext
loop until rst.eof

I think that should work

 

[jonp]

Thanks PDX-Man for your response. However, I tried to use the GROUP BY function within ACCESS (not VBA) in a query. I need to view all 3 fields in the query results so I really dont want to group on each field. What do I set the other fields to so that query will look for duplicate serial numbers based within each account number?

Thanks,

jonp