Results 1 to 15 of 15
  1. #1
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    62
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Question Display all clients' most recent referral

    Dear Windows Secrets MS Access Wizards,
    Side note:
    When posting your reply, as I am not an expert at MS Access, please present your answers with MORE detail than you think is neccesary, with numbered steps if appropriate - thanks!
    I am running Access 2003. My organization has hundreds of clients who are referred to us. I want to have a list which displays all those clients. However, each client has multiple referrals, and I only want to list to show the most recent one.

    To solve this problem, the previous Database Admin created a query called "MaxofReferrals".
    MaxOfReferrals.jpg

    The clients (called "PCs", not short for Personal Computers) are set to "Group By" on the Totals row and the ReferralID's are set to "Max" in the total row.

    Then I created a second query called "Main List" which displays the ID, name, social security #, status, etc. which is linked to the MaxReferral table. Running that query generates a list that shows each person once. I have it set you can click on the name, and it goes into the full record of the client.
    MainList.JPG

    Here is the problem:
    Some of the ReferralID's for newly keyed referrals into the database were assigned autonumbers that are lower than the older referral number for a client. So when the list displays, the older referral record is showing up in the list, not the newely keyed referral.

    How can I fix this? I am thinking I need to change the first "MaxofReferrals" query so it sorts on the "DateReferralReceived" field. I tried this with no success, as setting the Totals row to "Max" for a date field didn't work.
    MaxOfReferralsByDateField.jpg.png

    Thanks for your help.
    Peter
    Last edited by Techie; 2011-08-31 at 16:57. Reason: Fixed a screenshot
    Peter
    Support for a large nonprofit
    Projects

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can use Max for Date fields, and that is what I was going to suggest.

    When you add that field to the Group By query need to remove the ReferralID , so that you only Group By the Client.
    Regards
    John



  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    One way to solve it. Create a new query, do not add any tables to it and choose SQL View for that query. That will lead you to a window where you can paste the SQL for that query. Paste this SQL there:
    Code:
    SELECT DISTINCT PClistID, (SELECT TOP 1 ReferralID FROM Tbl_Referral T2 WHERE T2.PClistID = T1.PClistID ORDER BY DateReferralReceived DESC) As ReferralID
    FROM Tbl_Referral T1
    Save the query and use it as a replacement for the MaxOfReferrals query.

    I am expecting it to work without any issues, but there may be some mistake on table or column names. If so, please post saying so.

  4. #4
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    62
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by johnhutchison View Post
    You can use Max for Date fields, and that is what I was going to suggest.

    When you add that field to the Group By query need to remove the ReferralID , so that you only Group By the Client.
    If you notice in the second image, the queries TBL_Referral and MaxofReferral are linked through the "ReferralID" field, so how can I remove it?
    Peter
    Support for a large nonprofit
    Projects

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Here you have a small database with a test table and the query as described above, working.

    TestDB.zip
    Last edited by ruirib; 2011-09-01 at 12:25.

  6. The Following User Says Thank You to ruirib For This Useful Post:

    Techie (2011-09-01)

  7. #6
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    62
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ruirib View Post
    Here you have a small database with a test table and the query as described above, working.
    First I fixed the field name to T1.[Date XXX Recd Referral], and then it ran with this error: (note that my org name is X'ed out)

    "At most one record can be returned by this subquery".

    MaxofReferralQuery.PNG

    Here is an example of what the final window looks like (not the embedded MaxofReferral query):
    MainList2.PNG

    Note that the problem is that the "Ref. Status" (Referral Status) is not picking the most recent referral for all the people the list, which is why I am trying to sort the underlying query of this list with a query not based on the ReferralID by the "Date XXX Recd Referral".

    Thanks.
    Peter
    Support for a large nonprofit
    Projects

  8. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The T1 prefix was not needed, but adding it causes no problem either.
    That error message seems to suggest that you have more than one referral for the same client done at the same date. What criteria should be used to untie such cases?

  9. #8
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    62
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ruirib View Post
    The T1 prefix was not needed, but adding it causes no problem either.
    That error message seems to suggest that you have more than one referral for the same client done at the same date. What criteria should be used to untie such cases?
    Thanks for translating the error, I was scratching my head. The case you mentioned only would happen if the referral was coded twice, by accident. I don't have any way of determining which one is correct in such cases.
    Is there a way for Access to just pick the first one it finds, in the case of such duplicates?

    P.S. Did you see any of the tourists in town for World Youth Day? I know some people that traveled to Spain and Portugal for the event in Madrid.
    Peter
    Support for a large nonprofit
    Projects

  10. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It's easy to solve the problem, you just need to add another field to the ORDER BY clause in the query.

    SELECT DISTINCT PClistID, (SELECT TOP 1 ReferralID FROM Tbl_Referral T2 WHERE T2.PClistID = T1.PClistID ORDER BY DateReferralReceived DESC, ReferralID DESC) As ReferralID
    FROM Tbl_Referral T1

    Is this enough or do you want me to add it to the query and post the database again?


    I am rather far from Madrid, so no tourists for that event, here.

  11. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Techie View Post
    If you notice in the second image, the queries TBL_Referral and MaxofReferral are linked through the "ReferralID" field, so how can I remove it?
    I see that you have a solution using the approach suggested by ruirib. I have been away for a few days.

    You cannot group by ReferralId and use Max of Ref Date as the ReferralIDs are all distinct. Once you group by them, you just get all the records returned.
    Once you remove the ReferralID, you need to join on ClientId and Ref Date instead.
    Regards
    John



  12. The Following User Says Thank You to johnhutchison For This Useful Post:

    Techie (2011-09-06)

  13. #11
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    62
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ruirib View Post
    It's easy to solve the problem, you just need to add another field to the ORDER BY clause in the query.

    SELECT DISTINCT PClistID, (SELECT TOP 1 ReferralID FROM Tbl_Referral T2 WHERE T2.PClistID = T1.PClistID ORDER BY DateReferralReceived DESC, ReferralID DESC) As ReferralID
    FROM Tbl_Referral T1
    I got a syntax error in the part included within the parenthesis when copying your replacement code into your old "Test.mdb" file. Can you please double check your code?

    Thanks,
    Peter
    Peter
    Support for a large nonprofit
    Projects

  14. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The code works, I had checked it before and checked it again. I am attaching a new DB with it.

    TestWSL.zip

  15. The Following User Says Thank You to ruirib For This Useful Post:

    Techie (2011-09-08)

  16. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Is there a possibility of a client having two referrals on the same date?
    If that can happen, then joining on client and date will result in two records for the one client.

    (Just yesterday I found this was happening in two separate databases, and users were confused by the duplicates appearing in the list.)
    Regards
    John



  17. #14
    Star Lounger Techie's Avatar
    Join Date
    Dec 2009
    Location
    Philadelphia, PA, USA
    Posts
    62
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ruirib View Post
    The code works, I had checked it before and checked it again. I am attaching a new DB with it.
    I found the problem - I copied and pasted the code from the email auto-notification I got off this thread. For some reason the system added a star after the two word you put in bold. Adding code tags, like you did on your first post, can prevent this issue in the future. Just stating the obvious...

    Thanks again for your help, I got it to work!
    Peter
    Support for a large nonprofit
    Projects

  18. #15
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Great, you're welcome .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •