[Mr Bill]

Please help. I need to perform the fllowing in MS Access 2007

CREATE TABLE dbo.Foo
(
FieldA varchar(50) NOT NULL,
FieldB varchar(50) NOT NULL,
FieldC varchar(50) NOT NULL,
FieldD varchar(50) NOT NULL,
FieldE varchar(50) NOT NULL
)
go

insert into foo values('abc123', '123abc', '01', '01', '')
insert into foo values('abc123', '123abc', '012', '012', '')
insert into foo values('abc123', '123abc', '0123', '01', '')
insert into foo values('abc123', '123abc', '01234567', '01', '')
insert into foo values('abc123', '123abc', '012345', '012345', '')
insert into foo values('def123', '123def', '012345', '012345', '')
insert into foo values('def123', '123def', '', '012345', '')

select * from
(
select *, row_number() over(partition by fielda, fieldb order by len(fieldc) + len(fieldd) desc) seq
from foo
) ordered
where seq = 1;

The Idea is to resolve duplicate rows (based upon fields A & B) into single rows while directing which duplicate rows to toss out (i.e. keep the rows with longest entries in fields C & D), allowing field E to tag allong for the ride.

Results from the above should be 2 rows:
'abc123', '123abc', '012345', '012345', ''
'def123', '123def', '012345', '012345', ''


Thanks,

Mr Bill