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

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

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

> Nate wrote:
> > 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,
>
> 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.
> .
>
From: Nate on 19 Apr 2010 16:31
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" wrote:

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

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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: disable clipboard warning
Next: Make field Invisible/Visable