database - Access: Removing a duplicate if two fields are the same -

i have access database table contains duplicate profileids. create query excludes 1 (or more, if necessary) of duplicate records.

the condition duplicate record excluded is: if pricebefore , priceafter fields not equal, considered duplicate. if equal, duplicate field remains.

in example table above, records id 7 , 8 have same profileids. id 8, pricebefore , priceafter not equal, record should excluded query. id 7, 2 equal, remains. note pricebefore , priceafter id 4 same, profileid not duplicate, record must remain.

what best way this? happy use multiple queries if necessary.

create pointer query. call pquery:

select profileid, sum(1) x mytablename having sum(1) > 1 

this give profileid of every record that's part of dupe.

next, find records prices don't match. call pnomatchquery:

select mytablename.* mytablename inner join pquery on pquery.profileid = mytablename.profileid pricebefore <> priceafter 

you have query of every record should excluded dataset. if want permanently delete of these records, run delete query inner join source table pnomatchquery:

delete mytablename.* mytablename exists( select 1 pnomatchquery pnomatchquery.id = mytablename.id ) = true 

first, make absolutely sure pquery , pnomatchquery returning expect before delete source table, because once it's gone it's gone (unless make backup first, highly suggest before run delete first time).