Solved

Need some VBA logic help

Posted on 2012-12-21
5
541 Views
Last Modified: 2012-12-21
I'm trying to work the logic out for this. I want my results to something like below. I'm trying to populate the SeqNum off a check if the prior Client_Id, last and first match.

Client_Id_Num   Last_Name    First_Name  SeqNum
00-1365         Smith        Alfred      1
00-1365         Smith        Alfred      2
00-1365         Smiths       Alfred      1
00-1412         Jones        Alan        1
00-1412         Jones        Alan        2
00-1412         Jones        Alan        3
00-1550         Doe          Jeff        1
00-1550         Doe          Mitzi       1
02-1036         (dtr)        Terri       1
02-1036         Thomas       Terry       1
02-1036         Pear         Stacy       1
02-1036         (dtr)        Stacy       1
02-3362         Lemon        Alfredo     1
02-3362         Lemon        Alfredo     2
02-3362         Lemon        Guadalupe   1
04-1005         Cherry       Omar        1
04-1005         Orange       Marisol     1
04-1005         Orange       Marisol     2
04-1126         Banana       Deborah     1
04-1126         Banana       Deborah     2
04-1126         Apple        Tristan     1
04-1126         Apple        Deborah     1

Open in new window


The logic I have so far:

ClientID = "0"
FName = "ZZ"
LName = "ZZ"

Do While RS.EOF = False
    If ClientID <> RS!Client_Id_Number Then
        I = 1
        With RS
            ClientID = !Client_Id_Number
            FName = !First_name
            LName = !Last_name
            .Edit
            !SeqNum = I
            .Update
        End With
    Else
        If LName <> RS!Last_name Then
            If FName <> RS!First_name Then
                I = 1
                With RS
                    ClientID = !Client_Id_Number
                    LName = !Last_name
                    FName = !First_name
                    .Edit
                    !SeqNum = I
                    .Update
                End With
            Else
                I = I + 1
                With RS
                    .Edit
                    !SeqNum = I
                    .Update
                End With
            End If
        Else
            I = 1
            With RS
                ClientID = !Client_Id_Number
                LName = !Last_name
                .Edit
                !SeqNum = I
                .Update
            End With

        End If
    End If

    RS.MoveNext

Loop

RS.Close

Open in new window


It's not giving me the right Sequence Number. On the Jeff and Mitzi doe, I'm getting a 1 and 2. On the Alfred Smith, I'm getting 1, 1, 2. It should be 1,2,1. I just can't get the logic down for this one.

Thanks all.
0
Comment
Question by:Jim P.
  • 3
5 Comments
 
LVL 9

Expert Comment

by:borki
ID: 38714661
You have not given us the actual SQL query you are using to traverse your recordset. What is the order that you are using on this query?

I think your code should work, but it is crucial that you are sorting by Client_Id_Num, Last_Name, First_Name.

Not sure what your mission is, but wouldn't you be better off introducing a unique, artificial primary key?
0
 
LVL 38

Author Comment

by:Jim P.
ID: 38714723
The query:

SQL = "SELECT Facility_Code, Client_Id_Number, Last_Name, First_Name, ContactType01, SeqNum " & _
    "FROM ContactsTbl " & _
    "ORDER BY Facility_Code, Client_Id_Number, Last_Name, First_Name, ContactType"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

Open in new window


Basically I need to put a sequence number on the data, because later on I have to de-normalize it. There are fields to the right that are addresses, contact types, etc. that I'm exporting to another system. The Contact has to be one line and the spec I'm writing to has fields done as ContactType01, ContactType02, ContactType03, ...

So what I'm trying to do is put a sequence number on it so that I can move the contact type into the proper spot. The end-users are going to have to simply deal with their typos and extra contacts in the receiving system.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38714769
jim,

add an autonumber field (ID) to your table, then use this query

SELECT TableX.ID, TableX.Client_Id_Num, TableX.Last_Name, TableX.First_Name, (select count(*) from tableX as x where x.Client_Id_Num=tablex.Client_Id_Num and x.Last_Name=tablex.Last_Name and x.First_Name=tablex.First_Name and x.ID<=tablex.ID) AS Seq
FROM TableX
ORDER BY TableX.Client_Id_Num, TableX.Last_Name, TableX.First_Name, TableX.ID;
0
 
LVL 38

Author Comment

by:Jim P.
ID: 38714865
Final Code:

SQL = "SELECT ContactsTbl.Client_Id_Number, ContactsTbl.Last_Name, ContactsTbl.First_Name, (select count(*) from ContactsTbl as x where x.Client_ID_Number =ContactsTbl.Client_ID_Number  and x.Last_Name=ContactsTbl.Last_Name and x.First_Name=ContactsTbl.First_Name and x.ID_Num<=ContactsTbl.ID_Num) AS Seq, ContactsTbl.ID_Num, ContactsTbl.ContactType01 " & _
"FROM ContactsTbl " & _
"ORDER BY ContactsTbl.Client_Id_Number, ContactsTbl.Last_Name, ContactsTbl.First_Name, ContactsTbl.ContactType01, ContactsTbl.ID_Num;"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then
    RS.MoveFirst
    Do While RS.EOF = False
        
        SQL = "UPDATE ContactsTbl " & _
            "SET Seq_Num = " & RS!Seq & " " & _
            "WHERE ID_Num = " & RS!ID_Num
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL SQL, False
        DoCmd.SetWarnings True
        
        RS.MoveNext
        
    Loop
End If

RS.Close

Open in new window

0
 
LVL 38

Author Closing Comment

by:Jim P.
ID: 38714867
Thanks Cap.

I know how to use Partition Over in SQL, just couldn't get it here.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now