回覆: Select Max, return unwanted rows.
in [Queries]
|
From: Aldred on 28 Apr 2010 01:37 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. >>
|
Pages: 1 Prev: Union query not displaying select statement Next: 回覆: Select Max, return unwanted rows. |