?
Solved

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

Posted on 2013-02-06
6
Medium Priority
?
220 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 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.
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 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...
...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
Independent Software Vendors: 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 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.

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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