Help in comparision of multiple values to multiple values

[carpet]

I need to compare 2 sets of vales obtained from multiple tables and based on mulitple criteria.

I have up to 100 values in set A (Named as T1, T2, etc, T100) and another potential 100 values in set B (Named T1a, T2a, etc, T100a).

I need to compare each value in set A against each value in set B and the only way I can think of to do it is

If T1 = T1a then
TAnd1=T1
Elseif T1 = T2a then
TAnd1=T1
Elseif etc
TAnd1=T1
End if

If T2 = T1a then
TAnd2=T2
Elseif T2 = T2a
TAnd2=T2
etc
End if

Which produces a set of TAnd values (values in both set A and set B) which I can then string together and produce one record set which has both values from A and B. Also need to eventually be able to have up to 10 different recordsets in the comparison but once I've don I can probably get the rest.

Can anyone suggest a succinct way to do this? I'm flummoxed and my way is horrible (exceedingly long repetitive code). All it is an AND function really...


Thanks

 

[pbaldy]

I'm not really clear on what you're trying to do, but it sounds like 2 For/Next loops might be the answer (psuedo-code):

For x = 1 to 100
  For y = 1 to 100
    If Tx = Tya Then
      'whatever
    End If
  Next y
Next x

 

[carpet]

I may have missed some VBA basics (wouldn't be the first time!) but I think that would just compare, for example, "T1" and "T1a" rather than the values assigned to each variable.

instead I need, for instance, if

T1 = 875634
T2 = 13452
and
T1a = -543234
T2a = 875634

to pull the T2a value out since T1 and T2a are equal.

Sorry I'm not being clearer - it all makes sense in my head but that doesn't always translate! Cheers

 

[pbaldy]

Sorry, I missed that there was a reply. That would compare every value in A against every value in B. As noted, that was just "pseudo-code". It might help if you posted the actual table and field names.

 

[WayneRyan]

Carpet,

The problem is that you are trying to compare columns. I don't know exactly
what you're trying to do here.

What if there's a matching column?
How should that be registered?

I think the columns in your data should be individual rows.
What makes T57 different to T72? Each column has a "unique" meaning.

Other than the usual normalization talk, I'll assume that you have each set
of data in recordsets.

Dim i As Integer
For i = 1 To 100
      If rst.Fields("T" & CStr(i)) = rst.Fields("T" & CStr(i) & "a") Then
         '
         ' I have no idea what you do on a match.
         '
         MsgBox "Match"
   Next i

But, if you are trying to see if "T37" matches "T67a" then you really had
better split those columns of data --> into rows.

Each of your current columns has an attribute that can identify its new row!

hth,
Wayne

 

[carpet]

Thanks for your help chaps but for some reason this is proving to be tricky.

If I use pbaldy's method then it compares the string "Tx" to the string "Tya" instead of the values. However if I string the identifyer together, as in "T" & x and "T" & y "a", print the string to the immediate window and then hover over the printed, during a break then T1, T2 etc are assigned to their numerical values.

This operation is a teeny part of a much bigger automated database and so offering you tables etc would probably just confuse matters.

WayneRyan: I'm really just comparing two sets of data to see if a value (any of the values) from one set is also in the other set. I can get this to work by stringing one set of values together and searching in the string with each data in the other set but it is very limiting in what I can do next.

I feel like I'm really close but missing a piece of the puzzle...

 

[pbaldy]

Like I said, that was simply "pseudo-code". It was not intended to be a finished product. If you concatenate the loop variables with the actual names and get the necessary values, how is it not working for you? If you can't post the actual tables, how about a small sample that isolates this part of the problem? I may be misunderstanding the goal, but I feel that the loop will solve the problem.

 

[tranchemontaigne]

Are the values in 'Set_A' and 'Set_B' static, or are they expected to be updated over time? If tyey are expected to be updated over time, then I would store the values in tables and create two recordsets (one for 'Set_A' and another for 'Set_B') and use PBaldy's method.

Here is a raw code sample. Please note that though this snippet has not been tested, it does demonstrate the concept as I would apply it.

...
dim rstA as ADODB.recordset
dim rstB as ADODB.recordset
dim strSQL_A as string
dim strSQL_B as string
dim intRecordCount as integer
...
 
 
strsql_A = "SELECT CodeValue_A from tbl_Set_A;"
strsql_B = "SELECT CodeValue_B from tbl_Set_B;"
 
set rstA = New ADODB.recordset
rstA.open strSQL_A, currentproject.connection, adOpenForwardOnly, adLockOptimistic
 
set rstB = New ADODB.recordset
rstB.open strSQL_B, currentproject.connection, adOpenForwardOnly, adLockOptimistic
 
'check for empty recordsets
  intRecordCount  = 0
  do until rstA.EOF
    intRecordCount   = intRecordCount   + 1
    rstA.movenext
  loop
 
  if recordcount = 0 then exit function
 
  intRecordCount  = 0
  do until rstB.EOF
    intRecordCount   = intRecordCount   + 1
    rstB.movenext
  loop
  if recordcount = 0 then exit function
 
'check for matches
rstA.movefirst
rstB.movefirst
 
do until rstA.EOF
  do until rstB.eof
    if rstA.[CodeValue_A].value = rstB.[CodeValue_B].value then
 
      'insert code here to do something with match matches
      'if nothing else, you could call a docmd.runsql command
      'to write the successful match to a report extract table
 
    end if
    rstB.movenext
  loop
 
  rstA.movenext
loop
...

________
TOYOTA TF102 HISTORY

 

[datAdrenaline]

If the tables are structured in which the values to be compared are ROWS and not columns, then a simple INNER JOIN on the common fields will provide you a resultant set that has the values that are common to both tables ..

SELECT tblA.*, tblB.*
FROM tblA INNER JOIN tblB ON tblA.Field1 = tblB.Field2 AND tblA.Field2 = tblB.Field2

 

[carpet]

Thanks so much all - with your help and some banging of head against a brick wall I managed to get this to work with recordsets and loops eventually!