I need to use SQL with a "Distinct" so that only one row of a record is returned from the query. I can do this with one variable but what is the syntax for multiple fields... the example that works is...

SELECT DISTINCT field_name
FROM table_name
WHERE field_name2 = some_value

What I need is to return multiple field_names from the same table/query but only with one row returned for the record. I have a record ID that is unique for the row already established. An example of what i need is as follow:

User_Name Address Interests
Kent Steelman Someplace USA Scuba Diving

Not

User_Name Address Interests
Kent Steelman Someplace USA Scuba Diving
Kent Steelman Someplace USA Baseball

Now you may think simple just retrict by the Interests Field... yes I can do that, but what i want the user to be able to do is either select a single interest or use the all catigory and still only return one record for the USER

Kent

Wm. Kent Steelman

The easiest way to resolve this would be to rorganise your table structure slightly so that all interests for one user are stored in a single text or memo field.

For example, when capturing the info set up a series of check boxes for interests or a list/menu that allows multiple selections. Multiple selections from a list/menu will give something like:

Football, Skiing, Motorcycles,


You could set up a series of checkboxes to do something similar.

Collect the comma separated values:

varInterests = Request("ListMenuName"
varLength = Len(varInterests) ' Find the length of the string
varInterests = Left(varInterests, (varLength - 1)) ' Get rid of the final comma
varInterests = Replace(varInterests,", " "<BR>" ' Replace the commas with an HTML line break.

Then insert this into an interests memo field.

This would be much more efficient than storing multiples of the same information just to allow multiple interests.

When you display the information on the page the line breaks will allready be there so you will see the username, location then the interest in a virticle list.

Alternatively store the values in th memo field with the commas and replace them with line breaks when you display the text.

Then when a user selects an interest category all you need is:

varCategory = Request("ListCategoriesName"

Then:

Recordset.Source = "SELECT Whatever, WhateverElse FROM TableName WHERE Intersts LIKE '" & varCategory & "'"

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo