This is a forum where members new and old can come to ask questions and get info and opinions. It is not a place to advertise your business or have other forms of advertising, whether it be in your posts or signature.

All links in the forum will not be indexed by Search Engines and any unapproved forms of advertising or spam will be dealt with accordingly, deleted, and that member's account banned.

Forums

Overview » SQL and Databases » Using the DISTINCT operator

Using the DISTINCT operator
DAVID HEARN Member Since: 09 Nov 2007 Posts: 1	Posted 11 Nov 2008 12:40:13 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 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

Using the DISTINCT operator

DAVID HEARN
Member

Since: 09 Nov 2007
Posts: 1

Posted 11 Nov 2008 12:40:13

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

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

Alan C
Total freaking Member

Since: 04 May 2006
Posts: 473

Replied 11 Nov 2008 18:45:32

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.