I am working with SQL 8.00. I have the following tables
TABLE ClientInfo
CheckNum Account Name Addr1 City State Zip
---------------------------------------------------------------------
12345 11111 John 123 Mary St Miami FL 33139
54321 22222 Mary 321 River Side Clifton NJ 07055
98765 33333 Tom 12 Main St Miami FL 33139
and TABLE ClientAcct
CheckNum Account Cost Credit Notes
---------------------------------------------------------------------
12345 11111 1 Yes Great
12345 11111 11 Yes Well
12345 11111 111 No Bad
54321 22222 2 Yes Fine
54321 22222 22 No OK
98765 33333 3 Yes I like it
This the end result that I want.
CheckNum Account Name Addr1 City State Zip SUM of
Max(Notes)
of Cost or
Min(Notes)
-----------------------------------------------------------------------------
12345 11111 John 123 Mary St Miami FL 33139 123 Great
54321 22222 Mary 321 River St Clifton NJ 07055 24 Fine
98765 33333 Tom 12 Main St Miami FL 33139 3 I like it
I need to avoid duplicate rows(Note only if the Account field are
equal).
I need to get the fields shown above including the SUM of
ClientAcct.Cost and the MAX or Min of ClientAcct.Notes.
I have searched the web and
have tried DISTINCT, UNION, GROUP, COUNT(*) = 1 AND COUNT(*) <> 1 but
I can't get the correct results.
This statement give me too many rows(duplicate)
--------------------------------------
SELECT [Name], ClientInfo.account, addr1, City, State, ZIP, COST,
Notes from ClientInfo JOIN ClientAcct on
ClientInfo.CheckNum=ClientAcct.CheckNum
Does any body have a solution to this?
Thanks in advance
Julio