remove duplicates in query

[tizwaz]

I have a table TblContracts and a table TblCategory. Each contract may have one or more categories. I want to be able to search for a contract using different fields, one of which could be category and show the matching contracts in a continuous form

However when a contract has more than one category the query returns several entries for the same contract with each different category alongside it unless my search criteria specifies a category.

Is there a way of eliminating the duplicate contract numbers or do I have to do 2 separate search forms - one including category and the other for the rest of the fields (which are all in the main contract table)

 

[Golom]

SQL of the form

Select C.Contract, ... Other Contract Fields ...,
       MAX(A.Category) As [MAX Category]

From Contracts C INNER JOIN Categories A
     ON C.Contract = A.Contract

Group By C.Contract

Will produce only one record for each contract.