Prev: disable clipboard warning
Next: Make field Invisible/Visable
From: Nate on 20 Apr 2010 11:51
That worked perfectly. Really appreciate it.

"Hans Up" wrote:

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