Prev: disable clipboard warning
Next: Make field Invisible/Visable
From: Nate on 19 Apr 2010 12:02
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];
From: Hans Up on 19 Apr 2010 14:22
Nate wrote:
> 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-

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].
From: Hans Up on 19 Apr 2010 14:33
Hans Up wrote:
> Nate wrote:
>> 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-
>
> 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].

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

GROUP BY
[Patient ID]
From: Nate on 19 Apr 2010 13:49
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" wrote:

> Nate wrote:
> > 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-
>
> 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].
> .
>
From: Nate on 19 Apr 2010 14:02
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" wrote:

> Hans Up wrote:
> > Nate wrote:
> >> 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-
> >
> > 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].
>
> Oops, I left an extra p in there. Change the GROUP BY to:
>
> GROUP BY
> [Patient ID]
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: disable clipboard warning
Next: Make field Invisible/Visable