Extract Max amount's reccord from each customer

[rizhaque]

Need SQL that could pull only one record per customer with the highest amount.

Here is the table:

Last First Gift Date MaxAmount
James kim 17-Dec-02 $90.00 This record
James kim 23-Jan-96 $10.00
James kim 12-Feb-97 $9.00
weeks alen 01-May-99 $33,000.00 This record
weeks alen 02-Feb-92 $30,000.00
weeks alen 31-Dec-91 $27,000.00
Finkelstine Gail 25-Apr-89 $16,000.00 This record
Finkelstine Gail 27-Dec-96 $13,000.00
Finkelstine Gail 01-Apr-91 $12,000.00

Thanks,

 

[Russell Hauf]

Try something like this (tbl072903 is the name of my table):

SELECT A.*, B.[Gift Date] FROM (Select Last, First, Max(Amount) AS MaxAmount
FROM tbl072903
GROUP BY Last, First) AS A, tbl072903 B
WHERE A.Last=B.Last AND A.First=B.First and A.MaxAmount=B.Amount;

HTH,

Russell

 

[rizhaque]

Will there be THREE tables containing identical records, as the following SQL call for tables "A", "B" and "tbl072903"?

Thanks,

 

[Russell Hauf]

No. A and B are just aliases (sp?). A is the table that would be made from (also called a table expression):

• (Select Last, First, Max(Amount) AS MaxAmount FROM tbl072903 GROUP BY Last, First)

and B is simply another name for tbl072903 - so I could do less typing. Is the query not working for you?

HTH,

Russell

 

[rizhaque]

I get "Type mismatch in expression" error when running the following query. The name of the table is Test.

SELECT A.*, B.[GiftDate] FROM (Select LastName, FirstName, Max(Amount) AS MaxAmount
FROM Test
GROUP BY LastName, FirstName) AS A, Test B
WHERE A.Last=B.Last AND A.First=B.First and A.MaxAmount=B.MaxAmount;

 

[Russell Hauf]

The last part should be:

• A.MaxAmount=B.Amount

instead of:

• A.MaxAmount=B.MaxAmount

HTH,

Russell

 

[Russell Hauf]

Actually, you need to also change Last and First. It should look something like this:

• SELECT A.*, B.[GiftDate] FROM (Select LastName, FirstName, Max(Amount) AS MaxAmount
  FROM Test
  GROUP BY LastName, FirstName) AS A, Test B
  WHERE A.LastName=B.LastName AND A.FirstName=B.FirstName and A.MaxAmount=B.Amount;

 

[rizhaque]

After making changes, here is SQL:

SELECT A.*, B.[GiftDate] FROM (Select LastName, FirstName, Max(Amount) AS MaxAmount
FROM Test
GROUP BY LastName, FirstName) AS A, Test B
WHERE A.LastName=B.LastName AND A.FirstName=B.FirstName and A.MaxAmount=B.Amount;

It prompts for "Enter PArameter Value" for Amount, and if I click OK than get another one for B.Amount...

 

[Russell Hauf]

What are the exact names of the fields in your table?

 

[rizhaque]

Test Table Contains four fields:

1. LastName (data type Text)
2. FirstName (data type Text)
3. GiftDate (data type Date/Time as Short Date MM/DD/YYYY
4. MaxAmount (data type Currency)