By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

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

P: 2
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.
Mar 18 '17 #1

✓ answered by jforbes

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

Share this Question
Share on Google+
3 Replies


jforbes
Expert 100+
P: 1,107
Using this function is about the only way I know to do this in Access:Concatenate values from related records
Mar 20 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
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
Mar 20 '17 #3

P: 2
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.
Mar 21 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.