Access 2016: Mission impossible?

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote