Matching two fields across Records in Table

[Guest]

Hi,

I've a table with six fields,
AutoNum(PK), AccountNum, PostDate, Debit, Credit, ReconStatus.

The debits are reconciliatory i.e. every originating debit has a
corresponding credit at a later date.

The datasheet view is something like this:
AutoNo AccountNo PostDate Debit Credit ReconStatus
-------- ------------ ---------- ------ ------- --------------
1 111 09/07/06 600 Reconciled
2 111 10/07/06 600 Reconciled
3 111 10/07/06 750 Unreconciled

Now, the problem is I have more than 100000 records that have to be uploaded
into my db, and I want to programmatically decide which debit entries are
reconciled i.e. have a corresponding credit(update ReconStatus).

The criteria for this are really simple:If two records have the same
AccountNum and Debit and Credit amounts are equal, they are deemed to be
reconciled. Any ideas ?

Thanks for any help.

 

[kingston via AccessMonster.com]

Create an update query with two instances of the table. Link AccountNo to
AccountNo and Debit to Credit. Update ReconStatus in every instance. The
danger is that you may have multiple instances of the same account number and
the same debit and credit amounts.

 

[Guest]

I have some concern about your logic. The basic rules are pretty straight
forward, but the problem I see is that with 100,000 records, it is highly
likely you will have some debit amounts which are identical. How can you be
sure you are matching the correct credit record for that debit record? This
problem not withstanding, the following will satisfy your rules as posted.
This will take some VBA recordset processing. The code below is totally
untested air code, but the basic concept should hold. You will, of course,
have to change the naming to suit your database.

Dim rstPrimary as Recordset
Dim rstLookUp as Recordset
Dim dbf As Database

Set dbf = CurrentDb
Set rstPrimary = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Set rstLookup = dbf.OpenRecordset("MyTableName", dbOpenDynaset)

Do While NOt rstPrimary.EOF
rstLookup.FindFirst "[AccountNo] = '" & rstPrimary![AccountNo] & _
[Credit] = " & rstPrimary![Debit] & _
" And [ReconStatus] = 'Unreconciled'"
If rstPrimary![Debit] <> 0 Then
If Not rstLookup.NoMatch Then
rstPrimary.Edit
rstPrimary![ReconStatus] = "Reconciled"
rstPrimary.Update
rstLookup.Edit
rstLookup![ReconStatus] = "Reconciled"
rstLookup.Update
End If
End If
rstPrimary.MoveNext
Loop

rstPrimary.Close
rstLookup.Close
Set rstPrimary = Nothing
Set rstLookup = Nothing
Set dbf = Nothing

 

[Guest]

Thanks Klatuu,

I've also tried the method of opening two recordsets of the same table, but
something was missing. Your code should work. Your concern about some records
being similar is right. But, this is legacy data being uploaded into my db
and I'm only concerned with whatever records are remaining unreconciled. I'll
fine tune from hereon.
--
Sreedhar

I have some concern about your logic. The basic rules are pretty straight
forward, but the problem I see is that with 100,000 records, it is highly
likely you will have some debit amounts which are identical. How can you be
sure you are matching the correct credit record for that debit record? This
problem not withstanding, the following will satisfy your rules as posted.
This will take some VBA recordset processing. The code below is totally
untested air code, but the basic concept should hold. You will, of course,
have to change the naming to suit your database.

Dim rstPrimary as Recordset
Dim rstLookUp as Recordset
Dim dbf As Database

Set dbf = CurrentDb
Set rstPrimary = dbf.OpenRecordset("MyTableName", dbOpenDynaset)
Set rstLookup = dbf.OpenRecordset("MyTableName", dbOpenDynaset)

Do While NOt rstPrimary.EOF
rstLookup.FindFirst "[AccountNo] = '" & rstPrimary![AccountNo] & _
[Credit] = " & rstPrimary![Debit] & _
" And [ReconStatus] = 'Unreconciled'"
If rstPrimary![Debit] <> 0 Then
If Not rstLookup.NoMatch Then
rstPrimary.Edit
rstPrimary![ReconStatus] = "Reconciled"
rstPrimary.Update
rstLookup.Edit
rstLookup![ReconStatus] = "Reconciled"
rstLookup.Update
End If
End If
rstPrimary.MoveNext
Loop

rstPrimary.Close
rstLookup.Close
Set rstPrimary = Nothing
Set rstLookup = Nothing
Set dbf = Nothing

Hi,

I've a table with six fields,
AutoNum(PK), AccountNum, PostDate, Debit, Credit, ReconStatus.

The debits are reconciliatory i.e. every originating debit has a
corresponding credit at a later date.

The datasheet view is something like this:
AutoNo AccountNo PostDate Debit Credit ReconStatus
-------- ------------ ---------- ------ ------- --------------
1 111 09/07/06 600 Reconciled
2 111 10/07/06 600 Reconciled
3 111 10/07/06 750 Unreconciled

Now, the problem is I have more than 100000 records that have to be uploaded
into my db, and I want to programmatically decide which debit entries are
reconciled i.e. have a corresponding credit(update ReconStatus).

The criteria for this are really simple:If two records have the same
AccountNum and Debit and Credit amounts are equal, they are deemed to be
reconciled. Any ideas ?

Thanks for any help.