Remove Duplicates

[eabano]

I have a table with the following data. If I use the Remove duplicates Query in Access it removes all the address that has the same street number and leave one address. So if I have 200 unit apartments I only get left 1 address

Any assistance is greatly appreciated.

[orange]

What exactly do you consider "duplicate"? Be very explicit so we understand what you are trying to do.

You might consider looking for and adjusting/removing/fixing records where Apartment_ is empty/blank.

[eabano]

I want to have one address like this one

4425	BADMINTON	DR		TPIA
4425	BADMINTON	DR		BSMT

Reduced to

4425

BADMINTON

DR

And those Addresses with APARTMENT UNIT Numbers as shown below should not be showing duplicates like the one below.

2000	CREDIT VALLEY	RD		110
2000	CREDIT VALLEY	RD		111
2000	CREDIT VALLEY	RD		112
2000	CREDIT VALLEY	RD		113
2000	CREDIT VALLEY	RD		122
2000	CREDIT VALLEY	RD		123
2000	CREDIT VALLEY	RD		124
2000	CREDIT VALLEY	RD		125
2000	CREDIT VALLEY	RD		201

[rpeare]

Why do you need to remove duplicates? Seems to me you could get what you're after using an aggregate query

SELECT Street_Num, Street_Nam, Street_Typ FROM <TableName> GROUP BY Street_Num, Street_Nam, Street_Typ

[eabano]

Doing this I will lose the apartment units such as below

2000	CREDIT VALLEY	RD		110
2000	CREDIT VALLEY	RD		111
2000	CREDIT VALLEY	RD		112
2000	CREDIT VALLEY	RD		113
2000	CREDIT VALLEY	RD		122
2000	CREDIT VALLEY	RD		123
2000	CREDIT VALLEY	RD		124
2000	CREDIT VALLEY	RD		125
2000	CREDIT VALLEY	RD		201

I will only get:

2000

CREDIT VALLEY

RD

Without the Apartment Units.

[rpeare]

so add the apartment number to the query

[orange]

And those Addresses with APARTMENT UNIT Numbers as shown below should not be showing duplicates like the one below.

What/why exactly do you think these are duplicates?
What are you trying to accomplish---please be specific and use simple, plain English?