concatenate and compare access 2002

[ciohousecaptain]

I work for a non profit that works on houses of elderly, disabled and
disadvantaged homeowners who need help keeping the place up.

I have a list of all the homes in this poor neighborhood and need to compare
the parcel resident with the parcel owner to make sure the resident is the
owner.

The "owner" info is in one field called:
"Owner Addr"

and returns the following:
4915 E 22ND ST

The "resident" address is split into 4 fields which I have concatenated
below into the calculated field "Parcel"

Parcel: [Parcel Addr] & " " & [Parcel Addr Prefix] & " " & [Parcel Addr
Street] & " " & [Parcel Addr Street Type]

Which works fine and returns a "like" value...... 4915 E 22ND ST

The problem comes when a named street has no [Parcel Addr Prefix] field




(or [Parcel Addr Prefix] field is null) like

2420 BRIGHTON
2420 BRIGHTON AVE

As you can see there is an extra space in the "Parcel" field and the owner
address has an extra "AVE" that is not in the resident address.

sometimes they are switched: "Parcel" has the "AVE" and the "Owner Addr"
has no "AVE"

Parcel: 1526 CYPRESS AVE
Owner Addr: 1526 CYPRESS

I need to spend my time helping these old ladies and families with children
with no water or heat get their water or heat back on. So your help will be
appreciated by 400 poor families not just me.

I know there is a way to manipulate the string to remove the unwanted space
with a statement like:

If [Parcel Addr Prefix] is null (then eliminate the space) and (do
something with these inconsistant "AVE" or "TERR" OR "RD"
thingy's).......[Parcel Addr Street Type]

but that is as good as my SQL gets.

I know that is pretty easy for someone who speaks SQL.

Then what I need to do is return owners and residents that match.

If "Parcel" = "Owner Addr" then (return just people who own their homes)


Help me help these people.

Brian Sullivan
(e-mail address removed)
www.christmasinoctober.org

p.s. I know this is a long post... but then...

I need to eliminate owners who own more than one house. I hate to ask more
than one question... in one post... but I'm in a time crunch.

Thanks in advance.

 

[ciohousecaptain]

I found this answer to the space question by John Vinson:

(
In your query put a calculated field:

Display: [A] & ("/" + )

The + operator propagates NULLS, so if is NULL then ("/" + ) is
also NULL. The & operator also concatenates strings, but treats a NULL
as a zero length string.

John W. Vinson[MVP]
)


"The + operator propagates NULLS"

Thank You, John Vinson

Parcel: [Parcel Addr] & (" "+[Parcel Addr Prefix]) & " " & [Parcel Addr
Street] & " " & [Parcel Addr Street Type]

 

[John Vinson]

The "owner" info is in one field called:
"Owner Addr"

and returns the following:
4915 E 22ND ST

The "resident" address is split into 4 fields which I have concatenated
below into the calculated field "Parcel"

Parcel: [Parcel Addr] & " " & [Parcel Addr Prefix] & " " & [Parcel Addr
Street] & " " & [Parcel Addr Street Type]

Why the different storage formats? Would it be possible to parcel out
(sorry!) the Owner address?

I'm really leery of using address information as a *reliable*
identifier though. There's LOTS of potential problems: street names
misspelled, "10th St." vs. "Tenth St.", etc.

Which works fine and returns a "like" value...... 4915 E 22ND ST

The problem comes when a named street has no [Parcel Addr Prefix] field




(or [Parcel Addr Prefix] field is null) like

2420 BRIGHTON
2420 BRIGHTON AVE

As you can see there is an extra space in the "Parcel" field and the owner
address has an extra "AVE" that is not in the resident address.

sometimes they are switched: "Parcel" has the "AVE" and the "Owner Addr"
has no "AVE"

Parcel: 1526 CYPRESS AVE
Owner Addr: 1526 CYPRESS

I know there is a way to manipulate the string to remove the unwanted space
with a statement like:

If [Parcel Addr Prefix] is null (then eliminate the space) and (do
something with these inconsistant "AVE" or "TERR" OR "RD"
thingy's).......[Parcel Addr Street Type]

You can use a trick to get rid of the extra spaces: the + operator and
the & operator concatenate strings, but + returns NULL if either
argument is NULL, while & treats a NULL as a zero length string. You
can get rid of the double spaces with an expression:

([Parcel Addr] + " ") & ([Parcel Addr Prefix] + " ") & ([Parcel Addr
Street] + " ") & [Parcel Addr Street Type]

I know that is pretty easy for someone who speaks SQL.

Then what I need to do is return owners and residents that match.

If "Parcel" = "Owner Addr" then (return just people who own their homes)

I'd be inclined to go the other way: rather than constructing
(unreliably, given the above) I'd suggest splitting up the [Owner
Addr] field, and probably matching JUST on address number and street
name.

Help me help these people.

Brian Sullivan
(e-mail address removed)
www.christmasinoctober.org

p.s. I know this is a long post... but then...

I need to eliminate owners who own more than one house. I hate to ask more
than one question... in one post... but I'm in a time crunch.

A Totals query grouping by [Owner Addr] and counting records, with a
criterion of =1 on the count, would do this.

John W. Vinson[MVP]