Results 1 to 8 of 8
  1. #1
    cechopin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    9

    Creating a membership address report with only one address per househol

    This is probably an easy thing to do but I cannot find the answer I am looking for. I have created a query that lists the address for all of our members for the past two years. It is using our membership table and our member table. When I run the query it shows me every time each member contributed, but since I am doing a mailing list, I only need one record (address) for each member. Do I do this in SQL or do I modify my query? Please let me know if you need more information.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would expect the query to be based on the member table, not a table containing transactions. Why is the transaction table included?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    cechopin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    9
    It is based on the table that contains our membership deposits, meaning it lists every time a member paid their annual contribution. The other table has the member's address.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since you're "doing a mailing list", wouldn't the other table be the logical source, if it's the one with addresses?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    cechopin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    9
    I actually have a query that combines all the tables I need. I am having trouble eliminating the duplicate records on the report for example:

    Jean Able 9701 Vire Rd Rockville, MD 6/01/15 $15
    Jean Able 9701 Vire Rd Rockville, MD 6/01/16 $15

    I want my report to only write Jean's address once even though she gave twice in the date frame I am using.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you want detailed info from the transaction table, you'll get multiple lines. Those aren't technically duplicates, since they're for different dates. If you want a single line for Jean, you need to decide what info you really want from the transaction table. I have to guess, so maybe you want this?

    http://allenbrowne.com/func-concat.html

    to display the dates and payments. Or maybe this to show the last payment:

    http://www.baldyweb.com/LastValue.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    cechopin is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    9
    Actually I don't need the information on the last payment, I just need all the member addresses with only one per household. I guess I'm not explaining this very well. I have multiple members listed on my address report because they gave money for multiple years. I want to send the members our annual report but I only want to send one to each household regardless of how many times they have contributed.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Then I'm back to why is the transaction table included? If you only include the member table, you should get:

    Jean Able 9701 Vire Rd Rockville, MD

    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 01-06-2018, 03:26 PM
  2. Creating an address book
    By faythe1215 in forum Reports
    Replies: 3
    Last Post: 04-09-2015, 06:38 AM
  3. Replies: 2
    Last Post: 08-22-2013, 12:02 AM
  4. Replies: 1
    Last Post: 10-07-2009, 08:15 AM
  5. Input Mask for an IP Address and Mack Address
    By baksg1995 in forum Access
    Replies: 18
    Last Post: 06-23-2009, 12:33 PM

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