Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Query Sorting

  1. #1
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18

    Query Sorting


    I have a donation database and built a query and report to do what I need it to do but now that I'm getting a couple years information built in I need it to filter out the duplicate donor's entries. How do I ensure I'm removing the duplicate donor but I would like to keep the most current year's information? I can't make it filter by just the year because I want a complete list of donors weather they have given or not.

  2. #2
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,454
    What are you trying to show, just the latest entry per donor? Create a pre-query, one that contains just the donor and the date(s) you want. Then bring that query in to the one you are using to narrow down your results.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,493
    Make a query, qsDonors, that pulls all donor names and IDs. Set the property UNIQUE VALUES= ttrue.
    make another query, qsDonations1Year, add the data table, and the qsDonors query.
    join the 2 on DonorID,
    dbl-click the join line.
    set propert to OUTER join,
    Set All recs in qsDonors, some recs in Data,
    bring Down fields: qsDonor.Name,tData.Amt
    bring down tData.Date field,and set the criteria to a date range.
    save query as qsDonations1Yr

    run query.

  4. #4
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18
    Well, it's not letting me attach the photos I wanted to but when I do this it is giving me the year I wanted but it seems that it's almost duplicating the 2016's and then it isn't including the 2015's that aren't a duplicate of 2016. Hope that makes sense. Did I miss something or do it incorrectly?

  5. #5
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18
    Well, it's not letting me attach the photos I wanted to but when I do this it is giving me the year I wanted but it seems that it's almost duplicating the 2016's and then it isn't including the 2015's that aren't a duplicate of 2016. Hope that makes sense. Did I miss something or do it incorrectly?

  6. #6
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,454
    Not sure where photos have come in to the picture now. Did you follow the instructions and get the queries working?

  7. #7
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18
    Quote Originally Posted by aytee111 View Post
    Not sure where photos have come in to the picture now. Did you follow the instructions and get the queries working?
    I was wanting to show screenshots of what I was doing to see where I'm going wrong... No I haven't gotten it to work correctly. It is only showing 2016's information not all of the entries other than duplicates if they have given more than one year.

  8. #8
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,454
    If none of these instructions have give you what you are after, then maybe we aren't understanding you. You don't need pictures, type in the data - what it looks like on the table and the way you want it to look afterwards.

  9. #9
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18
    Quote Originally Posted by aytee111 View Post
    If none of these instructions have give you what you are after, then maybe we aren't understanding you. You don't need pictures, type in the data - what it looks like on the table and the way you want it to look afterwards.
    Sorry, I understand better visually...

    Larry 2015, 2016
    Sally hasn’t given
    Harry 2015
    Moe 2015, 2016

    So from the "statistics" above I'm wanting to pull Sally(someone that hasn't given), Harry (someone that has given in previous years), and Larry/Moe's (2016 only/ most current) information.

    Does that make sense?

  10. #10
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,454
    Query 1 for donor names: (SELECT DISTINCT Donor FROM TableName)
    - now you have all donors
    Query 2 for "given" data: select donor and year, Group By donor and take Max for year
    - now you have all donors that have given and the last year they gave
    Query 3 for information: query 2 and table with data, join using donor and year and bring in the fields that you want
    - now you have the information for the last time the donors gave
    Query 4, get a full list of donors: query 1 and query 3, left join on donor, bring in name from query 1 and information from query 3

  11. #11
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18
    Quote Originally Posted by aytee111 View Post
    Query 1 for donor names: (SELECT DISTINCT Donor FROM TableName)
    - now you have all donors
    Query 2 for "given" data: select donor and year, Group By donor and take Max for year
    - now you have all donors that have given and the last year they gave
    Query 3 for information: query 2 and table with data, join using donor and year and bring in the fields that you want
    - now you have the information for the last time the donors gave
    Query 4, get a full list of donors: query 1 and query 3, left join on donor, bring in name from query 1 and information from query 3

    Almost got it! Now, if they have given for more than one year it looks like in the date column it is turning all years to the latest year. ie. from our sample above Larry and Moe it is turning 2015's donation to a 2016 and both are appearing.

  12. #12
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,454
    Which query is it that is causing this?

  13. #13
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18
    Quote Originally Posted by aytee111 View Post
    Which query is it that is causing this?
    On Query 3

  14. #14
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,454
    Did you follow the instructions? "join using donor and year"

  15. #15
    Snickren is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    18
    Quote Originally Posted by aytee111 View Post
    Did you follow the instructions? "join using donor and year"
    If I join the donor to the year the report comes up blank.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Sorting
    By BringTheR41n in forum Queries
    Replies: 3
    Last Post: 07-11-2014, 09:48 AM
  2. Sorting in Totals Query
    By Bertrand82 in forum Queries
    Replies: 1
    Last Post: 11-26-2012, 06:17 AM
  3. Sorting in Query
    By Rhubie in forum Access
    Replies: 11
    Last Post: 09-21-2012, 04:49 PM
  4. Replies: 11
    Last Post: 01-12-2012, 06:55 PM
  5. Sorting in a Query (Dates)
    By JohnS in forum Queries
    Replies: 2
    Last Post: 10-23-2009, 02:51 AM

Posting Permissions

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