debit and credit matching criteria

[BlakeW]

I have a table that contains both debits and credits, I would like to update the match field to a Y or N based on there being a corresponding debit or credit. In the example below I would like to have the 9 positive $5 entries match to the 9 oldest -$5 entries and update the match field to Y and leave the remaining 4 negative ones to N. I've done several queries and VB attempts but doesn't seem to work. Any suggestions would be appreciated, thank you.

MATCHED	ID_X	CAN	AMOUNT	SDATE
N	IX211455	1234	-$5.00	2014-11-17
N	IX2705	1234	-$5.00	2014-12-01
N	IX14804	1234	-$5.00	2014-12-08
N	IX27162	1234	-$5.00	2014-12-15
N	IX41717	1234	-$5.00	2014-12-22
N	IX211454	1234	-$5.00	2014-11-10
N	IX340220	1234	-$5.00	2015-01-05
N	IX211456	1234	-$5.00	2014-11-03
N	IX211457	1234	-$5.00	2014-11-24
N	IX340217	1234	-$5.00	2015-01-26
N	IX340218	1234	-$5.00	2015-01-19
N	IX340219	1234	-$5.00	2015-01-12
N	IX49827	1234	-$5.00	2014-12-29
N	IP274983	1234	$5.00	2015-01-01
N	IP274984	1234	$5.00	2015-01-01
N	IP274985	1234	$5.00	2015-01-01
N	IP10567	1234	$5.00	2014-12-01
N	IP274986	1234	$5.00	2015-01-01
N	IP10571	1234	$5.00	2014-12-01
N	IP10570	1234	$5.00	2014-12-01
N	IP10569	1234	$5.00	2014-12-01
N	IP10568	1234	$5.00	2014-12-01

[June7]

My suggestion is to not save this calculated value but to just calculate when needed, especially since the query and/or code to determine which records to update would have to do the calc anyway, why bother with the extra step of saving the value?

Why is it important to 'match' records as opposed to just calculating a balance?

[BlakeW]

Thanks for reply, the reason we need to 'match' instead of having a running balance is for investigation purposes. The investigation team looks at all the non-matched line items to determine if they are valid or put on a customer account in error. That's why I need to identify any non-matched ones.

[June7]

Your example data has the same $ amount for all records. What if there was a $20 record in the middle - it would be unmatched? Or would that $20 match to 4 -$5 records? You only want to match exact $ amount? I just don't understand the data well enough.

I can't see any query method to accomplish and expect the VBA would be complicated.

[BlakeW]

you are correct it would declare the 20$ as unmatched. So yes exact (opposite) matches only. I used the VBA code below to come really close but the table has about 80K records so it takes a while to run and in the end seemed to have matched a couple ones i didn't expect it to. The ID_X column above is only called ID in the actual database, hence the difference in the code to table. I'm still trying to adjust it so I get what I expected. I came on here looking for potential alternatives I might not have thought of more than anything. Thanks again for responding.

Private Sub Command1_Click()
On Error GoTo Err_Proc
'create a recordset for all nonmatched records
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_test WHERE MATCHED = 'N'")
If rs.RecordCount <> 0 Then
rs.MoveFirst
While Not rs.EOF
'layout each field from the recordset to form criteria for the new matched recordset
rsID = rs("ID")
rsCAN = rs("CAN")
rsAMOUNT = rs("AMOUNT")
'create new matched recordset by selecting top 1 matching record
rsSQL = "SELECT TOP 1 * " & _
"FROM tbl_test WHERE (((tbl_test.ID) > " & rsID & ") And ((tbl_test.CAN) = '" & rsCAN & "') " & _
"And ((tbl_test.MATCHED) = 'N') And (([tbl_test].[AMOUNT] + " & rsAMOUNT & ") = 0)) " & _
"ORDER BY tbl_test.ID"
'MsgBox (rsSQL) 'diagnostic msgbox
Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset(rsSQL)
'if there is a match, update the original and matched recordsets
If rs1.RecordCount <> 0 Then
'update matched recordset
rs1.Edit
rs1.Fields("MATCHED") = "Y"
rs1.Update

'update original recordset
rs.Edit
rs.Fields("MATCHED") = "Y"
rs.Update

End If
rs.MoveNext
Wend
End If
MsgBox ("WORK COMPLETE")

Exit_Proc:
On Error Resume Next
rs.Close
Set rs = Nothing
Set rs1 = Nothing
Exit Sub
Err_Proc:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Proc
End Sub

[ssanfu]

OK, where to start?????

The top two lines of EVERY code page should be

Option Compare Database
Option Explicit

"Option Explicit" requires variable declaration. This is good because you won't inadvertently type when "rs11" when you mean (ie "Fat Finger") "rs1". There were several undeclared variables.

You had the declaration for "Dim rs1 As DAO.Recordset" inside of a While Loop. I put all of my declarations at the top of the subroutine. Much easier to debug...
The recordset "rs1" was never explicitly closed.

--------------
I'm not sure I really understand your process, so here is what I did when I re-wrote your code. (the basics were there... just needed tweaking )

It sounds like you want to match positive and negative dollar amounts that are equal by the earliest (oldest) date. The "ID" doesn't matter. (poor choice for a field name, BTW)
As long as the dollar amounts match exactly, the "Matched" fields should be set to "Y".

Is this closer to what you want? (see dB)