I have a query I need to generate in which one of my tables contains
multiple records for an individual with the same values except for the
date the record was entered.  I want to retrieve the most recent date
and its associated balance for each individual to populate a grid in
D7.  

Here is the query I am currently using:

SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate)
FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID
                        INNER JOIN AcctBal a ON a.AcctID - am.AcctID
GROUP BY p.ID

Am I on the right track?  Thanks, in advance.

<KChief101@hotmail.com> wrote in message
newsisq2l9fftrc9bbtou9rkrn9grridd8q4@4ax.com...
>I have a query I need to generate in which one of my tables contains
> multiple records for an individual with the same values except for the
> date the record was entered.  I want to retrieve the most recent date
> and its associated balance for each individual to populate a grid in
> D7.
>
> Here is the query I am currently using:
>
> SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate)
> FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID
>                         INNER JOIN AcctBal a ON a.AcctID - am.AcctID
> GROUP BY p.ID
>
> Am I on the right track?  Thanks, in advance.

Which table is ARDate coming from?

Robert

On Wed, 17 Jan 2007 13:19:11 -0500, "Robert"
<ngsemail2005withoutthis@yahoo.com.ar> wrote:

>
><KChief101@hotmail.com> wrote in message
>newsisq2l9fftrc9bbtou9rkrn9grridd8q4@4ax.com...
>>I have a query I need to generate in which one of my tables contains
>> multiple records for an individual with the same values except for the
>> date the record was entered.  I want to retrieve the most recent date
>> and its associated balance for each individual to populate a grid in
>> D7.
>>
>> Here is the query I am currently using:
>>
>> SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate)
>> FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID
>>                         INNER JOIN AcctBal a ON a.AcctID - am.AcctID
>> GROUP BY p.ID
>>
>> Am I on the right track?  Thanks, in advance.
>
>Which table is ARDate coming from?
>
>Robert
>
Sorry.  It should be max(a.ARDate).

<KChief101@hotmail.com> wrote in message
news:ihjtq2d4vkf2nk8ligp92s83lr0jt34pco@4ax.com...
> On Wed, 17 Jan 2007 13:19:11 -0500, "Robert"
> <ngsemail2005withoutthis@yahoo.com.ar> wrote:
>
>>
>><KChief101@hotmail.com> wrote in message
>>newsisq2l9fftrc9bbtou9rkrn9grridd8q4@4ax.com...
>>>I have a query I need to generate in which one of my tables contains
>>> multiple records for an individual with the same values except for the
>>> date the record was entered.  I want to retrieve the most recent date
>>> and its associated balance for each individual to populate a grid in
>>> D7.
>>>
>>> Here is the query I am currently using:
>>>
>>> SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate)
>>> FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID
>>>                         INNER JOIN AcctBal a ON a.AcctID - am.AcctID
>>> GROUP BY p.ID
>>>
>>> Am I on the right track?  Thanks, in advance.
>>
>>Which table is ARDate coming from?
>>
>>Robert
>>
> Sorry.  It should be max(a.ARDate).

I think it is a very bad idea to select anything other than the aggregate
fields and the fields that you would use in a GROUP BY. Look at this query

State Name   Balance
AR   Robert  100
MA   John     200
AR   Jim       300

SELECT State, Name, sum(balance) from table group by state

Which name do you want for AR?

SELECT Max(ARDate) MDate,  AcctID MAcct into memory\temp from AcctBal group
by 2;

SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, m.Mdate
FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID
                           INNER JOIN AcctBal a ON a.AcctID = am.AcctID
                          INNER JOIN  memory\temp m ON m.MAcct = am.AcctID
where m.mdate = a.ARDate;

eliminates the ambiguity.

Robert

Agreed.

A couple of years back I asked a similar question and Tim
said that it was not *guaranteed* that the column values
were from the record with the MAX() value .. and in my case
there was no chance of duplicate MAX value records.

In my tests I got the correct values but I guess it depends
on the complexity of the query and data.

Malcolm