Query to Pull Most Recent Call with a Callback Date Scheduled

N

Nate

Hello,

I'm trying to create a query that only pulls in patients that have a
scheduled callback date, but only if the callback date was scheduled on there
most recent call. So far I've been unsuccessful. Below is the SQL that I'm
attempting to use-

SELECT [Patients Table].[Patient ID], [Patients Table].[First Name],
[Patients Table].[Last Name], [Patients Table].[Home Phone], [Patients
Table].[Day Phone], Max([Call]) AS Expr1, [Calls Table].[Call Outcome],
[Calls Table].[Callback Date]
FROM [Patients Table] INNER JOIN [Calls Table] ON [Patients Table].[Patient
ID] = [Calls Table].[Patient ID]
WHERE ((([Calls Table].[Callback Date]) Is Not Null))
GROUP BY [Patients Table].[Patient ID], [Patients Table].[First Name],
[Patients Table].[Last Name], [Patients Table].[Home Phone], [Patients
Table].[Day Phone], [Calls Table].[Call Outcome], [Calls Table].[Callback
Date]
ORDER BY [Calls Table].[Callback Date];
 
N

Nate

Hans, thanks for the quick response. I got the following error message when
I tried that "You tried to execute a query that does not include the
specified expression 'Patient ID' as part of an aggregate function." Sorry -
I'm not familiar with SQL. Thanks,

Hans Up said:
Nate said:
I'm trying to create a query that only pulls in patients that have a
scheduled callback date, but only if the callback date was scheduled on there
most recent call. So far I've been unsuccessful. Below is the SQL that I'm
attempting to use-
Click to expand...

See if this query returns the latest Call for each patient which had a
Callback Date scheduled.

SELECT
p.[Patient ID],
p.[First Name],
p.[Last Name],
p.[Home Phone],
p.[Day Phone],
c.MaxOfCall
FROM
[Patients Table] AS p
INNER JOIN (
SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
[Calls Table]
WHERE
[Callback Date] Is Not Null
GROUP BY
p.[Patient ID]
) AS c
ON p.[Patient ID] = c.[Patient ID];

If it works, save it as something like qryLastCall. Then you can create
another query which uses qryLastCall and [Calls Table] as its data
sources. INNER JOIN the two on Patient Id and qryLastCall.MaxOfCall =
[Calls Table].Call You can select all the fields from qryLastCall and
also the [Call Outcome] and [Callback Date] fields from [Calls Table].
.
Click to expand...
 
N

Nate

Thanks Hans - that worked to pull in the last date of any scheduled callback,
but I wasn't clear about what I wanted this to return. If the patient didn't
have a callback scheduled on there most recent call, I don't want it to pull
in anything for that patient. I only want the patients that have a callback
date scheduled, if it was scheduled on the most recent call. Sorry about
that.

Hans Up said:
Hans said:
Nate said:
I'm trying to create a query that only pulls in patients that have a
scheduled callback date, but only if the callback date was scheduled
on there most recent call. So far I've been unsuccessful. Below is
the SQL that I'm attempting to use-
Click to expand...

See if this query returns the latest Call for each patient which had a
Callback Date scheduled.

SELECT
p.[Patient ID],
p.[First Name],
p.[Last Name],
p.[Home Phone],
p.[Day Phone],
c.MaxOfCall
FROM
[Patients Table] AS p
INNER JOIN (
SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
[Calls Table]
WHERE
[Callback Date] Is Not Null
GROUP BY
p.[Patient ID]
) AS c
ON p.[Patient ID] = c.[Patient ID];

If it works, save it as something like qryLastCall. Then you can create
another query which uses qryLastCall and [Calls Table] as its data
sources. INNER JOIN the two on Patient Id and qryLastCall.MaxOfCall =
[Calls Table].Call You can select all the fields from qryLastCall and
also the [Call Outcome] and [Callback Date] fields from [Calls Table].
Click to expand...

Oops, I left an extra p in there. Change the GROUP BY to:

GROUP BY
[Patient ID]
.
Click to expand...
 
H

Hans Up

Nate said:
Hans, thanks for the quick response. I got the following error message when
I tried that "You tried to execute a query that does not include the
specified expression 'Patient ID' as part of an aggregate function." Sorry -
I'm not familiar with SQL. Thanks,
Click to expand...

Did you see my second mention about the extra p? Change the GROUP BY to
this:
GROUP BY
[Patient ID]

If that doesn't fix it up, try breaking out the sub select and running
it separately.

SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
INNER JOIN [Calls Table]
WHERE
[Callback Date] Is Not Null
GROUP BY
[Patient ID]

If that part doesn't work correctly, the rest is screwed for sure.
 
H

Hans Up

Nate said:
I'm trying to create a query that only pulls in patients that have a
scheduled callback date, but only if the callback date was scheduled on there
most recent call. So far I've been unsuccessful. Below is the SQL that I'm
attempting to use-
Click to expand...

See if this query returns the latest Call for each patient which had a
Callback Date scheduled.

SELECT
p.[Patient ID],
p.[First Name],
p.[Last Name],
p.[Home Phone],
p.[Day Phone],
c.MaxOfCall
FROM
[Patients Table] AS p
INNER JOIN (
SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
[Calls Table]
WHERE
[Callback Date] Is Not Null
GROUP BY
p.[Patient ID]
) AS c
ON p.[Patient ID] = c.[Patient ID];

If it works, save it as something like qryLastCall. Then you can create
another query which uses qryLastCall and [Calls Table] as its data
sources. INNER JOIN the two on Patient Id and qryLastCall.MaxOfCall =
[Calls Table].Call You can select all the fields from qryLastCall and
also the [Call Outcome] and [Callback Date] fields from [Calls Table].
 
H

Hans Up

Nate said:
Thanks Hans - that worked to pull in the last date of any scheduled callback,
but I wasn't clear about what I wanted this to return. If the patient didn't
have a callback scheduled on there most recent call, I don't want it to pull
in anything for that patient. I only want the patients that have a callback
date scheduled, if it was scheduled on the most recent call. Sorry about
that.
Click to expand...

In that case, try removing the WHERE clause from the sub select ... then
the whole thing should give you the latest call for each patient
regardless of whether or not a callback was scheduled. After you INNER
JOIN the revised qryLastCall to [Calls Table], you can apply the
"[Callback Date] Is Not Null" criteria on the whole result set.

At least I think that should work. But that's all I got time for right
now. If you run into trouble, post back with your latest query SQL and
describe how it's not correct. I'll try to check back tonight.
 
H

Hans Up

Hans said:
Nate said:
I'm trying to create a query that only pulls in patients that have a
scheduled callback date, but only if the callback date was scheduled
on there most recent call. So far I've been unsuccessful. Below is
the SQL that I'm attempting to use-
Click to expand...

See if this query returns the latest Call for each patient which had a
Callback Date scheduled.

SELECT
p.[Patient ID],
p.[First Name],
p.[Last Name],
p.[Home Phone],
p.[Day Phone],
c.MaxOfCall
FROM
[Patients Table] AS p
INNER JOIN (
SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
[Calls Table]
WHERE
[Callback Date] Is Not Null
GROUP BY
p.[Patient ID]
) AS c
ON p.[Patient ID] = c.[Patient ID];

If it works, save it as something like qryLastCall. Then you can create
another query which uses qryLastCall and [Calls Table] as its data
sources. INNER JOIN the two on Patient Id and qryLastCall.MaxOfCall =
[Calls Table].Call You can select all the fields from qryLastCall and
also the [Call Outcome] and [Callback Date] fields from [Calls Table].
Click to expand...

Oops, I left an extra p in there. Change the GROUP BY to:

GROUP BY
[Patient ID]
 
N

Nate

Hans - thanks that did fix it but I don't think I was clear about what I
wanted the query to return. This query returns the most recent call with a
callback date scheduled. I only want the query to pull in the call if the
most recent call had a callback date scheduled. Not all calls have a
callback date, so if the most recent call did not have a callback date
scheduled it shouldn't pull into the query. Sorry abou that. Thanks again.

Hans Up said:
Nate said:
Hans, thanks for the quick response. I got the following error message when
I tried that "You tried to execute a query that does not include the
specified expression 'Patient ID' as part of an aggregate function." Sorry -
I'm not familiar with SQL. Thanks,
Click to expand...

Did you see my second mention about the extra p? Change the GROUP BY to
this:
GROUP BY
[Patient ID]

If that doesn't fix it up, try breaking out the sub select and running
it separately.

SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
INNER JOIN [Calls Table]
WHERE
[Callback Date] Is Not Null
GROUP BY
[Patient ID]

If that part doesn't work correctly, the rest is screwed for sure.
.
Click to expand...
 
N

Nate

I removed the WHERE clause and was able to pull in only the last call date
for each patient, but when I tried to do an INNER JOIN on the Patient ID it
wasn't pulling in correctly. It looks like it is pulling in multiple calls
for some patients after I do the join. I'm thinking that I should be
matching the Call ID's instead of the Patient ID's, but when I try to add the
Call ID to the query that you created it stops pulling in only the last call
and pulls in every call. The Call ID is only located in the Calls Table.

Hans Up said:
Nate said:
Thanks Hans - that worked to pull in the last date of any scheduled callback,
but I wasn't clear about what I wanted this to return. If the patient didn't
have a callback scheduled on there most recent call, I don't want it to pull
in anything for that patient. I only want the patients that have a callback
date scheduled, if it was scheduled on the most recent call. Sorry about
that.
Click to expand...

In that case, try removing the WHERE clause from the sub select ... then
the whole thing should give you the latest call for each patient
regardless of whether or not a callback was scheduled. After you INNER
JOIN the revised qryLastCall to [Calls Table], you can apply the
"[Callback Date] Is Not Null" criteria on the whole result set.

At least I think that should work. But that's all I got time for right
now. If you run into trouble, post back with your latest query SQL and
describe how it's not correct. I'll try to check back tonight.
.
Click to expand...
 
H

Hans Up

Nate said:
I removed the WHERE clause and was able to pull in only the last call date
for each patient, but when I tried to do an INNER JOIN on the Patient ID it
wasn't pulling in correctly. It looks like it is pulling in multiple calls
for some patients after I do the join.
Click to expand...

No. I told you to INNER JOIN on Patient Id AND qryLastCall.MaxOfCall =
[Calls Table].Call

If your JOIN is based only on Patient ID, I suspect it's pulling every
row from [Calls Table] which matches the Patient.

My understanding is your qryLastCall SQL should now look like this (we
removed the Not Null criterion on [Callback Date]):

SELECT
p.[Patient ID],
p.[First Name],
p.[Last Name],
p.[Home Phone],
p.[Day Phone],
c.MaxOfCall
FROM
[Patients Table] AS p
INNER JOIN (
SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
[Calls Table]
GROUP BY
[Patient ID]
) AS c
ON p.[Patient ID] = c.[Patient ID];

My hope is that version of qryLastCall will return no more than one row
for each patient. If so, create another query like this:

SELECT
q.[Patient ID],
q.[First Name],
q.[Last Name],
q.[Home Phone],
q.[Day Phone],
q.MaxOfCall,
c.[Call Outcome],
c.[Callback Date]
FROM
qryLastCall AS q
INNER JOIN [Calls Table] AS c
ON q.[Patient ID] = c.[Patient ID]
AND q.MaxOfCall = c.Call
WHERE
c.[Callback Date] Is Not Null;

Notice the 2 conditions I mentioned for the JOIN. It should only return
call records for each patient where Call value matches MaxOfCall for
that patient. The WHERE clause excludes any patient whose last Call
didn't have a [Callback Date] value.
 
N

Nate

That worked perfectly. Really appreciate it.

Hans Up said:
Nate said:
I removed the WHERE clause and was able to pull in only the last call date
for each patient, but when I tried to do an INNER JOIN on the Patient ID it
wasn't pulling in correctly. It looks like it is pulling in multiple calls
for some patients after I do the join.
Click to expand...

No. I told you to INNER JOIN on Patient Id AND qryLastCall.MaxOfCall =
[Calls Table].Call

If your JOIN is based only on Patient ID, I suspect it's pulling every
row from [Calls Table] which matches the Patient.

My understanding is your qryLastCall SQL should now look like this (we
removed the Not Null criterion on [Callback Date]):

SELECT
p.[Patient ID],
p.[First Name],
p.[Last Name],
p.[Home Phone],
p.[Day Phone],
c.MaxOfCall
FROM
[Patients Table] AS p
INNER JOIN (
SELECT
[Patient ID],
Max([Call]) AS MaxOfCall
FROM
[Calls Table]
GROUP BY
[Patient ID]
) AS c
ON p.[Patient ID] = c.[Patient ID];

My hope is that version of qryLastCall will return no more than one row
for each patient. If so, create another query like this:

SELECT
q.[Patient ID],
q.[First Name],
q.[Last Name],
q.[Home Phone],
q.[Day Phone],
q.MaxOfCall,
c.[Call Outcome],
c.[Callback Date]
FROM
qryLastCall AS q
INNER JOIN [Calls Table] AS c
ON q.[Patient ID] = c.[Patient ID]
AND q.MaxOfCall = c.Call
WHERE
c.[Callback Date] Is Not Null;

Notice the 2 conditions I mentioned for the JOIN. It should only return
call records for each patient where Call value matches MaxOfCall for
that patient. The WHERE clause excludes any patient whose last Call
didn't have a [Callback Date] value.
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Upsizing Problem with Left Join Queries 19
Help with opening form from another form using a query 1
Most recent date query 2
Need query to return fields with most recent date 3
1 form, 2 tables 3
"Data Has Been Changed" error with MSSQL 2005 backend using left j 1
how to pull most recent date from a linked table 2
Access 2007-Table not displaying the values from a combo box 2

Top