Hi everyone.

I have a query linking 2 tables. The idea of the query was to show our longest serving customers. The one table contains CustNo, Address details, etc and the second table contains AccNo, CurrBal, AccType, CustNo, etc etc. (note the bold fields are the primary keys). However this is bringing back results on account level rather than customer level, i.e CustNo 3 might Have AccNo xxxxxx, AccNo xxxxx1, AccNo xxxxxx2. How do I get it to show the 'oldest' account for each customer. So it will only show each customer once and that customers oldest account. Heres the SQL for the query. Hope someone can help. Thanks

SELECT DBSINV97.DateOpen, DBSINV97.AccNo, DBSINV97.CustNo, DBSINV97.PostalName, DBSINV97.CurBal, DBSINV97.AccType, DBSINV97.DateClose, DBSCUS01A.Title, DBSCUS01A.Initial, DBSCUS01A.Surname, DBSCUS01A.Street, DBSCUS01A.District, DBSCUS01A.Town, DBSCUS01A.County, DBSCUS01A.Postcode

FROM DBSINV97 INNER JOIN DBSCUS01A ON DBSINV97.CustNo = DBSCUS01A.CustNo

WHERE (((DBSINV97.DateOpen)<"2002-01-01") AND ((DBSINV97.DateClose)="1800-01-01"));

Make it a group by query with a sort asending on the CustID and sort descending on the AcctNo. And change the account infor to be FirstOf. This is an assumption based on the fact that Account number are aged by numeric value. If not then you will have to sort by account created/started/setup date (if you have one).

CodeMaster::cool: