Ask Experts Questions for FREE Help !
Ask
    inquiring_canadian's Avatar
    inquiring_canadian Posts: 3, Reputation: 1
    New Member
     
    #1

    Nov 15, 2005, 05:59 PM
    how to view single record of ALL people at single address?
    I have a table with addresses and names. I can easily sort it by address to see all the people living at the same address grouped together. However, this is problematic with mail-merges, since I wind up generating a new letter for each person, sending multiple mailings to the same residence.

    Here is what the table looks like:

    Name Address
    ===== ==========
    Mary 101 Franklin Ave
    John 101 Franklin Ave
    Andrew 101 Franklin Ave
    Matthew 101 Franklin Ave
    Tim 2 Main St
    Mark 2 Main St
    Sam 2 Main St

    Is there a way I can use a query combine all the names into a single "virtual" field, so that I only have one row for each address?

    Here is what I would like to see:

    Name Address
    ============= =================
    Mary, John, Andrew, Matthew 101 Franklin Ave
    Tim, Mark, Sam 2 Main St
    CaptainForest's Avatar
    CaptainForest Posts: 3,645, Reputation: 393
    Ultra Member
     
    #2

    Nov 15, 2005, 06:07 PM
    Are you using an Access Database or what?
    inquiring_canadian's Avatar
    inquiring_canadian Posts: 3, Reputation: 1
    New Member
     
    #3

    Nov 15, 2005, 06:27 PM
    Yes, I am using Access 2003.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Nov 16, 2005, 06:41 AM
    There is no way to do this with just a query. What you will need to do is loop through a sorted recordset and concatenate the first names into a temporary table.

    There is an issue of your database design. You should have at least 2 tables here; A table for households which has the address info and a table for household members which has the name info. That would make it easier.

    I also found a possible solution for you here:

    http://www.utteraccess.com/forums/sh...7063&bodyprev=
    inquiring_canadian's Avatar
    inquiring_canadian Posts: 3, Reputation: 1
    New Member
     
    #5

    Nov 16, 2005, 05:39 PM
    This looks like a promising solution. However, I managed to accomplish my task using Update and Append queries. It is a little intimidating to me to use VB code.

    Thanks for the help.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Nov 17, 2005, 06:03 AM
    VBA isn't that hard and you miss a great deal of the power of Access if you don't use it.

    You should also spend time researching normalization rules. Normalization is an absolutely essential process for designing databases. Normalized databases are more efficient, easier to use and easier to enter data into. The rules of normalization have existed for over 30 years so they have stood the test of time as nothing as come along to replace them.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Single mom [ 13 Answers ]

Daughter is 20, in the Navy, pregnant, and single. Baby's father is in the marines with three more years to serve. My daughter may/may not separate from the Navy after baby is born. She and baby's father are not together nor care to be. If she separates from the Navy, she would like to return...

No Man-single [ 3 Answers ]

Hey. I am a 23 year old female and I am still single. I am really tired of being single. Is theer something that I am doing wrong? I think that I am attractive. But I do wear a lot of sweatpants a lot. Should I wear more tighter fitting clothes just to get a man? Any advise, please let me know....

Why am I still single? [ 6 Answers ]

Last summer I had a "fling" with a guy that I've gone to school with all my life. We really liked each other, but just when I thought he was going to formally ask me out, we headed off to high school (we both attend the same one) and he wouldn't talk to me anymore! Boom and it was over! No "let's...


View more questions Search