Select Max, return unwanted rows.

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.

hi Aldred,

On 26.04.2010 08:35, Aldred@office wrote:
Can some one please help in pointing me what's wrong?
Try this:

SELECT *
FROM tPart oP
INNER JOIN tCharge oC
ON oC.partNumID = P.ID
WHERE oC.ChargeDate =
(
SELECT Max(iC.ChargeDate)
FROM tCharge iC
INNER JOIN tPart iP
ON iP.ID = iC.partNumID
WHERE iP.ID = oP.ID
)




mfG
-- stefan --

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@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.

Hi Stefan,
Access complained that syntax error in join. Will that be the problem of
having no "AS"?

Let me try to put "AS" in and see if it works.

Thanks.

"Stefan Hoffmann" ¦b¶l¥ó
¤¤¼¶¼g...
hi Aldred,

On 26.04.2010 08:35, Aldred@office wrote:
Can some one please help in pointing me what's wrong?
Try this:

SELECT *
FROM tPart oP
INNER JOIN tCharge oC
ON oC.partNumID = P.ID
WHERE oC.ChargeDate =
(
SELECT Max(iC.ChargeDate)
FROM tCharge iC
INNER JOIN tPart iP
ON iP.ID = iC.partNumID
WHERE iP.ID = oP.ID
)




mfG
-- stefan --

Thanks. It works just what I want.

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

"KARL DEWEY" 在郵件
ä¸*撰寫...
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@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.