Carl,
You are probably going to have to use at least two queries. Check out this
Thread 343676 for some insight.
HTH.
>Hi Everyone,
> Lets assume for a moment that you have a table with: key (10 digit number), acctount_type (ATM, CHECKING, SAVINGS ECT.), and months (Number of months the account has been open). We want to write an SQL statement that will give us results that look like this:
>
>KEY,
>CNT-KEY(number of records with that same key),
>cnt-account_Type(number of unique account _types within the groups of same keys)
>MAX(month),
>accout_type (that goes with MAX(months)),
>MIN(Month),
>account_type (that goes with the minium month record)
>
>The code below does almost what we want but we can not get it to show the account_type for the min and max months records on the same row. Please help if you can.
>
>(The table name is SHAY and the account_type field is called ptype)
>SELECT Shay.key, COUNT(*), COUNT(distinct ptype), max(months), min(months);
> FROM shay;
> GROUP BY Shay.key
>Thanks in adavance :)
>
>Carl Burkhart
>carl.burkhart@brannrmg-va.com
>BrannData
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao