How to retrieve multiple name fields in query for label report.

Posted on 2013-02-06
Medium Priority
Last Modified: 2013-08-09
I am using Access 2007.
I have a table of names and addresses for mother, father, step mother, step father, foster parent, guardian and significant other relationships for patients.  I need to retrieve the active names and addresses, depending on the family dynamic,  to print on mailing labels.  I may be overthinking this.  Since I can only assign firstname, lastname, address etc to a label, I have in mind to make a create table query to extract the active names and addresses and insert them into generic fields in the created table and use that as the data source for the label report.  Then delete the table to be recreated the next time the report is run..
Is this feasable and is there a better way to approach it?

Question by:leachj
  • 3
  • 2
LVL 31

Accepted Solution

Helen Feddema earned 664 total points
ID: 38860071
If each person has a separate record with name and address information, all you need to do is add a Yes/No field called Active to the table, and check it for the addresses you want to use in each mailing.  Then make a select query filtered by Active = True.

If you don't have separate records for each person, it is more complicated, and we need more information on your table and field setup.

Author Comment

ID: 38860275
Currently there is no active yes/no field but could be added as this is in development.  There is a ParentGuardian table which contains the possible parent relationship with the patient. i.e. father name, address, city etc, stepmother name, address and other possible relations.  I can use the information gathering form to test which set of parents, guardian, sig other etc is being used for the mailing and set the active yes/no in the table for each.  How would I pick those out and put them into the labels even if I had an active field to indicate which ones were being used so I don't have to have several label formats?
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1336 total points
ID: 38860289
Yes with the term "Family" being constantly redefined, this is tricky...
For example what if the Grandparent is the Guardian?
What if the Mother and father Divorce or separate?
What if the SO is the Guardian, then the SO gets married?
What about Joint Custody...

But to be clear, ...for each "patient", how many labels are you printing out?
For example if Mother/Father, ..are you printing one label that says "Mother/Father", or do you want one label to the mother and one to the father?

In other words you need a "Mail To" designation where one or more people will receive any mailings

For example:

gGetMailing (Yes/No field)

Here, you can use the "gGetMailing" field instead of "Active", because the situation will inevitably arise where perhaps  a Gardian may be "Active", but another Guardian will only be allowed to receive mailings...
(But here, perhaps you need to clearly define what "Active" means in your organization)

You can also do things like concatenating the names if more than one person is the guardian... (or can receive mailings)

Now here is where it gets complex.
You may need a system to ensure that at least one Guardian per patient has the gGetMailing (or "Active") field set to yes.

But again, perhaps you could explain this requirement in a bit more detail...

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

ID: 38860495
I believe I have the parents designated to receive mailing in my logic.  I they are considered a parent pair by us (i.e. mother/father, mother/stepfather, father/sig other etc., there will only be one mailing per address.  If there is a divorce and seperate addresses, we chose to select only the person responsible for the patient.  We discussed the scenarios you mentioned in detail.  The field names in the table are designated as MFname MLname for mother, GFname GLname for guardian etc.
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1336 total points
ID: 38861229
Again, lets be *very clear*

<I believe I have the parents designated to receive mailing in my logic.>
Both parents or only one?, ..if one , then how do you decide which one...

>If they are considered a parent pair by us (i.e. mother/father, mother/stepfather, father/sig other etc., there will only be one mailing per address.>
per *What* address?
The patient address or the Parents address?
If the parents address, ...again, how are you deciding which one?

Perhaps this would be easier if you provided a Matrix of *exactly* what you wanted to happen for each contingency.

So that this does not turn into a nightmare of conditions, trying to allow for an infinite number of possible scenarios.


Author Comment

ID: 38867740
For the purposes of this database, there is a combobox that allows for entry of 9 possible scenarios for family dynamic, (Father, Mother, Father and Mother, Father and Sig. Other, Mother and Sig. Other, Father and Step Mother, Mother and Step Father, Foster Parent, and Guardian).  If 2 are paired, i.e. Father and Mother or Mother and Step Father), only one address will be used for mailing.  The mailings will go to the Parents/guardian or foster parent address as this deals with pediatrics and the adult is the target.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question