Query Access database from Excel 1

[kodr]

I'm connecting to an Access database from excel using Microsoft ActiveX Data Objects 2.6

All is good, able to retrieve and add information to my tables just fine.

My problem is this, I have a table (tblA) and it has four fields that are numberical indexes.

Field1 can contain a number between 1 and 5, the same for Field2 thru Field4.

Each possible combination may have upto 24 duplicate records. Is it possible with one query to pull one occurance of each possible combination?

For instance:

1,1,1,1
1,1,1,2
...
1,1,2,1
...
1,2,1,1
etc...

I believe this would be something like 125 records, as opposed to the 3,000 I'm getting now.

My current query is this:

sSQL = "SELECT * FROM tblA WHERE Field1=1"
RSET1.Open sSQL, CONN1, adOpenStatic, adLockOptimistic

Any ideas?

 

[PHV]

SELECT DISTINCT * ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kodr]

Can you use DISTINCT on more then one field?

 

[PHV]

Simply use this:
sSQL = "SELECT DISTINCT * FROM tblA WHERE Field1=1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kodr]

Thanks to you both. The simpilest answer is usually the best. I was picturing an overly complicated statement.

 

[kodr]

Er, both of your answers that is.