Compare data between two recordsets / lookup recordset value in another recordset

[rizo98]

As the title suggets, I have created to recordsets (1: current year, 2: previous year)
I simply want to be able to do the following:
1-by-1 lookup an id/value in last year and pull the information back displaying it with Debug.Print.

from there I think i know how to write this data to an array.

[rizo98]

the code I currently have is as follows:

Function arraytest2()
 Dim db As DAO.Database
 Dim rst As DAO.Recordset
 Dim rst1 As DAO.Recordset
 Dim rst1filtered As DAO.Recordset
 
 Dim Rowcount As Long
 Dim FieldCount As Integer
 Dim i As Integer
 Dim j As Integer
 Dim arrCount() As Variant
 
 Set db = CurrentDb
 
 
 
 Set rst = db.OpenRecordset("qry_Market Share", dbOpenDynaset)
 Set rst1 = db.OpenRecordset("qry_Market Share1", dbOpenDynaset)
 Set rst1filtered = db.OpenRecordset("qry_Market Share1", dbOpenDynaset)
 
 
Rowcount = rst.RecordCount
FieldCount = rst.Fields.count
 
'set the dimensions for the array
ReDim arrCount(0 To Rowcount, 0 To 8)
 
 
rst.MoveLast
rst.MoveFirst
 
 
Do Until rst.EOF
 
rst1filtered.Filter = "Firmid = " & rst.Fields("Firmid").Value & ""
Set rst1filtered = rst1.OpenRecordset
 
 
 
        'write to the array
        arrCount(i, 0) = rst.Fields("FirmID")
        arrCount(i, 1) = rst.Fields("FirmName")
        arrCount(i, 2) = rst.Fields("Perc")
        arrCount(i, 3) = rst.Fields("Turnover")
        arrCount(i, 4) = rst.Fields("Total")
        arrCount(i, 5) = rst1filtered.Fields("FirmID").Value
        arrCount(i, 6) = rst1filtered.Fields("FirmName").Value
        arrCount(i, 7) = rst1filtered.Fields("perc").Value
 
 
            rst.MoveNext
        'see the array in the Immediate window
        Debug.Print rst.Fields("FirmID"), rst.Fields("FirmName"), rst.Fields("Perc"), rst1.Fields("FirmID"), rst1.Fields("FirmName"), rst1.Fields("perc")
         'Debug.Print i, arrCount(i, 0), arrCount(i, 1), arrCount(i, 2), arrCount(i, 3), arrCount(i, 4), arrCount(i, 5), arrCount(i, 6), arrCount(i, 7)
 
        i = i + 1
Loop

End Function

[ADezii]

Unless I am misinterpreting your request, create a Recordset directly from the Filter and display the results, as in:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intNumOfFields As Integer
Dim intFldCtr As Integer
 
'Fictitious Value to search for
Const conVALUE_TO_SEARCH As Long = 1
 
strSQL = "SELECT * FROM tblPreviousYear WHERE [ID] = " & conVALUE_TO_SEARCH
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
 
rst.MoveLast: rst.MoveFirst     'For accurate Record Count
 
intNumOfFields = rst.Fields.Count
 
With rst
  Do Until .EOF       'Return anything?
    For intFldCtr = 0 To intNumOfFields - 1
      Debug.Print .Fields(intFldCtr) & " | " & .Fields(intFldCtr).Value
    Next
      Debug.Print "--------------------------------------------------"      'Separator Bar
        .MoveNext
  Loop
End With
 
rst.Close
Set rst = Nothing

[rizo98]

Thanks for the reply. I want to search another recordset or array though. let me give you an example. I have a recordset called Athlete2013 and another recordset called Athlete 2012.
Looking at the below recordsets, I want to take Mo Farah from Recordset_Athlete2013 and search for him in Recordset_Athlete2012. Having found him, I want to to display his position. So as a final output I will have something like below. I hope this is clear. I want to be able to do this for each record in Recordset_Athlete2013.

Athlete    2013_position    2012_Position
Mo Farah    1    2
 
 
 
 
Recordset_Athlete2013
Athlete    2013_position
Mo Farah    1
 
Recordset_Athlete2012
Athlete    2012_Position
Mo Farah    2

[Rabbit]

If that's the result you wnat, there's no need for any VBA code. Just join the two recordsets on the Athlete.

[rizo98]

But if I just join the recordsets on the athletes then will that not jumble up the results? E.g. Mo Farah was the winner in 2013 and runner up in 2012. If I joined the recordsets would I not have Mo Farah's 2013 score and someone else's (2012 winner) score next to it?
ASo I would have as below. But Mo was in 2nd place in 2012 and not 1st.
Athlete 2013_position 2012_Position
Mo Farah 1 1


Also the recordsets do not have the same number of records.

[Rabbit]

Why would it jumble? As long as you join on the key fields, in this case Athlete, then the records will match up. If the recordsets don't have the same number of records, you can just use an outer join, be it left, right, or full outer.

[zmbd]

also sounds like the database isn't normalzied...> Database Normalization and Table Structures.

[ADezii]

@zmbd:
I feel as though you are correct about the Data not being Normalized.

I think the question here is Does each Athlete have a Unique Identifier that is consistent in each Table?

[rizo98]

I can do this by creating a query but i need to do it using vba. So i need to know the best method of going about it. By using recordsets, arrays or another method. The database is normalised. I have gathered the data for 2012 and 2013 using queries. I can create another query and do a right join on 2012 and get all corresponding records for 2012. However as i said i need to do this in vba. I have assigned 2013 query to a recordset called rst and 2012 query to another recordset called rst1 as shown in my code above. Now i need to a method that simply does the following.
For each record in recordset in rs lookup firmID in rst1. If exists then print the line using debug.print. If it doesnt exist them go to next record in recordset

[rizo98]

@Adezil
Simple answer is no. An athelete will have also raced in another country too. So in this case (if
we were to doit in a query) i will need to link on athlete id and country id to get the correct last year position. So i will need to create more than one join while looping the array or recordset

[ADezii]

It appears as though you wish to test every Record in 2013 against every Record in 2012 based on a [FirmID] Field, thereby creating a Cartesian Product. If my assumption is correct, than this can be accomplished by Nested Recordsets. If my assumption is not correct, than I give up (LOL). In any event see if the below Logic will accomplish what you need.

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tbl2013", dbOpenForwardOnly)  '2013 Data
Set rst1 = MyDB.OpenRecordset("tbl2012", dbOpenSnapshot)    '2012 Data
 
With rst
  Do While Not .EOF                         'Loop thru ALL in tbl2013
    Do While Not rst1.EOF                   'Loop thru ALL in tbl2012
      If ![FirmID] = rst1![FirmID] Then     'Match foound on [FirmID]
        Debug.Print ![FirmID]               'Print relvant 2013 Data
        Debug.Print ![Perc]                 '           "
        Debug.Print ![Turnover]             '           "
        Debug.Print ![Total]                '           "
        Debug.Print ![FirmName]             '           "
 
        Debug.Print rst1![FirmID]           'Print relvant 2012 Data
        Debug.Print rst1![FirmName]         '           "
        Debug.Print rst1![Perc]             '           "
      End If
        rst1.MoveNext                       'Next Record in 2012
    Loop
       rst1.MoveFirst                       'Move to First Record in 2012,
                                            'Getting ready for next Iteration
      .MoveNext                             'Next Record in 2013
  Loop
End With
 
'Clean up after yourself
rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing

[Rabbit]

Why do you "have" to do it in VBA?

[rizo98]

@ADezii
You are correct. Your code is basically what I need.
However in your code only matching records are printed. Unmatching records are not printed with just their 2013 data.
what is the way around it?

I think the below need to to be placed somewhere, but where

 
   If rst1.EOF Then
 
     Debug.Print ![FirmID], ![FirmName], ![Perc], ![Turnover], ![Total], rst1![FirmID], rst1![FirmName], rst1![Perc]
     End If
 

[rizo98]

@Rabbit
Needs to be done in VBA because there is more work / other work to be done in vba alongside

[ADezii]

If I am reading you correctly, we can make use of a Boolean Variable to let us know whether or not any Match(es) were found for each Record in the 2013 Data.

Dim blnMatchFound As Boolean
 
'********************** Code Intentionally omitted **********************
With rst
  Do While Not .EOF                         'Loop thru ALL in tbl2013
    Do While Not rst1.EOF                   'Loop thru ALL in tbl2012
      If ![FirmID] = rst1![FirmID] Then     'Match foound on [FirmID]
        blnMatchFound = True                'At least 1 Match Found
          Debug.Print ![FirmID]               'Print relvant 2013 Data
          Debug.Print ![Perc]                 '           "
          Debug.Print ![Turnover]             '           "
          Debug.Print ![Total]                '           "
          Debug.Print ![FirmName]             '           "
 
          Debug.Print rst1![FirmID]           'Print relvant 2012 Data
          Debug.Print rst1![FirmName]         '           "
          Debug.Print rst1![Perc]             '           "
      End If
        rst1.MoveNext                       'Next Record in 2012
    Loop
      'No Match(es) found for Iteration, print 2013 Data only
      If Not blnMatchFound Then
        Debug.Print ![FirmID], ![FirmName], ![Perc], ![Turnover], ![Total]
      Else
        'Match(es) found, must RESET for next Iteration of 2013 Data
        blnMatchFound = False
      End If
       rst1.MoveFirst                       'Move to First Record in 2012,
                                            'Getting ready for next Iteration
      .MoveNext                             'Next Record in 2013
  Loop
End With
'********************** Code Intentionally omitted **********************
 

[rizo98]

Nice one. This should get me started. Is this the best approach for doing something like this? Using a recordset or an array? What other ways could it be done in thats quick and effecient?

[rizo98]

Also many thanks for the help/support guys. Well approciated