Trying to get just one row returned for each client

[Jackxxx]

I'm trying to get the largest payment amount and a couple of other columns the statement below will work, but if a client made multiple payments on the same max date it returns all of them and I only want one row per client.
Can anyone suggest what might do this?

SELECT A.clientID, A.AmountPaid, Max(A.PayDate) AS LastMaxPayDateFROM [My table] A INNER JOIN (SELECT clientID, Max(AmountPaid) AS Largest FROM [My table] GROUP BY clientID) B ON A.clientID = B.clientID AND A.AmountPaid = B.LargestGROUP BY A.clientID, A.AmountPaid

 

[SkipVought]

Hi,

What seems to be the problem? Please describe the adverse results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

How about this?

SELECT A.clientID, A.AmountPaid, Max(A.PayDate) AS LastMaxPayDate FROM [My table] A INNER JOIN (SELECT [b][COLOR=red]DISTINCT[/color][/b] clientID, Max(AmountPaid) AS Largest FROM [My table] GROUP BY clientID) B ON A.clientID = B.clientID AND A.AmountPaid = B.LargestGROUP BY A.clientID, A.AmountPaid

Randy

 

[SkipVought]

I was going to submit that you have missing SPACES between CLAUSES...

SELECT
  A.clientID
, A.AmountPaid
, Max(A.PayDate) AS LastMaxPayDate
FROM [My table] A INNER JOIN 
(
SELECT
  clientID
, Max(AmountPaid) AS Largest 
FROM [My table] 
GROUP BY
  clientID
) B
  ON A.clientID = B.clientID
 AND A.AmountPaid = B.Largest
GROUP BY
  A.clientID
, A.AmountPaid

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jackxxx]

So here is my latest try. What happens with it is, if a client has to highest payment amounts the same, they were made on different dates, and they were made to different project types, I get both rows returned. I just want one row for each client and it should be the following.

The highest payment, paydate, and projecttype, but only one even if there are ties.

SELECT DISTINCTROW A.ClientID, A.[AmountPaid], Max(A.PayDate) AS LastMaxPayDate, A.[ProjectType]
FROM tblPayments AS A INNER JOIN (SELECT DISTINCTROW ClientID, Max([AmountPaid]) AS Largest FROM tblPayments GROUP BY ClientID) AS B ON (A.[AmountPaid] = B.Largest) AND (A.ClientID = B.ClientID)
GROUP BY A.ClientID, A.[AmountPaid], A.[ProjectType];

 

[SkipVought]

Please post some sample source data that illustrates this issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

What is the primary key field in tblPayments?
I would probably use something like:

SELECT *
FROM tblPayments P1
WHERE PkFld = 
 (SELECT TOP 1 PkFld
  FROM tblPayments P2 
  WHERE ...[you fill in the rest]...
  ORDER BY AmountPaid DESC, PayDate, projecttype )

Duane
Hook'D on Access
MS Access MVP

 

[Jackxxx]

Basically it would look like this:

555 and 777 show two rows, I only want to return one row, preferably the latest one, but either would be great.

555 | $1000 | 2/13/2008 | General
555 | $1000 | 5/17/2009 | Public
777 | $500 | 5/23/2009 | General
777 | $500 | 7/1/2008 | Public
333 | $2000 | 7/12/2009 | General
222 | $200 | 3/12/2009 | General

 

[SkipVought]

SELECT 
  A.ClientID
, A.[AmountPaid]
, A.PayDate
, A.[ProjectType]

FROM [My table] 
 A
, (
SELECT 
  ClientID
, Max([AmountPaid]) AS Largest 
, MAX(PayDate) AS PD

FROM [My table] 

GROUP BY
  ClientID
) AS B

Where (A.[AmountPaid] = B.Largest)
  AND (A.ClientID = B.ClientID)
  AND (A.PayDate  =B.PD)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jackxxx]

I tried this, but it only returns 1 row, I need 1 row for each client. What am I missing?

SELECT A.ClientID, A.[AmountPaid], A.[PayDate] AS LastMaxPayDate, A.[ProjectType]
FROM tblPaymentss AS A WHERE ClientID = (SELECT TOP 1 ClientID FROM tblPayments ORDER BY [AmountPaid] DESC, [PayDate], [ProjectType])
GROUP BY A.ClientID, A.[AmountPaid], A.[PayDate], A.[ProjectType];

 

[SkipVought]

SELECT TOP 1 ClientID

ONE CLIENT!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Jackxxx,
You need to add the where condition in the SELECT TOP 1 to match the ClientIDs. I also don't understand why you are using any GROUP BY.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Also, is ClientID the primary key in the tblPayments[red]s[/red]? I would doubt it is unless you only get one payment per client.

Duane
Hook'D on Access
MS Access MVP