Solved Calculate Matrix Repeat

[lukets123]

Good Morning.

Please, I need to perform a matrix count within Access, I am leaving attached the model of my tables in Excel.

I need to convert my Excel formulas into Access. Formulas are in columns Q, R, S, T and U of the "Result" tab

Below is the link to download the Excel model and tables in Access

 

[MajP]

Can you explain the formula? For example Repeat 11 for ID 1 is 150. Does that mean that pattern 2 to 25 matches 11 items of the series 150 times?

 

[lukets123]

Can you explain the formula? For example Repeat 11 for ID 1 is 150. Does that mean that patter 2 to 25 matches 11 items or the series 150 times?

@MajP

I will continue considering ID 1 for the explanation, but the rule is the same for everyone.

The formula takes the sequence of ID 1 (Results), and verifies how many values are equal in the Base table (ID 1), if we compare both lines we will have that 11 values are equal, being: 2 3 5 6 10 11 14 16 18 24 25

So if we have 11 equal values, we store 1 in the "Q" column. Thus, the value of 150 indicates that we had 150 times at least 11 equal values between ID 1 and all the rows in the Base table.

This is repeated for all rows and all columns.

 

[MajP]

I read that as any 11. If it has more than 11 does it get rejected from the count? I see repeat 14 as 0 but repeat 15 as 1. Is that a mistake or are there no lines with exactly 14?

 

[lukets123]

I read that as any 11. If it has more than 11 does it get rejected from the count? I see repeat 14 as 0 but repeat 15 as 1. Is that a mistake or are there no lines with exactly 14?

@MajP

You must store, if the result is 11, 12, 13, 14 or 15, separating them into 5 new columns.

I redid the calculation in a simpler way in a new excel, and we really don't have any combination that has 14 equal values between the tables and we only have 1 time that the 15 numbers are repeated.

Attached, I leave the new excel file, the new calculation is highlighted in green.

 

[MajP]

This appears to work. I did this brute force. I got the base recordset and each result. I turned both into an array (much much faster than working with a recordset). I looped the base row cols and compared to the result column by column. I counted the matches. Any sql solution would be labor intensive if even possible.

  Dim aResult As Variant
  Dim aBase As Variant
  Dim rsResult As DAO.Recordset
  Dim rsBase As DAO.Recordset
  Dim i As Integer
  Dim j As Integer
  Dim k As Integer

  Dim count As Integer
  Dim TotalCount As Integer
  Set rsResult = CurrentDb.OpenRecordset("Select * from Result where ID = " & ID)
  Set rsBase = CurrentDb.OpenRecordset("Select * from Base")
  If Not rsBase.EOF Then
    rsBase.MoveLast
    rsBase.MoveFirst
  Else
    MsgBox "No Base records returned"
  End If
  If Not rsResult.EOF Then
    rsResult.MoveLast
    rsResult.MoveFirst
  Else
    MsgBox "No results returned"
  End If
  aResult = rsResult.GetRows(rsResult.RecordCount)
  aBase = rsBase.GetRows(rsBase.RecordCount)
' Debug.Print UBound(aBase, 2) & " Base count"
  For i = 0 To UBound(aBase, 2) 'Loop base rows
   For j = 1 To UBound(aBase, 1) 'Loop columns  this is backward column, row. First item is ID
     For k = 1 To UBound(aResult)
      ' Debug.Print aResult(k, 0) & "result " & aBase(j, i) & "base"
       If aResult(k, 0) = aBase(j, i) Then count = count + 1
     Next k
     'Debug.Print count & " Count"
     'If count >= Matches Then Exit For
   Next j
     If count = Matches Then TotalCount = TotalCount + 1
     count = 0
     'Debug.Print "total count " & TotalCount & " " & i
  Next i
  GetMatches = TotalCount
End Function

The query looks like this.

SELECT result.id,
       Getmatches([id], 11) AS Match11,
       Getmatches([id], 12) AS Match12,
       Getmatches([id], 13) AS Match13,
       Getmatches([id], 14) AS Match14,
       Getmatches([id], 15) AS Match15
FROM   result;

Results
Query1 Query1

ID	Match11	Match12	Match13	Match14	Match15
1​	150​	27​	4​	0​	1​
2​	139​	28​	3​	0​	1​
3​	120​	23​	2​	0​	1​
4​	135​	39​	6​	0​	1​
5​	155​	31​	2​	0​	1​

This is not super efficient as a function. If doing this with a mutch larger result set, then you will be better off writing to a Temp table. This takes about 1-2 seconds to run on a pretty fast machine.

My question was for Match11 do you count 12,13,14,15 matches. The answer is No. Must only be 11.

 

[MajP]

This will write to a table

Public Sub WriteCounts()
  Dim i As Integer
  Dim id As Integer
  Dim total As Integer
  Dim rsResults As DAO.Recordset
  Dim strSql As String
  CurrentDb.Execute "qryClearCounts"
  Set rsResults = CurrentDb.OpenRecordset("Select * from Result")
  Do While Not rsResults.EOF
    id = rsResults!id
    strSql = "Insert INTO TABLECOUNTS (ID) VALUES (" & id & ")"
    CurrentDb.Execute strSql
    For i = 11 To 15
      total = GetMatches(id, i)
      'Debug.Print total
      strSql = "Update TableCounts SET MATCH" & i & " = " & total & " WHERE ID = " & id
      'Debug.Print strSql
      CurrentDb.Execute strSql
    Next i
    
    rsResults.MoveNext
  Loop
End Sub

If you have a big results table this may be better. If you do this often you may need to compact your db periodically since you are deleting and writing to the table.

 

[Gasman]

Crossposted @ 12:32 today https://www.accessforums.net/showthread.php?t=83292

 

[lukets123]

This will write to a table

Public Sub WriteCounts()
  Dim i As Integer
  Dim id As Integer
  Dim total As Integer
  Dim rsResults As DAO.Recordset
  Dim strSql As String
  CurrentDb.Execute "qryClearCounts"
  Set rsResults = CurrentDb.OpenRecordset("Select * from Result")
  Do While Not rsResults.EOF
    id = rsResults!id
    strSql = "Insert INTO TABLECOUNTS (ID) VALUES (" & id & ")"
    CurrentDb.Execute strSql
    For i = 11 To 15
      total = GetMatches(id, i)
      'Debug.Print total
      strSql = "Update TableCounts SET MATCH" & i & " = " & total & " WHERE ID = " & id
      'Debug.Print strSql
      CurrentDb.Execute strSql
    Next i
   
    rsResults.MoveNext
  Loop
End Sub

If you have a big results table this may be better. If you do this often you may need to compact your db periodically since you are deleting and writing to the table.

My database is huge.

I send for you the real version to check, i'm trying to running this Module but not running.

Follow the link to download the database:
we.tl/t-ZjolYcJH6J

 

[MajP]

Does this run in Excel with 3M results in a reasonable time? Of have you tried. Like I said this was a brute force method so there may be an efficient way. If Excel is lightening fast then the solution may be to store data in Access and run in Excel. You might be able to use the Access .worksheetfunction to run this using the excel function.

I ran this with 1000 records and it took 3 minutes. So if my math is correct this would take about 6 days to run.

There are a few things that can be done that could make it faster, maybe get it down to an 8th of the time. Still probably still not going to get you what you need doing this in this approach.

For example I loop all rows and columns in the base. But if looking for a 1 you only need to look in column 1. For a 3 columns 1 to 3. For 7 search 2 to 7.


Is this a one time thing? Where you just add records to it, or are you getting batches of 3m data to run? I can work on greater efficiency, but if this is not something you could do in batches or run for a long time then some other optimization is needed.

By the way, what is the point of this? I can run over night to see how far it gets.

 

[MajP]

Anyways here is the first 1000. Can you verify they are correct?

 

[lukets123]

Anyways here is the first 1000. Can you verify they are correct?

Does this run in Excel with 3M results in a reasonable time? Of have you tried. Like I said this was a brute force method so there may be an efficient way. If Excel is lightening fast then the solution may be to store data in Access and run in Excel. You might be able to use the Access .worksheetfunction to run this using the excel function.

I ran this with 1000 records and it took 3 minutes. So if my math is correct this would take about 6 days to run.

There are a few things that can be done that could make it faster, maybe get it down to an 8th of the time. Still probably still not going to get you what you need doing this in this approach.

For example I loop all rows and columns in the base. But if looking for a 1 you only need to look in column 1. For a 3 columns 1 to 3. For 7 search 2 to 7.


Is this a one time thing? Where you just add records to it, or are you getting batches of 3m data to run? I can work on greater efficiency, but if this is not something you could do in batches or run for a long time then some other optimization is needed.

By the way, what is the point of this? I can run over night to see how far it gets.

No need to leave it running all night, you already helped me a lot

In Excel and Access I'm letting it run for hours but I don't finish it, but now the reason is explained, I need 6 days to update all the information.

Anyway thank you very much, I will divide the database into smaller parts to perform the task,

 

[MajP]

I can set up the code so you can run X records at a time and start from the last ID that was processed. This way if it dies then just start from where you left off.
Like I said, I can make it faster maybe about 1/4 of what it does now. If the base is not going to change.
Query2 Query2

MinOfLine 1	MaxOfLine 1	MinOfLine 2	MaxOfLine 2	MinOfLine 3	MaxOfLine 3	MinOfLine 4	MaxOfLine 4	MinOfLine 5	MaxOfLine 5	MinOfLine 6	MaxOfLine 6	MinOfLine 7	MaxOfLine 7	MinOfLine 8	MaxOfLine 8
1​	6​	2​	9​	3​	11​	4​	12​	5​	14​	6​	15​	7​	17​	8​	18​

I can apply the rule.
Only search for a 1 in line 1, only for a 2 in 1 and 2, only for 7 in 2 to 7.

So this version is set up if you run the Execute it will process the next x records. You can change that X. For demo purpose I have 100 set.

Public Sub Execute()
  MsgBox WriteCounts(100)
End Sub

Do not even try to do this with the query. You probably could not even do 50 results.

I will try applying the above logic to see what that does for speed.

 

[lukets123]

I can set up the code so you can run X records at a time and start from the last ID that was processed. This way if it dies then just start from where you left off.
Like I said, I can make it faster maybe about 1/4 of what it does now. If the base is not going to change.
Query2 Query2

MinOfLine 1	MaxOfLine 1	MinOfLine 2	MaxOfLine 2	MinOfLine 3	MaxOfLine 3	MinOfLine 4	MaxOfLine 4	MinOfLine 5	MaxOfLine 5	MinOfLine 6	MaxOfLine 6	MinOfLine 7	MaxOfLine 7	MinOfLine 8	MaxOfLine 8
1​	6​	2​	9​	3​	11​	4​	12​	5​	14​	6​	15​	7​	17​	8​	18​

I can apply the rule.
Only search for a 1 in line 1, only for a 2 in 1 and 2, only for 7 in 2 to 7.

So this version is set up if you run the Execute it will process the next x records. You can change that X. For demo purpose I have 100 set.

Public Sub Execute()
  MsgBox WriteCounts(100)
End Sub

Do not even try to do this with the query. You probably could not even do 50 results.

I will try applying the above logic to see what that does for speed.

Thank you very much!

 

[MajP]

I played with the searching for the number only within the feasible columns. It did not seem to make a significant difference. There is another thing that have not tried.
If I am searching for say 11 matches by column 5 I need at least 1, by column 6 I need at least 2... That should speed things up. If you need 15 you are kicking out if not found after 1 column

 

[MajP]

So I added that fix to kick out early and it still took 6 minutes for 3K records. If I do the math correct that is still 4 days for 3M records. So these razor thin improvement, are not making a big diff. But hey that is 2 days. With these changes are more opportunities I screwed something up.