Query the most recent entry for each record in a separate table

[diberlee]

Hi,

I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.

• Table tClients stores the current clients - primary key is named "clientRef"
• Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
• When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
• There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
• Once the completed flag is set that client will be ignored so no further entries will appear.

So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.

Any help would be gratefully received as this is one of the few things left on my to-do list for this project

Cheers
Duane

 

[Isskint]

I do not have access to Access :banghead: at the moment, however the SQL of the query would be something like;

SELECT tClients.ClientRef FROM tClients LEFT JOIN tContactEvents ON tClients.ClientRef = tContactEvents.ClientRef WHERE ((Date(tContactEvents.dateTime) = Date()) AND ((tContactEvents.Completed = FALSE) OR (Dcount("ClientRef","tContactEvents","ClientRef=" & tClients.ClientRef) =0)));

You want both tables available to the query (joined on ClientRef - should be a relationship already i hope) and the criteria is (Date of dateTime = today) and either (the completed flag = FALSE or count of ClientRef in tContactEvents = 0)

 

[billmeye]

The part about returning only clientRef where not Completed or not even attempted makes sense, but I was confused how the current date plays a part, maybe you could better explain.

SELECT tClients.ClientRef
FROM tClients
WHERE (((DCount("*","tContactEvents","ClientRef=" & [tClients].[ClientRef] & " And [completed]=-1"))=0)) OR (((DCount("ClientRef","tContactEvents","ClientRef=" & [tClients].[ClientRef]))=0));

 

[diberlee]

The part about returning only clientRef where not Completed or not even attempted makes sense, but I was confused how the current date plays a part, maybe you could better explain.

SELECT tClients.ClientRef
FROM tClients
WHERE (((DCount("*","tContactEvents","ClientRef=" & [tClients].[ClientRef] & " And [completed]=-1"))=0)) OR (((DCount("ClientRef","tContactEvents","ClientRef=" & [tClients].[ClientRef]))=0));

Hi Bill,

The date is relevant as we sometimes have to rebook a client. tContactEvents will contain entries for a rolling 30 day period so we can use it to generate reports on success rates etc. I am considering making a separate table for these historic entries, but so far it hasn't been needed. Maybe you have some input on whether it's best practice to separate historic data from the set we're actively working on? All my SQL queries that govern which client we call next narrows results down to just the current day, but now that I think about it I guess the whole table still has to be scanned to find the relevant entries, and performance will be improved with a smaller pool of data?

As it is right now, if we spoke to somebody on the 17th and it wasn't convenient for them to talk we would mark them as completed in tContactEvents to avoid any further attempts on the day. If we then rebooked them to the 20th and didn't narrow the search results to today only the query would see the entry from the 17th for that client that is marked as completed and count them as being dealt with already.

Thanks both of you for your input, I'll have a tinker now and see if I can get it working

Cheers
Duane

 

[diberlee]

IsSkint's code won't come out of SQL view. It says "Wrong number of arguments used with function in criteria expression"

Bill's code gives a "data type mismatch in criteria expression" error and I need to click OK a bunch of times to get to an empty table.

It's encouraging that you both use similar solutions though, so I'll do some reading on DCount and LEFT JOIN which I've not encountered so far - fairly new to anything beyond the basics in Access. I'll check back here in a bit.

Thanks guys

 

[MSAccessRookie]

IsSkint's code won't come out of SQL view. It says "Wrong number of arguments used with function in criteria expression"

Bill's code gives a "data type mismatch in criteria expression" error and I need to click OK a bunch of times to get to an empty table.

It's encouraging that you both use similar solutions though, so I'll do some reading on DCount and LEFT JOIN which I've not encountered so far - fairly new to anything beyond the basics in Access. I'll check back here in a bit.

Thanks guys

You should double-check the data types of your Fields, since "data type mismatch in criteria expression" most often means that there is an instance of differing datatypes in a comparison or a Concatenation. From what I see, an obvious choice to consider would be whether tClients].[ClientRef] is a string. If it is, then the Dcounts will need to include Quotes around the value.

DCount("*","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "' And [completed]=-1")=0'

DCount("ClientRef","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "'")=0;
​

Note that there are pros and Cons to using "*" in the first DCount. As long as ClientRef is a required Field, then counting it is another option that could produce the same results a little faster.

-- Rookie

 

[diberlee]

Thanks, clientRef is a string and adding quotes prevented the errors although the query still doesn't return any results.

clientRef is also a required field, so I will leave the * until I understand what is going on a little better, but will bear what you said in mind for future reference.

Cheers

 

[billmeye]

Thanks 'Rookie' for answering. I did not know what the data types where, just assumed ID was a number.

If you are only concerned with the current date, then change the SQL to:

SELECT tClients.ClientRef
FROM tClients
WHERE (((DCount("*","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "' And [completed]=-1 And DateValue([dateTime])=date()"))=0)) OR (((DCount("ClientRef","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "'  And DateValue([dateTime])=date()"))=0));

 

[diberlee]

Thanks 'Rookie' for answering. I did not know what the data types where, just assumed ID was a number.

If you are only concerned with the current date, then change the SQL to:

SELECT tClients.ClientRef
FROM tClients
WHERE (((DCount("*","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "' And [completed]=-1 And DateValue([dateTime])=date()"))=0)) OR (((DCount("ClientRef","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "'  And DateValue([dateTime])=date()"))=0));

That's a fair assumption, I should have been clearer. I've always avoided using number format unless there will be calculations performed on that information. I've never seen the DateValue function, it would have saved me a lot of headaches over the past couple of weeks if I had

This is working perfectly, thanks a lot for taking the time to help.

 

[billmeye]

OK Duane, glad I could help. Good Luck with the rest of your project.

 

[MSAccessRookie]

Thanks 'Rookie' for answering. I did not know what the data types where, just assumed ID was a number.

If you are only concerned with the current date, then change the SQL to:

SELECT tClients.ClientRef
FROM tClients
WHERE (((DCount("*","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "' And [completed]=-1 And DateValue([dateTime])=date()"))=0)) OR (((DCount("ClientRef","tContactEvents","ClientRef='" & [tClients].[ClientRef] & "'  And DateValue([dateTime])=date()"))=0));

Have you checked the Types and Values in the Field [dateTime]? The DateValue will Return what you expect if the Type is String or the Type is some form of Date, but if the Value is Null, it might not return what you expect. If a Null value is the issue, then consider using Nz() and defining a default value for the case of a Null.

-- Rookie