Solved

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

Posted on 2013-02-06
6
214 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
  • 3
  • 2
6 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 166 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:leachj
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now