INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Thanks! Awesome group. I put out a simple question in the access/vba forum that I couldn't find answered on technet or anywhere else on the web and it was answered the same day!!..."
Geography
Where in the world do Tek-Tips members come from?
|
Finding integer sequence amongst another integer sequence.
|
|
|
VickyC (TechnicalUser) |
12 Jun 12 19:35 |
hello - I'm hoping there is a good solution to a problem I'm having. I can probably figure out the details if someone can point me in the right direction.
Consider two tables.
CODEtblA tblB
A_ID A1 A2 A3 A4 B_ID B1 B2 B3 B4 B5 B6 B7 B8 B9 B10
1 45 42 42 36 100 75 45 45 43 42 41 36 23 11 9
2 54 29 21 8 101 65 50 45 44 44 42 42 39 36 33
3 35 35 13 10 102 56 52 50 51 37 33 29 10 7 3
4 etc... 104 45 42 39 35 35 35 35 30 13 10
105 etc...
In tblA, A1 >= A2 >= A3 >= A4.
In tblB, B1 >= B2 >= B3 >= B4 >= B5 >= B6 >= B7 >= B8 >= B9 >= B10
Here's my question. I need to DELETE all records in tblB that contain the values (A1, A2, A3, A4) as a subset.
In the example shown, record 101 gets deleted because it contains the from record 1:
CODE1 45 42 42 36 101 65 50 45 44 44 42 42 39 36 33
Similarly, 104 gets deleted because it contains the values in record 3:
CODE3 35 35 13 10 104 45 42 39 35 35 35 35 30 13 10
Note - record 100 DOES NOT get deleted. It has all the values in row 1, but it only has one 42.
tblA has 4 columns of data. If tblB also had the same number of columns, then I can just use a Left Join ON (A1 = B1) AND (A2 = B2) AND... ..WHERE tblB.B_ID IS NULL.
When tblB has 5 data columns, I can do something similar, but the 'ON' is more complicated...
CODEON (((A1=B1) AND A2=B2) AND A3=B3) AND A4=B4)) OR ((A1=B1) AND A2=B2) AND A3=B3) AND A4=B5)) OR ... etc.
My real problem is that tblB has so many data columns that the SQL has become completely lenghty and awkward. I'm hoping there is a better way.
Many thanks, Vicky
|
|
|
MajP (TechnicalUser) |
14 Jun 12 7:07 |
Your title is sequence, but the description says "contains the values". This may make it more difficult. In other words assume
in A
5 3 2 1
if B is the following
6 5 4 3 2 1
Is that a match? It does not have the same sequence, but contains all the values. B interrupts the sequence with the number 4. If it has to be a matching sequence I could use the instr function to make it easier. Then what I would build a UDF that returns a concatenated string. I would separate it with a delimiter. It would return
5:3:2:1
Same on b
9:7:6:5:3:2:1
Then I would build a second udf that I pass the concatenated strings and it would use an instr function to check if concatenated A is in concatenated B and return true.
If it only has to contain the values (and the same amount of each value) then that is a little more complicated. I would do it again with a UDF. Pass in all the A and B values and return true.
Something like
1) Take first number from A
2) Find first match in B if no match return false
3) loop remain letters in A an move through B from current location until finding match. If no match return false.
4) else return true
In that version you could use the function in a query.
Personally I would do this all in code. Basically the same logic, but instead of passing in field values I would pass in the query or table names. Then loop the records and loop the fields in A and B (excluding the first field). In that way if table A has 50 fields and B 200 fields this would be still very doable. I will give a try and pass the code if it works. Although that may sound complicated my guess the amount of code is very short since it is really just 3 loops.
|
|
|
VickyC (TechnicalUser) |
14 Jun 12 16:15 |
hi MajP - Yes it is true that the matches for A1, A2, A3, A4 can be spread out and do not need to be contiguous, still while still being in descending order. I too have realized that code might be better here that just straight SQL. I think this may be a bit slower, but I only need to do this process about 25 times - speed shouldn't be too big an issue.
Although you mentioned 3 loops, I'm starting to get this to work using 4. Basically, I loop through every record of tblA. For each of these, I loop through every record of tblB. For each of these, I loop through each data COLUMN of tblA. For each of these, I loop through every data column of tblB. Things seem to be working very well, and faster than I had thought.
This highlights a problem I seem to struggle with from time to time - I don't always have a good sense of when it is best to use straight VBA code, SQL, or some combo of the two.
Thanks for the pointers
Vicky C. |
|
|
MajP (TechnicalUser) |
14 Jun 12 20:27 |
If you got this working then good on you. Because I think I was a little cavalier in how easy this would be. Here is my solution and not even sure it is correct. This was pretty hard. I am curious how you did it. The hard part was once you find a value in B the next value from A has to occur in a field in B after the field in which you found the value. That is the only way to ensure that if A has two 45s then B has at least two 45s. I loop all records in A and all records in B. I loaded each record into a collection and pass that to a function to check to see if the values in A are contained in B.
CODEPublic Sub testAinB()
FindAinB "TableA", "TableB"
End Sub CODEPublic Sub FindAinB(qryA As String, qryB As String)
Dim rsA As DAO.Recordset
Dim rsB As DAO.Recordset
Dim fldA As DAO.Field
Dim fldB As DAO.Field
Dim Aid As Integer
Dim Bid As Integer
Dim Avalue As Integer
Dim Bvalue As Integer
Dim collA As Collection
Dim collB As Collection
Set rsA = CurrentDb.OpenRecordset(qryA, dbOpenDynaset)
Set rsB = CurrentDb.OpenRecordset(qryB, dbOpenDynaset)
Do While Not rsA.EOF
Set collA = New Collection
For Each fldA In rsA.Fields
If fldA.Name = rsA.Fields(0).Name Then
Aid = fldA
Else
collA.Add (fldA)
End If
Next fldA
Do While Not rsB.EOF
Set collB = New Collection
For Each fldB In rsB.Fields
If fldB.Name = rsB.Fields(0).Name Then
Bid = rsB.Fields(0)
Else
collB.Add (fldB)
End If
Next fldB
If AinB(collA, collB) Then
'code here to do something once you find A in B such as delete
Debug.Print "True" & " " & Aid & " " & Bid
End If
rsB.MoveNext
Loop
rsA.MoveNext
Loop
End Sub
Public Function AinB(collA As Collection, collB As Collection) As Boolean
Dim i As Integer
Dim j As Integer
Dim startLoc As Integer
Dim numberFound As Integer
Dim numberRequired As Integer
numberRequired = collA.Count
startLoc = 1
For i = 1 To collA.Count
blnFound = False
For j = startLoc To collB.Count
'Debug.Print "A " & collA(i) & " B " & collB(j)
If collA(i) = collB(j) Then
numberFound = numberFound + 1
startLoc = j + 1
'Found all required
If numberFound = numberRequired Then
AinB = True
Exit Function
Else
'Did not find all and at end
If startLoc > collB.Count Then Exit Function
'Check next A
Exit For
End If
End If
Next j
Next i
End Function |
|
|
MajP (TechnicalUser) |
15 Jun 12 8:25 |
I forgot to answer this question
Quote:
I don't always have a good sense of when it is best to use straight VBA code, SQL, or some combo of the two.
SQL is designed to work well with normalized data, but does not really support non normal structures. Your data is nonnormal. Searching across rows I do in SQL searching across columns I write code.
But the better alternative is to normalize your data because 9 times out of ten SQL is more efficient. In some cases I write code when it is just easier for me then writing very detailed long SQL. With that said you probably should normalize the data and use SQL. Now that I look at it that solution should be far easier
1) Build normalizing union queries qryNormalA and qryNormalB. Your data would then look like
AID Fld Val
1 A1 45
1 A2 42
1 A3 41
1 A4 36
2 A1 54
...
3 A4 10
do the same with b
2) Now simlpy join the queries where Val A = Val B, sort by AID
3) If there was no duplicates this would be easy. Do a group by query and return the AID, BID where the amount of found records = 4
But assume you have
AID 1 = 4 4 2 1
BID 7 = 4 3 3 3 2 2 1
So you would return
AID BID
1 A1 4 7 B1 4
1 A2 4 7 B1 4
So you would have to have in your where statement to return the min AID where AID = BID and A val = B Val
You are also going to get 2 records for the value 2 (1 record in A matches two records in B) But you can have more so I do not think you need to worry about it. Just return the IDs where the count is >= 4 |
|
|
VickyC (TechnicalUser) |
15 Jun 12 10:53 |
hi MajP
Thanks for your code and for the suggestion re normalized tables. (I kind of knew that was going to come up sooner or later :) ).
For what it's worth, here's the VBA solution I'd worked on. In tblB I added a column RecordMatch (integer, default = 0). Instead of deleting the matches as I'd originally intended, I just not which A_ID in tblA made the match. CODE'Identify records in tblB whose integer sets include the full integer sets of a records in tblAA.
' Repeats are allowed. Both sets in DESC order. tblA holds fewer integer values that tblB.
'
'eg: tblA: A_ID A1 A2 A3 A4 tblB: B_ID B1 B2 B3 B4 B5 B6 B7 RecordMatch
' 100 42 38 38 14 is fully contained INSIDE 234 44 42 41 38 38 38 14 100
' 101 42 38 38 14 is NOT INSIDE tblB record 555 42 41 38 14 14 12 11 0
Function IntSetA_Inside_IntSetB()
Dim rsA As DAO.Recordset, rsB As DAO.Recordset
Dim strTblA As String, strTblB As String
Dim ColMatchCount As Integer, B_StartCol As Integer, ChkIf_A_ColMatches_B As Integer
Dim A_FirstDataCol As String, A_LastDataCol As String, B_FirstDataCol As String, B_LastDataCol As String, B_NewStartCol As String
'@@@@@@@@@@@@@@@@@@ SET THESE BEFORE RUNNING FUNCTION IntSetA_Inside_IntSetB @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
strTblA = "tblA_TEST": strTblB = "tblB_TEST"
A_FirstDataCol = 1: A_LastDataCol = 4: B_FirstDataCol = 1: B_LastDataCol = 7 '(use 0 based index)
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Set rsA = CurrentDb.OpenRecordset(strTblA, dbOpenDynaset)
Set rsB = CurrentDb.OpenRecordset(strTblB, dbOpenDynaset)
Do Until rsA.EOF
rsB.MoveFirst
Do Until rsB.EOF
ColMatchCount = 0: B_NewStartCol = B_FirstDataCol
For ACol = A_FirstDataCol To A_LastDataCol
'If an A column has no match, then bail and go to next tblA record
ChkIf_A_ColMatches_B = 0
For BCol = B_NewStartCol To B_LastDataCol
'I there is a match...
If rsA(ACol) = rsB(BCol) Then
'when ColMatchFound = 4, we have a full match of A and B records
ColMatchCount = ColMatchCount + 1
ChkIf_A_ColMatches_B = ChkIf_A_ColMatches_B + 1
'if A1 finds its match in B3, say, then start looking A2's match at B4
B_NewStartCol = BCol + 1
'found a column match, so go to next value of A
Exit For
End If
Next BCol
'if an A value has no B match, then move on to next tblB record
If ChkIf_A_ColMatches_B = 0 Then Exit For
Next ACol
If ColMatchCount = A_LastDataCol - A_FirstDataCol + 1 Then '=4, in this sample
With rsB
.FindFirst "B_ID = " & rsB!B_ID
If Not .NoMatch Then
.Edit
'Identify the match. Or, get match count with... !RecordMatch = !RecordMatch + 1
!RecordMatch = rsA!A_ID
.Update
End If
End With
End If
rsB.MoveNext
Loop
rsA.MoveNext
Loop
End Function
This actually works well, but I'd still like to explore doing this in SQL using normalized tables, as you mentioned.
Thanks for all your assistance
Vicky
|
|
|
PHV (MIS) |
15 Jun 12 11:12 |
|
|
VickyC (TechnicalUser) |
15 Jun 12 19:46 |
hi PHV - I guess the rsB.Findfirst is not needed, but I just had recalled a caution from Allen Browne some time ago. If I remember, his point was that it is best to check for such a match because, otherwise, the code can appear to be working until we hit a case where the Find fails. I'm on thin ice here - does this make any sense? Vicky
|
|
|
PHV (MIS) |
15 Jun 12 20:39 |
But you already are on the row you want to update ! |
|
|
VickyC (TechnicalUser) |
15 Jun 12 21:45 |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One