Query to combine names from different records with the same address?

[Roknor]

Here an image of my table:

I need a query that will combine the first names of each person who lives at a particular address combined with their last name.

Essentially, I need this:

Abram Jeffery 858 Crystal Lake Rd. Whitehall, MI 49461
Abram Petra 858 Crystal Lake Rd. Whitehall, MI 49461

To be this:

Jeffery & Petra Abram 858 Crystal Lake Rd. Whitehall, MI 49461

I cannot combine the records. They need to remain distinct.
Any help would be appreciated.

Thank you.

[jforbes]

Using this function is about the only way I know to do this in Access:Concatenate values from related records

[PhilOfWalton]

Your database is not normalised.
You should have a table of addresses with AddressID as the key, and a table of Contacts with AddressID as the foreign key.

Simple guide to normalisation. Anything in a table should be self contained, so Contacts would include names, emails, mobile phone, hair colour, gender etc. That contact exists in his or her own right.

Similarly an address (house) exists in it's own right. Whether that address has anyone living in it that is relevant to you is immaterial. The address would contain details of Address lines & Zip code. To my mind, the Towns should be in a separate table, as should the State, because both Towns & States exist in their own right.

If you get this set up properly, and use a Combo box to select a town, then the State is automatic. Equally once a town is set into the database, there is no possibility of spelling mistakes.

Possibly this picture will give you the right idea (bearing in mind this is for the UK)

Phil

[Roknor]

Wow, thank you both for your suggestions.

JForbes, the concatenate function is exactly what I was looking for. I could do it in Excel easy enough, I just couldn't figure out the formulas for Access. Thankfully, I was able to get this to work, but with 10,000 records it did take a while for the query to run. I should be able to fiddle with it to get what I want.

PhilofWatson, I will have to figure out the best way to import the excel files into my database in order to get the best table set up and still have the proper connections. This is not my data, it is a copy of the voter rolls I receive after each election. There are over 28 columns of data, each value has it's own column. (E.g. Street prefix, Street Number, street name, street suffix, apartment number, state, city, zip, etc.) I simplified it for my example above.

Again, thank you both very much.