Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello folks, I have a table "Test Results", on which the following wee procedure is run. I need to change it slightly to only show unique records on a field called "Control". Can you help me??
    I know about the Unique Values in the query grid, but don't know how to get the same result running a module.

    Thanks as always.

    ub AddTest()
    AddData "TestResults", 5
    End Sub

    'ADDS TOTALS FROM TestResults TABLE TO LINE 5 OF TBLH_TestingProgress
    Sub AddData(strSource As String, lngLineID As Long)
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim i As Long
    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset(strSource, dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("SELECT * FROM TblH_TestingProgress WHERE LineID=" & _
    lngLineID, dbOpenDynaset)
    ' Optional: set all fields to 0 except the first two
    rstOut.Edit
    For i = 2 To rstOut.Fields.Count - 1
    rstOut.Fields(i) = 0
    Next i
    rstOut.Update
    ' Loop through data records
    Do While Not rstIn.EOF
    'If rstIn!SentforSox = "Yes" Then
    ' Update appropriate field and total
    rstOut.Edit
    rstOut.Fields(rstIn!Location) = rstOut.Fields(rstIn!Location) + 1
    rstOut!Total = rstOut!Total + 1
    rstOut.Update
    ' End If


    rstIn.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstOut.Close
    Set rstOut = Nothing
    rstIn.Close
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Can't quite see from your code which bit needs to be Unique

    Is it the Source Data that ought to be Unique

    Code:
    Set rstIn = dbs.OpenRecordset(strSource, dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("SELECT * FROM TblH_TestingProgress WHERE LineID=" & lngLineID, dbOpenDynaset)
    However in General using SQL

    Code:
    SELECT Field1, Field2, Field3 FROM tblData
    will Create a Full List of ALL Records

    To Extract the UNIQUE List against Fields 1 to 3 you need

    Code:
    SELECT DISTINCT Field1, Field2, Field3 FROM tblData
    Andrew

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by AKW View Post
    Can't quite see from your code which bit needs to be Unique

    Is it the Source Data that ought to be Unique

    Code:
    Set rstIn = dbs.OpenRecordset(strSource, dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("SELECT * FROM TblH_TestingProgress WHERE LineID=" & lngLineID, dbOpenDynaset)
    However in General using SQL

    Code:
    SELECT Field1, Field2, Field3 FROM tblData
    will Create a Full List of ALL Records

    To Extract the UNIQUE List against Fields 1 to 3 you need

    Code:
    SELECT DISTINCT Field1, Field2, Field3 FROM tblData
    You could also use the GROUP BY clause on those fields

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You can use GROUP BY , but if you are writing it in the code window,
    using DISTINCT is less typing, unless you want to also get some summary data.
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •