Address Database need to combine names if address field is the same

[aussiegrl]

I have a straightforward address database with:

ID (autonumber)
First Name
Middle Name
Last Name
Address
City


State
Zip

I need to create a query or something to combine first names with last name if the address is the same. Example:

Bob J. Smith 1515 Canopy Lane Hope MI 48642
Linda R. Smith 1515 Canopy Lane Hope MI 48642

So these are two records in the database and I want to create mailing labels, but want it to be addressed as:

Bob & Linda Smith
1515 Canopy Lane
Hope, MI 48642

Can anyone help me with this? I know it can't be too difficult, but I am stumped. THANK YOU!

[orange]

There are people who have built careers trying to cleanse data.

Perhaps you could post 50 records as a sample of the data you have.
Do you have variations in Street, Str, St., Lane Ln, PO Box , P.O. Box#..... 55 Apple Street; 55 Apple St, Apt D;
How do you know what makes up the name or the address etc?
You may find names like Oscar de la Hoya, Ali vant Goor --- that will/could pose a logic issue.

In your sample, how do you know that Bob and Linda are related/a couple?
Could be Father/daughter; mother and son.....

You may have better data than most, but I've seen a lot of these requests that are far from "I know it can't be too difficult".

[aussiegrl]

They are only spouses as that is what the database was created with. There are about 5500 duplicate records when I did a duplicate record query on the address field. After a quick scan all addresses match as far as variation goes. It is definitely turning out to be more difficult then I thought. I figured I could write some kind of IF statement like: IF Address is = then Concatenate FirstNames, but I can't figure out how to make that work. UGH!! Coding is definitely not my strong suit. I may have to break down and do it by hand.

[orange]

OK, can you post a copy of your data base. You may have to zip it.

[bridgeo730]

I would be interested in knowing how this was solved!