Select Distinct

kirkmc · April 3rd, 2006, 11:05 PM

When one applies Select Distinct to field X in a query only those records which have a unique field X are displayed.

But what if you want all fields in the record, but still based on field X being unique?

Also is there a way to get .FieldCount similar to .RecordCount property?

Thanks - Kirk

echovue · April 3rd, 2006, 11:16 PM

Try something like...

SELECT DISTINCT FieldX, FieldA, FieldB

Mike
EchoVue.com

kirkmc · April 4th, 2006, 03:44 AM

As soon as another field is added 'fieldX'loses it's distinctness.
This may well be no solution.

What I'm after is

Select FieldX, FiedlA, FieldB
From Fred
Where FieldX <> Any other FieldX in Fred.

Is this possible in a query?

Bob Bedell · April 4th, 2006, 05:39 AM

Here's the FieldCount piece:

' Call FieldCount("Table1")

Sub FieldCount(strTable As String)
  Dim cnn As New ADODB.Connection
  Dim cat As New ADOX.Catalog
  Dim intFieldCount As Integer

  Set cnn = CurrentProject.Connection
  Set cat.ActiveConnection = cnn

intFieldCount = cat.Tables(strTable).Columns.Count
Debug.Print intFieldCount

End Sub

HTH,

Bob

Bob Bedell · April 4th, 2006, 06:00 AM

Not sure off hand if the other part can be done using SQL, but you can do it using Collections. The following counts the number of times each value occurs in FieldX, then outputs all values that are unique (i.e., ValueCounts(ValueNames(index)) = 1):

Code:

Sub Test()

    Dim db As DAO.database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim strSQL As String, key As String
    Dim index As Long, total As Long
    Dim ValueCounts As Collection
    Dim ValueNames As Collection

    Set ValueCounts = New Collection
    Set ValueNames = New Collection

    Set db = CurrentDb()

    strSQL = "SELECT FieldX FROM Table1"

    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    rst.MoveFirst
    Do Until rst.EOF
        For Each fld In rst.Fields
            On Error Resume Next
            total = 0
            key = fld.Value
            total = ValueCounts(key)
            ValueCounts.Remove key
            ValueCounts.Add total + 1, key
            ValueNames.Add key, key
        Next fld
        rst.MoveNext
    Loop
    On Error GoTo 0

    For index = 1 To ValueNames.Count
        If ValueCounts(ValueNames(index)) = 1 Then
            Debug.Print ValueNames(index); " = "; ValueCounts(ValueNames(index))
        End If
    Next
End Sub

Output looks like:

F = 1
K = 1
U = 1
Z = 1

The value "A" appears twice in my test data, so it is ignored.

HTH,

Bob

kindler · April 4th, 2006, 07:32 AM

I think the easiest way to do this is to use two queries where one is an aggregate, i.e.

Query1:
SELECT FieldX, COUNT(FieldA) As Cntr
FROM Tbl
WHERE Cntr = 1

Query2:
SELECT Tbl.*
FROM Tbl, Query1
WHERE Tbl.FieldX = Query1.FieldX

kirkmc · April 5th, 2006, 12:34 AM

Thanks one and all.
Lots to experiment with.
Much appreciated - Kirk

kirkmc · April 5th, 2006, 04:47 AM

Hi kindler

Before I apply Bob's code (thank you Bob) I spent some time with your
queries to see if I could implement them and also see how they worked. I always get an error though. He's the sql (changed to actual field & table name) for Query1

SELECT Track, COUNT(Track) AS Cntr
FROM tbl1953
WHERE Cntr = 1;

You have a different field for COUNT but I found the same result either way:-

You tried to execute a query that doesn't include the specified expression 'Track' as part of an aggregate
function.

I first ignored this and went on to Query2 but that also gave the same error.

SELECT tbl1953.*
FROM tbl1953, Query1
WHERE tbl1953.track = Query1.track;

Any thoughts?

Thanks - Kirk

kindler · April 5th, 2006, 11:24 AM

Ah, messed up the syntax some, it should be for Query1:

SELECT Track, COUNT(*)
FROM tbl1953
GROUP BY Track
HAVING COUNT(*) = 1;

The second query should work fine as is. I just tested this on one of my DBs and it worked, hopefully you get the same results.

kirkmc · April 6th, 2006, 09:28 PM

Thanks kindler. That worked just perfectly.
Although - LOL - it's opened a can of worms proving my initial logic needs modifying.
Wonderful response from you and Bob. Both methods under experimentation here! Thanks again. This forum looks majorly useful. Hope I can ask a bit more in the future.
Cheers - Kirk