From: Aldred on
Thanks. It works just what I want.

I will need to study why this query works but mine doesn't.

"KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> 在郵件
news:95304482-1547-4E14-A867-B0A877AEAB31(a)microsoft.com 中撰寫...
> UNTESTED UNTESTED
> Try this --
> Select PartNumID, Charge, ChargeDate
> from tClient inner Join (tPart inner Join tCharge on tPart.ID =
> tCharge.PartNumID) on tClient.ID = tPart.ClientID
> Where ClientID = 9 AND DateValue(tCharge.ChargeDate) = (SELECT
> Max(DateValue([XX].ChargeDate)) FROM tCharge AS [XX] WHERE [XX].PartNumID
> =
> tPart.ID)
> ORDER BY PartNumID, Charge, ChargeDate;
>
> --
> Build a little, test a little.
>
>
> "Aldred(a)office" wrote:
>
>> Hi all,
>> I have searched this forum with keyword max, but those solutions doesn't
>> seem to work on my problem. Can some one please help in take a look?
>>
>> I have 3 tables look like this:
>>
>> tClient
>> ID, other fields...
>>
>> tPart
>> ID, ClientID, PartNum, other fields...
>>
>> tCharge
>> ID, partNumID, Charge, ChargeDate(This is a Date/time field), other
>> fields...
>>
>> I have a query looks like this:
>>
>> Select PartNumID, Charge, max(ChargeDate)
>> from tClient inner Join (tPart inner Join tCharge on tPart.ID =
>> tCharge.PartNumID) on tClient.ID = tPart.ClientID
>> Where ClientID = 9
>> Group by PartNumID, Charge
>>
>> It returns something liks this
>> PartNumID Charge Expr1002
>> 382 HK$0.33 26/5/2009 13:30:30
>> 382 HK$0.38 1/4/2010 11:16:56
>> 383 HK$0.39 26/5/2009 13:31:11
>> 383 HK$0.45 1/4/2010 11:31:17
>> .
>> .
>> .
>>
>> But obviously, I'd like to have the query return something like this:
>> PartNumID Charge Expr1002
>> 382 HK$0.38 1/4/2010 11:16:56
>> 383 HK$0.45 1/4/2010 11:31:17
>>
>> I tried something like this and it returns nothing:
>> Select PartNumID, Charge, ChargeDate from tClient inner Join (tPart inner
>> Join tCharge on tPart.ID = tCharge.PartNumID) on tClient.ID =
>> tPart.ClientID
>> Where ChargeDate = (Select Max(ChargeDate) from tPart inner Join tCharge
>> on
>> tPart.ID = tCharge.PartNumID) and ClientID = 9
>> Order by PartNUmID
>>
>> Looks like the query above will return only the max date from the table
>> tcharge and it is not linked with ClientID 9 so it returns nothing.
>>
>> Can some one please help in pointing me what's wrong?
>>
>> Thank you so much in advance.
>>