Selcting unique values

[k209310]

Hello again..

I need to select three fields from a table however i need to select only the fields where two of the fields are DISTINCT. The final collum is a collum of unique values.

I have written a query to selct the fields I require. The problem i am having is when I make the query and Set it to distinct all of the records are selected becasue of the unique value.

Does anyone know how to set the query to pick up fields where field1 and field2 are unique and to ignore the third.

I hope this makes sense

Thanks in advance

Chris

 

[mission2java_78]

Use multiple queries or multiple recordsets...for instance...
first to select your first set of records:

strSQL = "SELECT DISTINCT blah blah"
'create recordset
set db=currentdb
set rs=db.openrecordset(strSQL)

strSQL2 = "SELECT DISTINCT blah blah WHERE ID= " rs
("ID").Value

Jon

 

[k209310]

thanks for the reply.

Is there a way to do it without using code? Just using queries?

I will use code if i have to but i was hoping there was an easier way.

Chris

 

[neileg]

Yes you can use queries, Jon said so, but did not give the example.

I assume this is a folow on from your other question I answered, so:

Create a query with your two tables and join fields 1 and 2 as before. Add Fields 1 and 2 to your grid, set the unique values property on. Don't add field3 to the grid. Save this query.

Create a second query, add your first query and your table with field3. Join fields 1 and 2 so that it shows all of the values from the query and only those from the table that match. Add field 1, 2 and 3 to your grid.

Is this what you want. If not, please clarify and I'll have another go.

 

[k209310]

Spot on once again. Thanks.