Results 1 to 14 of 14
  1. #1
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33

    Mailing labels for spouses

    Hello, I know this has been posted before, but I'm just not seeing enough details in the answers to get me through. Please help if you can!

    I run a travel database, and each traveler is usually traveling with their spouse. 10% of the time they travel alone. I need to make mailing labels (among other merged documents) where I can send items to one spouse/traveling companion, or both if they happen to register for a trip together. I've followed the advice of other forums, and have two different tables: one for all travelers (TravelerID, HouseholdID, Last Name, First Name, Email). And the second table is a Household table (Household ID, Address, City, State, Zip).

    I've tried making a query to bring both the first and last name of each couple, but then they appear twice (John and Jane Smith at 123 Bldv. then Jane and John smith at 123 Bldv).

    What exactly am I doing wrong? How do I get rid of the duplicates? Is it somewhere in the relationship that I need to edit? I'm new to Access (only a month in), so the more specific the better. I just started learning about joins, but unfortunately I can't get much farther.



    Thank you, lovely community!

    Bridget

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Maybe select the TOP 1 from each Household? Alphabetically, that would be Jane. Review http://allenbrowne.com/subquery-01.html#TopN

    You show John and Jane at two different addresses. Or is that a typo?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Quote Originally Posted by June7 View Post
    Maybe select the TOP 1 from each Household? Alphabetically, that would be Jane. Review http://allenbrowne.com/subquery-01.html#TopN

    You show John and Jane at two different addresses. Or is that a typo?
    Thanks for responding! I wouldn't want to mark them alphabetically only because format dictates "John and Jane Smith" and not the other way around (my clients would be very sensitive to this). This TOP idea is interesting though. I could mark one as the TOPfor example, John, but what if John isn't traveling on a trip? Would it still pull Jane's address if she isn't listed as TOP? I suppose I could update it with each new reservation but that seems a bit cumbersome. Thoughts?
    Last edited by bridgeo730; 01-07-2018 at 12:53 PM.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Really? Why must John be first - because he's male? What rules determine which name must be first?

    How do you document in the reservation if it is for individual or couple?

    What to do you mean by this is a 'travel' database - employee travel reimbursement, hotel booking, travel agency? No children in the household traveling with parents? Exactly what is this data?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    We sell international group trips to our largest donors. I would say 90% of our donors are male. This is why their name, unless otherwise noted, comes first (I've been doing in by hand in excel for the past few years). If John is a $300,000 donor and is traveling with his wife who is not a donor, I cannot address the letter to Jane and John, which as I said is 90% of the time. But John might not be available for a trip and Jane still wants to go as a single, then the label would just have her name on it (which isn't the label issue).

    But at any rate, knowing our company policy on this, and getting back to the original question... The DB is not yet up and running (still in spreadsheets until this issue is solved). I plan to register each person individually. All I need to know is who is the primary traveler (the donor), who they are traveling with (children not accepted, so it's usually a spouse or brother or parent, all uniqueID), and which trip they registered for (Paris site-seeing, SA safari).
    Last edited by bridgeo730; 01-08-2018 at 10:21 AM.

  6. #6
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    in fact you need 2 more tables: Trips and TripTravelers. In the Trips table you put the trip information (date depart, date return, destination, ...). TripTravelers is the linking table between Trips and the table Travelers.

    tblTrips
    -----------------
    tripID
    tripDateDepart
    tripDatereturn
    tripDestination

    tblTripTravelers
    ---------------------
    ttID
    ttTravelerID
    ttTripID


    The name info comes from the table Travelers and the address info can be picked up from the Household table.

    This way you can easily print labels per trip.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I would say the two tables will one for primary traveler and the second for optional partner, with just the name. The label will then be to John (primary traveler), with an optional "and Jane" if a record exists on the second table and if the last names are the same (otherwise John Smith and Jane Doe). If Jane is traveling as a single then she would get her own label.

    There will be only one record per primary traveler in the query if done this way.

  8. #8
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    in fact you need 2 more tables: Trips and TripTravelers. In the Trips table you put the trip information (date depart, date return, destination, ...). TripTravelers is the linking table between Trips and the table Travelers.

    tblTrips
    -----------------
    tripID
    tripDateDepart
    tripDatereturn
    tripDestination

    tblTripTravelers
    ---------------------
    ttID
    ttTravelerID
    ttTripID


    The name info comes from the table Travelers and the address info can be picked up from the Household table. If you want only 1 label per household than add the field 'MainTraveler' Yes/No to the table travelers. This way you can use the correct build-up of the name in a calculated field.

    This way you can easily print labels per trip.

  9. #9
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Thanks Noella. I forgot to mention that I do have those tables. Sorry, thought it was implied. So total I have: a Traveler List (all travelers listed uniquely), a Household List, a Trip list, and a Registered Trip list (where I mark the travelers as registered or cancelled for a respective trip). I am still running into issues though with the double printing of spouses with this method. Because the Name info will come twice from the Registered trip list, both as John and Jane, and Jane and John. I'm looking for a way to eliminate the duplicate.

  10. #10
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Interesting approach, aytee111. I might run into issues when Jane becomes the primary traveler (traveling with her mother, or adult daughter, or something, which is rare, but it does happen). But this is an interesting take.

  11. #11
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    It looks like the best solution for now is to just have one spouse related to another, but not reversed... For example, I now have in my Traveler List the TravelerID, and a Related Traveler ID. John is related to Jane (self join, no need for two tables). BUT, in Jane's ID, she is not related to anyone (otherwise the labels would pull, as I already stated, John and Jane, and then Jane and John). I will just updated the relationship based on who the "head of the household" ie the donor. Hoping there aren't errors with this, but it's the solution that I found and it works... for now!!

  12. #12
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035

    Solution example

    Hi, maybe the included example contains the solution you need : the travelers priority is put into the table tblTripTravelers, so the priority can change per trip. For labels only travelers with priority 1 and 2 are taken into account, nut there can be more travelers/household with lower priorities. The result query is qryLabels.

    Example.zip

  13. #13
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    This is really great, NoellaG!

    Being a total newbie, I'm having trouble reading the syntax in the label creation column. Where/when do you incorporate the priority level? And if the travelers have different last names, I think it would look different. To keep it simple, I'll just separate it like this:
    John Brown
    Jane Brown
    123 N. Boulvard Lane

    and then

    Kenneth White
    Gary Blue
    321 S. Road

    Attaching what I have, be kind, I'm new!
    Attached Files Attached Files

  14. #14
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, the separation of the level is done in the intermediate queries: qryTravelers1 only contains the travelers from level1, qrytravelrs2 those level2. If you want their last name instead of the family name, just change the intermediate queries to contain the correct individual last names from table travelers and use them to build up the name in the same way as I have build the first names.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Mailing labels for 'no-email' records ONLY
    By Angeline in forum Queries
    Replies: 9
    Last Post: 06-25-2015, 02:13 PM
  2. Linked Data Bases for Mailing Labels
    By Kennertoy in forum Access
    Replies: 6
    Last Post: 03-20-2013, 02:26 PM
  3. Mailing labels formatting
    By Keith Sayers in forum Reports
    Replies: 11
    Last Post: 01-01-2013, 01:13 AM
  4. Centering Mailing Labels if line is Blank
    By shane201980 in forum Reports
    Replies: 4
    Last Post: 10-24-2012, 09:00 PM
  5. Mailing Labels from a Search Form
    By waltb in forum Access
    Replies: 4
    Last Post: 03-08-2012, 06:03 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums