Create query to pick one choice from many

[Brian]

I have a list of 144,000 addresses. The list consists of high density housing
addresses. So for example, there are 50 addresses from the same apartment
complex. The difference in the address line is only the unit number. There
are thousands of apartment complex addresses in this database and various
units designator types like Unit, SPC, Apt, etc.

I need to create a query that will winnow the addresses done to only one. So
basically I need only one address for the complex instead of all the
addresses for the individual units.

 

[KARL DEWEY]

It would help a whole lot to tell what your data structure looks like for the
addresses.

Is the complete address in one single text field or in several fields?

 

[Brian]

Dwelling ID Primary Given
Name Surname N_ADDRESS N_CITY N_STATE N_ZIP N_LAT N_LON
6 17 GOODWIN ABRAMSON 2400 ALPINE BLVD SPC
147 ALPINE CA 91901 32.835499 -116.762189


The addresses are all in one column.

 

[Jeff L]

In the design of your query, you will need a column for each of your
designators. It will look like this. UnitLoc: InStr([Address],"Unit
")
UnitLoc: InStr([Address],"Unit ")

Another one would be: AptLoc: InStr([Address],"Apt ")
etc

You would then need another column that looks like:
Expr1:
IIf([UnitLoc]>0,Left([Address],[UnitLoc]-1),IIf([AptLoc]>0,Left([Address],[AptLoc]-1),""))
Obviously you are going to need to add to it for your other
designators.

 

[KARL DEWEY]

Try using two queries. The first one a totals query with the left-most say
15 characters of N_ADDRESS plus N_CITY, N_STATE, and N_ZIP.

The second query use your table and the first query. Join N_CITY, N_STATE,
and N_ZIP. Again use a field of the left-most 15 characters of N_ADDRESS
from the table and use the first query 15 character field as criteria.

Analyze the data and see how good it looks.