Sorry this long and involved but I feel I have to describe the problem as fully as possible. I have an Access table which has the following fields: (simplified extract)

boatname refcode minsleeps
Clipper xxx1 2
Topper xxx2 2 to 3
Chipper xxx3 2 to 4

I have a search form which allows the user to search by boat name as a keyword, by reference code and by the number of people the boat can sleep. As the second and third items in the above table can be used by 2, 3 or 4 people I don't want to limit the results if the user selects 2 in the search page so that it does not show the other boats. As such, I reasoned the best thing to do would be to add records to the table as below so that the user could for instance search for 2 but still get the records for the other 2 boats by using the >= operator in the SQL of the recordset.

boatname refcode minsleeps
Clipper xxx1 2
Topper xxx2 2
Topper xxx2 3
Chipper xxx3 2
Chipper xxx3 3
Chipper xxx3 4

The trouble is if the user selects 2, they get duplicate records on the results page - Topper twice and Chipper three times. I have tried to use the DISTINCT clause in the SQL to eliminate duplicates using the field name "refcode" but despite having tried it in every way I can imagine, it does not work. Can anyone suggest the correct notation for the SQL please. Below is the actual expanded copy of my SQL Recordset. Please be aware that I am new to this and am trying to "feel" my way along. I use Dreamweaver and the form and results page are on a testing server at the moment.

SELECT *
FROM BoatTable
WHERE minsleeps >=varminsleeps AND yardlocation LIKE 'varyardlocation' AND northorsouth LIKE 'varnorthorsouth' AND steersfrom LIKE 'varsteersfrom' AND Toilets LIKE 'varToilets' AND boatname LIKE '%keyword%' AND Potter LIKE 'varPotter' AND Microwave LIKE 'varMicrowave' AND homevoltage LIKE 'varhomevoltage'
ORDER BY minsleeps ASC

Many thanks

David

Hi David,
to recap, you have multiple records showing the number of sleeping berths for each boat, so for Chipper you have three records, in that case you will get three results because three records meet your criteria. I've only used DISTINCT to pull out values from single columns when building dropdown lists.
Could you try putting in the maximum capacity, so for Chipper the number of berths would be 4, then you could search for boats that had <= (less than or equal) then you would only need one record for each.

Another thought occurred to me too, if the boatname is not unique, for example if you had lots of Clippers, Toppers and Chippers, then you would be storing all those names more than once. If you were to change the name of any of them, for instance if Chippers became Choppers, then you would have to write an update to change all of them. You might consider putting the names into a separate table then putting the key (id) from that table into the name field in your boats table. That way you only store the name once, so if you change it, or add to the list of names it's easy. BUT it complicates the logic because you have to JOIN the tables to get the data out.