Solved

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

Posted on 2013-02-06
6
218 Views
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?

Thanks,
John
0
Comment
Question by:leachj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 166 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.
0
 

Author Comment

by:leachj
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?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 334 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...
...etc
;-)

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:
tblPatients
pID
pFName
pLName
pAddress
...ect

tblGaurdians
gID
gRelationship
gFName
gLName
gAddress
gGetMailing (Yes/No field)
...ect

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...

JeffCoachman
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:leachj
ID: 38860495
Jeff,
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.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 334 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.

JeffCoachman
0
 

Author Comment

by:leachj
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.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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