How to compare value in same field?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • wilboy
    New Member
    • Jun 2007
    • 7
    #1

    How to compare value in same field?

    Hi

    I tired to compare the value in same field, eg:

    Staff ID TranNum
    123456 1
    122333 1
    133222 1
    123456 2?
    111223 1
    123456 3

    I want to increase the TranNum by 1 if the staff ID is same. Is there any way?
    Thanks!

    Wilboy
    Tags: None
    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834
      #2
      Originally posted by wilboy
      Hi

      I tired to compare the value in same field, eg:

      Staff ID TranNum
      123456 1
      122333 1
      133222 1
      123456 2?
      111223 1
      123456 3

      I want to increase the TranNum by 1 if the staff ID is same. Is there any way?
      Thanks!

      Wilboy
      It can be done via VBA code, but I'm going to lay low and see if someone can come up with a better and simpler solution. I'll keep in touch.

        Comment

        • wilboy
          New Member
          • Jun 2007
          • 7
          #3
          Anyone please help....

          ADezii, thanks for your quick reply. I guess your way is the only way to solve my problem now..... Would you mind to show me how to do it?
          Many many many thanks!

            Comment

            • wilboy
              New Member
              • Jun 2007
              • 7
              #4
              I urgently need help. If you have any suggestion please let me know!
              Thank you

                Comment

                • ADezii
                  Recognized Expert Expert
                  • Apr 2006
                  • 8834
                  #5
                  Originally posted by wilboy
                  I urgently need help. If you have any suggestion please let me know!
                  Thank you
                  1. Create tblWilboy with the following Fields as you previously indicated. DO NOT change this name or the name of the Query listed below.
                    1. Staff ID
                    2. TranNum
                  2. Create qryUniqueStaffI Ds exactly as described in the code template and do not change its Name to anything else.
                  3. Copy and paste this code wherever appropriate:
                    [CODE=vb]Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
                    Dim intNumOfDups As Integer, MyRS_2 As DAO.Recordset, intCounter As Integer

                    'Create the following Query and Name it qryUniqueStaffI Ds. This Query will contain only 1 Field from tblWilboy and will return 'only Unique Staff IDs' in Acsending Order
                    'SELECT DISTINCT [tblWilboy].[Staff ID]
                    'FROM tblWilboy
                    'ORDER BY [tblWilboy].[Staff ID];

                    Set MyDB = CurrentDb()
                    Set MyRS = MyDB.OpenRecord set("qryUniqueS taffIDs", dbOpenSnapshot)

                    MyRS.MoveFirst

                    DoCmd.SetWarnin gs False
                    Do While Not MyRS.EOF
                    If DCount(MyRS![Staff ID], "tblWilboy" , "[Staff ID] = " & MyRS![Staff ID]) = 1 Then
                    MySQL = "UPDATE tblWilboy SET tblWilboy.TranN um = 1 WHERE tblWilboy.[Staff ID]= " & MyRS![Staff ID]
                    DoCmd.RunSQL MySQL
                    Else
                    intNumOfDups = DCount(MyRS![Staff ID], "tblWilboy" , "[Staff ID] = " & MyRS![Staff ID])
                    MySQL = "Select * From tblWilboy Where [Staff ID] = " & MyRS![Staff ID]
                    Set MyRS_2 = MyDB.OpenRecord set(MySQL, dbOpenDynaset)
                    MyRS_2.MoveFirs t
                    For intCounter = 1 To intNumOfDups
                    MyRS_2.Edit
                    MyRS_2![TranNum] = intCounter
                    MyRS_2.Update
                    MyRS_2.MoveNext
                    Next
                    End If
                    MyRS.MoveNext
                    Loop
                    DoCmd.SetWarnin gs True

                    MyRS.Close
                    MyRS_2.Close[/CODE]

                    Comment

                    • wilboy
                      New Member
                      • Jun 2007
                      • 7
                      #6
                      Originally posted by ADezii
                      1. Create tblWilboy with the following Fields as you previously indicated. DO NOT change this name or the name of the Query listed below.
                        1. Staff ID
                        2. TranNum
                      2. Create qryUniqueStaffI Ds exactly as described in the code template and do not change its Name to anything else.
                      3. Copy and paste this code wherever appropriate:
                        [CODE=vb]Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
                        Dim intNumOfDups As Integer, MyRS_2 As DAO.Recordset, intCounter As Integer

                        'Create the following Query and Name it qryUniqueStaffI Ds. This Query will contain only 1 Field from tblWilboy and will return 'only Unique Staff IDs' in Acsending Order
                        'SELECT DISTINCT [tblWilboy].[Staff ID]
                        'FROM tblWilboy
                        'ORDER BY [tblWilboy].[Staff ID];

                        Set MyDB = CurrentDb()
                        Set MyRS = MyDB.OpenRecord set("qryUniqueS taffIDs", dbOpenSnapshot)

                        MyRS.MoveFirst

                        DoCmd.SetWarnin gs False
                        Do While Not MyRS.EOF
                        If DCount(MyRS![Staff ID], "tblWilboy" , "[Staff ID] = " & MyRS![Staff ID]) = 1 Then
                        MySQL = "UPDATE tblWilboy SET tblWilboy.TranN um = 1 WHERE tblWilboy.[Staff ID]= " & MyRS![Staff ID]
                        DoCmd.RunSQL MySQL
                        Else
                        intNumOfDups = DCount(MyRS![Staff ID], "tblWilboy" , "[Staff ID] = " & MyRS![Staff ID])
                        MySQL = "Select * From tblWilboy Where [Staff ID] = " & MyRS![Staff ID]
                        Set MyRS_2 = MyDB.OpenRecord set(MySQL, dbOpenDynaset)
                        MyRS_2.MoveFirs t
                        For intCounter = 1 To intNumOfDups
                        MyRS_2.Edit
                        MyRS_2![TranNum] = intCounter
                        MyRS_2.Update
                        MyRS_2.MoveNext
                        Next
                        End If
                        MyRS.MoveNext
                        Loop
                        DoCmd.SetWarnin gs True

                        MyRS.Close
                        MyRS_2.Close[/CODE]

                      -----------------------------------------------------------------------------------------------------
                      Hi ADezii

                      You are the Champion! The code works perfectly!
                      I can't say enough "THANKS"....... ..... :)

                      Wilboy

                        Comment

                        • ADezii
                          Recognized Expert Expert
                          • Apr 2006
                          • 8834
                          #7
                          Originally posted by wilboy
                          -----------------------------------------------------------------------------------------------------
                          Hi ADezii

                          You are the Champion! The code works perfectly!
                          I can't say enough "THANKS"....... ..... :)

                          Wilboy
                          Glad it worked for you, it was a little tougher nut to crack than I originally anticipated. Sorry for the delay. I was almost certain that there was a simpler method to solve this and that's why I held off - guess I was wrong. Good Luck.

                            Comment

                            Previous template Next
                            Working...