How to identify if a value in a column is found more than once?

[MsAxes]

Hi all

I have a query that first groups the customers different account types and sums up the balances (simple). The subquery below returns the max balance for a customer for that specific product they have. I just realized that 4 of the 10k records this runs for have the same balance therefore it returns more than one ssn with the same balance (this causes a duplication in another query).

SELECT Table1.*FROM Table1 INNER JOIN (SELECT SSN, Max(Sum_of_Bal) AS Max_Bal FROM Table1 GROUP BY SSN)  AS Table2 ON (Table1.Sum_of_Bal = Table2.Max_Bal) AND (Table1.SSN = Table2.SSN);

How can I add a column that tells me if that ssn is returned more than once?

[ranman256]

something like this:

public function getCountOfCustID(pvID)
getCountOfCustID=Dcount("*", "qsMyQuery","[CustID]=" & pvID )
end function

[MsAxes]

I'll give that a try - what is pvID?

[MsAxes]

I couldn't get the function to work (not sure what pvID is) so I just created a third (I hate to have this many) subquery where I inner join the SQL above to get the count by SSN and it worked.