Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Rickmansworth, Hertfordshire, United Kingdom
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2016: Mission impossible?

    If I have a single record containing fields: name1, email1, name2, email2, can I query things so that I get a 2-column result of names v email addresses?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    You need to read-up on database normalization. Your table design would seem to violate one of the basic principles, which results in your having to go through all sorts of manipulates to get what should be a simple result.

    That said, this should work:

    SELECT name1, email1 FROM yourtablename WHERE name1 is not null
    UNION SELECT name2, email2 FROM yourtablename WHERE name2 is not null
    ORDER BY name1
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Rickmansworth, Hertfordshire, United Kingdom
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Mark, that worked. Your point is well taken but I did take the example out of context. I'll spell out the actual context because it must be a common scenario and therefore useful to others.

    It's a club membership database where each record might represent a single or double membership (husband & wife); each has their own email address. At the moment where I have two addresses per record they are in the same email field separated by a semi-colon. Where membership runs into 100s or 1000s you need to use a bulk email app such as Mail Chimp to send messages to all members. Mail Chimp doesn't like two addresses separated by a ';', so it becomes necessary to get all addresses into a single column from where they are exported to Mail Chimp via Excel. Or is there a more elegant way of coping with this scenario?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Perfect normalization is seldom achievable. My favorite saying (from a fellow I knew from another forum and who is an Access MVP) is to "normalize until hurts, then denormalize").

    In your situation, perhaps it is not necessary that any name be associated with a membership in the main membership record? It is just a Membership#, start date, maybe a billing address, etc. Then a child table contains any individuals (along with email addresses, maybe phone#, etc. One immediate question you need to ask yourself is "can there be more than 2 individuals on a membership?" What if a child can be listed?

    I'm not saying what you've done is necessarily wrong. It would be wrong if you didn't consider alternatives. If you did consider alternatives and concluded that this design worked best given all factors, then that's OK.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Rickmansworth, Hertfordshire, United Kingdom
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Mark, for those thoughts. No, there are never more than 2 individuals per membership but my situation is complex. We keep a base address table (with about 15 fields) which lists not only members but non-members whose details we keep and who we would like to recruit. We also have many other contacts of one type or another. Thus, I have 'categories' table and use a query to pull out 'members', 'non-members', 'overseas contacts' etc. This structure has worked well for many years and I fear a child table as you suggest might complicate things a step too far. The SQL you gave in your first post provides the enhancement I need for our system for which I thank you again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •