• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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

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
leachj
Asked:
leachj
  • 3
  • 2
3 Solutions
 
Helen FeddemaCommented:
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
 
leachjAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
leachjAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
leachjAuthor Commented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now