Display all clients’ most recent referral

Topic

New Reply

Dear Windows Secrets MS Access Wizards,[INDENT]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!

[/INDENT]
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”.
28809-MaxOfReferrals

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.
28810-MainList

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.
28813-MaxOfReferralsByDateField.jpg

Thanks for your help.
Peter
:confused:

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Here you have a small database with a test table and the query as described above, working.

28821-TestDB

Reply | Quote

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”.

28825-MaxofReferralQuery

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

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The code works, I had checked it before and checked it again. I am attaching a new DB with it.

28866-TestWSL

Reply | Quote

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!

Reply | Quote

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.)

Reply | Quote

Great, you’re welcome :).

Reply | Quote