Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 3rd, 2006, 11:05 PM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select Distinct

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

  #2 (permalink)  
Old April 3rd, 2006, 11:16 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Try something like...

SELECT DISTINCT FieldX, FieldA, FieldB

Mike
EchoVue.com
  #3 (permalink)  
Old April 4th, 2006, 03:44 AM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

  #4 (permalink)  
Old April 4th, 2006, 05:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

  #5 (permalink)  
Old April 4th, 2006, 06:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

  #6 (permalink)  
Old April 4th, 2006, 07:32 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old April 5th, 2006, 12:34 AM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #8 (permalink)  
Old April 5th, 2006, 04:47 AM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #9 (permalink)  
Old April 5th, 2006, 11:24 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #10 (permalink)  
Old April 6th, 2006, 09:28 PM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Distinct kirkmc Excel VBA 3 May 5th, 2006 07:55 PM
Select Distinct? hugh@kmcnetwork.com SQL Language 5 November 5th, 2005 09:58 AM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM
select distinct bmains ADO.NET 0 April 8th, 2004 02:50 PM
Select distinct elements andrin XSLT 2 August 4th, 2003 04:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.