Scan for close matches within Table (1 Viewer)

[MatthewH]

I'm looking to create a vba code that would search and find close matches within a table.

Sample Data is attached!!!

I would like to find a potential match based on all of these criteria but in opposite directions (aka a buy and a sell). If it's not there, I created a code to find it in another database which has a master of all of the data.
1) It MUST match on Trade ID (Mandatory criteria).
2) If it matches on 4 of the 5 next items, it is a match (Company, Quantity, Time, Manager Name, OPPOSITE directions).
3) I want it to just say in my last field (Comments) - "Match found - Broken on _____", with _____ being what's wrong.

In the example above, the first two would be a match broken on Time (12:05 vs 12:06). Nothing else would be a match in this document (MH156 trades are off by a lot of things so it wouldn't fit the criteria).

Anyone that can help me code this via VBA or a query would be a HUGE help!!

Thanks!!
M

 

[plog]

First, what does Opposite direction mean? I know nothing of your data, so it means nothing to me. How is B the opposite of S and vice versa? What other values are opposites?

With that said, you do this by a simple query and a custom function. For the query, you bring in 2 instances of your table and link them via Trade ID. I would bring down all fields from both instances then build your Comments field like so:

Comments: get_Comments(A1, A2, B1, B2, C1, C2, D1, D2, E1, E2)

(where A1 is Company from first instance of table, A2 is Company from second instance, B1 is Quantity from first instance of table, B2 is Quantity...)

For the custom function it would look like so:

Public Function get_Comments(in_A1, in_A2, in_B1, in_B2, in_C1, in_C2, in_D1, in_D2, in_E1, in_E2) As String
    ' sees how many of the lettered values match their counterparts, will return field it fails on

ret=""
' return value 

if (in_A1 <> in_A2) then ret="A Field" 
if (in_B1 <> in_B2) then ret&="B Field"
'  logic for all fields here

get_Comments= ret
End Function

 

[jdraw]

Further to plog's comments can you tell us in plain English (as you would tell an 8 year old) what is the business behind this. Give us an overview of what you are trying to do with a clear example.

 

[MatthewH]

Further to plog's comments can you tell us in plain English (as you would tell an 8 year old) what is the business behind this. Give us an overview of what you are trying to do with a clear example.

Ok I'm going to give my best explanation attempt.

Our system checks each trade against one another for our product to try finding a match. A buy must be matched to a sell and if they match on all of the criteria (price, time, quantity, etc), our system automatically links them up and stores them.
We pull in a file that has all the items that didn't match up. This is the sample DB I gave. I want to check each record against one another to see which ones are CLOSE and then tell us where it broke and what went wrong. Hence the comment section would say "Break on PRICE" or "Break on QUANTITY" as long as everything else links up. It's very important that Trade ID is the same though, as if that's not the same then the other fields don't matter.

Hope that helped explain it and someone can give me some useful queries or VBA code (I'm currently attempting most of this in a module but would love for queries to get it done, they're usually better from my experience.)

Thanks!!!!
M

 

[jdraw]

Matthew,

It could be that 99% of the readers are not in the same business as you. You understand the terminology since it is your issue. But to me and many readers, you description is what I call "jargonese". You make Trades (stocks, bonds, cars, horses?) "our Product"??
A buy must be matched to a sell....our system..links and stores them.
"It's very important that Trade ID is the same though"

Perhaps your description is perfectly clear to some in the forum. My recommendation is that you try again to describe your issue in a business context. What would someone want to know if you were asking them to solve "the issue"?
What would you want to know if someone asked you to create a database to solve a business problem//opportunity?

 

[MatthewH]

Matthew,

It could be that 99% of the readers are not in the same business as you. You understand the terminology since it is your issue. But to me and many readers, you description is what I call "jargonese". You make Trades (stocks, bonds, cars, horses?) "our Product"??
A buy must be matched to a sell....our system..links and stores them.
"It's very important that Trade ID is the same though"

Perhaps your description is perfectly clear to some in the forum. My recommendation is that you try again to describe your issue in a business context. What would someone want to know if you were asking them to solve "the issue"?
What would you want to know if someone asked you to create a database to solve a business problem//opportunity?

Okay so we sell paper on a large scale. Every paper trade has a Trade ID. This Trade ID comes up twice, one when we sell the paper (listed in our DB as a SELL) and another comes from the other company as a BUY order (listed in our DB as a BUY). We link these two up to ensure that we didn't put out orders that they didn't request and that all the details are correct. That we didn't over-charge, we sent the right amount, we sent it at the right date. That's why we include those fields and we ensure they match up. If they match up PERFECTLY, they're taken care of by our system and don't come into this file. This file is our "misfits", items that do not match up exactly or do not have a link yet (they may not have finished the purchase order on their side).

I was hoping to create a macro/query that would read all of these misfits and try to find a close match, stating what exactly is causing them to not be a perfect match. So if the price per ream of paper is $0.01 different, it'll fail to match them with our system but this macro would show that it found a potential match but the only difference is PRICE. We do this so that we can quickly reach out to the companies and remedy the issue to create a match.

So it would search every cell within the database and try to find one that would be a match to the current cell it is on (and would loop through the entire recordset).

I think this is a good explanation, if you have any questions again please post and I'll happily try answer them. I think the specifics will help (hence why I thanked you!), I really do hope this can be solved as this process manually takes us an hour or two and I believe it could be done in just a few minutes with a query or code.

Thanks!!!!!!!!!
M

 

[jdraw]

Much better!!
Now to help readers can you tell us what CLOSE means in your organization?

Do misfits always have a TradeID?
Do they always have some value in each field? That is, there are no NULL /empty fields involved?

Your sample database has all fields set up as Text data type. Is that the way the real database is set up?
Time is a reserved word in Access. Access supports a Date/Time datatype that would let you determine the difference in 2 times.

 

[MatthewH]

Much better!!
Now to help readers can you tell us what CLOSE means in your organization?

Do misfits always have a TradeID?
Do they always have some value in each field? That is, there are no NULL /empty fields involved?

Well CLOSE would be that the TradeID matches up perfectly but there's just one error in the other fields. The error can be a big error but as long as the other fields all line up we can identify where that error is. So if Price Quantity Time Trade ID and Direction are all exact matches but the Manager Name (initials in this case) are different then it will just tell us that the system we use did not pick up an exact match because of the Manager Name. That's a close match for us. It's one field off from being good in our system, so long as that one field isn't TradeID.

These fields I'm searching in (Price,Quantity,Time,TradeID,Manager Name, Direction) will always have values in them and every misfit will have a TradeID.

Glad I'm making progress explaining it

 

[MatthewH]

Any help is truly appreciated I'm stuck :/

 

[MatthewH]

Hope somebody is able to help soon... Any tips/help is appreciated!

 

[Frothingslosh]

jdraw or plog are probably working on it. Please keep in mind that what you're asking for help with is anything BUT straightforward to do, and we're all volunteers here. You'll get an answer, but it'll probably take some time - at least until people are able to tackle this at home rather than while they're at work.

 

[Cronk]

Also there is a difference between help meaning "I have a specific problem with the following bit of code", and help meaning "Please write all the code for me"

In addition, it is unclear to me in post #1 whether the opposite Direction ie buy/sell is mandatory "match based on all of these criteria but in opposite directions",
or it is one of the four optional ones cited in point (2)

 

[MatthewH]

Also there is a difference between help meaning "I have a specific problem with the following bit of code", and help meaning "Please write all the code for me"

In addition, it is unclear to me in post #1 whether the opposite Direction ie buy/sell is mandatory "match based on all of these criteria but in opposite directions",
or it is one of the four optional ones cited in point (2)

Well the problem I'm particularly having is creating a recordset that skips the current record when looking for matches but searches the rest of the database.

The idea I came up with so far that seemed to work was to set a BucketCount and assign each field a value. So if it matches on Price it has 1 point. If it matches on Quantity, another. Matching on TradeID is worth 3. If it passes all the criteria it should be a total of 10. If it matches all but one (non Trade-ID), it would score a 9 and I have an if statement after each one to assign a string to the missing value if it doesn't match. If it scores below a 9, it moves to the next record.

The only problem is that I'm currently going through each record in the recordset when doing this so it's kind of like a loop of a recordset within a loop of a recordset and I think it's finding the same record and saying it's a match. How do I go about fixing that and not identifying the current record when checking through the entire recordset?

If anyone has another approach to this problem at hand, please let me know. I worked all night just coming up with that idea above and I'm sure there are much better ways to approach it.

 

[jdraw]

Matthew,

I have a rough solution based on your earlier posts. I did add a couple of records for testing.
It does not weight matches. It just checks for exact matches and awards 10 points for each match.
I took your data and added an autonumber field so each record could be uniquely identified.
Each record is compared with all others, assigned points and written to a table(Results).
A query reads the Results and ranks records based on point score.

'---------------------------------------------------------------------------------------
' Procedure : closeMatch
' Author : mellon
' Date : 09-May-2017
' Purpose : To rank misfit records in hopes of manually assigning a match.
'
' General logic
' Assign an autonumber to each incoming record to uniquely identify every record
' Order the records in the incoming table by Trade ID ascending
' take each record in turn and compare it with other uncompared records
'
' with 8 records (28 comparisons)

'compare 1 with 2-8
'compare 2 with 3-8
'compare 3 with 4-8
'compare 4 with 5-8
'compare 5 with 6-8
'compare 6 with 7-8
'compare 7 with 8
' assign a point value based on the number of matching fields for each comparison
'
' Write the record ids being compared and their comparison score to a table (Results)
' Sort the Results based on descending scores.
'-------------------------------------------------------------------------------------------------

This approach uses your Table1 and some vba code.

??Question for you??

Does your table1 in your earlier post represent your real data or was it a made up example?
I will post the code based on your reply. But whether or not it is useful depends on your data and your familiarity with vba.

I will be away from computer for about 5 hours.

Sample output from queryScore order)

Score	CompRecID	WithRecID	Timestamp
60	10	3	09-May-2017 9:19:46 AM
50	8	7	09-May-2017 9:19:46 AM
50	6	1	09-May-2017 9:19:46 AM
50	6	2	09-May-2017 9:19:46 AM
50	6	5	09-May-2017 9:19:46 AM
50	7	6	09-May-2017 9:19:46 AM
50	2	1	09-May-2017 9:19:46 AM
40	5	2	09-May-2017 9:19:46 AM
40	7	1	09-May-2017 9:19:46 AM
40	8	2	09-May-2017 9:19:46 AM
40	9	5	09-May-2017 9:19:46 AM
30	10	9	09-May-2017 9:19:46 AM
30	3	6	09-May-2017 9:19:46 AM
30	10	6	09-May-2017 9:19:46 AM
30	3	4	09-May-2017 9:19:46 AM
30	4	9	09-May-2017 9:19:46 AM
30	8	6	09-May-2017 9:19:46 AM
30	9	6	09-May-2017 9:19:46 AM
30	8	1	09-May-2017 9:19:46 AM
30	7	5	09-May-2017 9:19:46 AM
30	7	2	09-May-2017 9:19:46 AM
30	5	1	09-May-2017 9:19:46 AM
20	3	9	09-May-2017 9:19:46 AM
20	10	2	09-May-2017 9:19:46 AM
20	3	7	09-May-2017 9:19:46 AM

 

[static]

This assumes there are only 2 records to check. Ignores anything else.

ID MH122 12:05 does not match 12:06
ID MH156 JCD does not match MHT
ID MH156 150 does not match 199
ID MH156 12:06 does not match 12:07
ID MH156 JC does not match JJ
ID MH156 B matches B

Private Enum t1rec
    id
    comp
    qty
    tm
    mgr
    dirxn
End Enum

Private Sub Command0_Click()
    Dim t1()
    ReDim t1(1, t1rec.dirxn)
    
    Dim i As Byte
    With CurrentDb.OpenRecordset("select [Trade ID],count(1) from table1 group by [Trade ID] having count(1)=2")
        Do Until .EOF
            With CurrentDb.OpenRecordset("select * from table1 where [Trade ID]='" & .Fields("Trade ID") & "'")
                Do Until .EOF
                    For j = 0 To t1rec.dirxn
                        t1(i, j) = .Fields(j)
                    Next
                    i = i + 1
                    .MoveNext
                Loop
                .Close
                i = 0
            End With
            .MoveNext
            
            For j = 0 To t1rec.mgr
                If t1(0, j) <> t1(1, j) Then
                    Debug.Print "ID "; t1(0, 0), t1(0, j), "does not match", t1(1, j)
                End If
            Next
            If t1(0, t1rec.dirxn) = t1(1, t1rec.dirxn) Then
                Debug.Print "ID "; t1(0, 0), t1(0, t1rec.dirxn), "matches", t1(1, t1rec.dirxn)
            End If
        Loop
        .Close
    End With
End Sub

 

[jdraw]

Matthew,

If you are still monitoring this thread, attached is my test database. It uses the table structure of Table1 as you provided in earlier post. I added an autonumber to each record in your table to get a unique identifier for each record.
This routine processes each record against other non processed records as mentioned in my previous post. I have added weighting factors 3 for Trade id match and 1 for every other matching field in the record comparisons.

This database has a FormStart --to read and open the Processing Form -frmRunMatches.
frmRunMatches does the bulk of the work and outputs a table Results that shows the Scores for each comparison. It displays the Score and Record Ids for each comparison. It then opens a query in which you Enter a Score value, and it will display comparison records whose Score is greater than the value you Entered.

I'm sure there are many other solutions.
Hope it is useful.

Good luck.

 

[jdraw]

Here is a more complete database with form(s) interface.
I hope it's useful.
I welcome any feedback.
Good luck.