In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Solved
Need some VBA logic help
Posted on 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.
The logic I have so far:
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.
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
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
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
5 Comments
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?
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?
The query:
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.
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)
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.
Active today
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.Cli
FROM TableX
ORDER BY TableX.Client_Id_Num, TableX.Last_Name, TableX.First_Name, TableX.ID;
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.Cli
FROM TableX
ORDER BY TableX.Client_Id_Num, TableX.Last_Name, TableX.First_Name, TableX.ID;
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s
ManageEngine
webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship.
Add the tables:
Create the relationship:
Decide if you’re going to set referential integrity:
Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month15 days, left to enroll
649 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.