Account Reconciliation Query 1

[sjck]

Account Reconciliation Query

I am trying to create clearing logic for a table. Unfortunately, I am a very new user to Access.

I have downloaded the account information from our computer system to perform account reconciliation. I would like to remove offsetting entries from the table to leave me with only open items.

I have a table similar to the following:

ID (Primary Key Assigned by Access during import)
Location
Customer
Date
System
Amount

Sample data:

ID Location Customer C Code Date System Amount
1 Denver 456 AB 11/12/2003 G1 50.00
2 Denver 456 AB 11/12/2003 G2 -50.00
3 Denver 456 AB 11/12/2003 G1 -50.00
4 Denver 456 AB 11/12/2003 G1 50.00
5 Denver 456 AB 11/12/2003 G2 -50.00
6 Denver 456 AB 11/12/2003 G1 50.00
7 Denver 456 AB 11/13/2003 G1 50.00


I would like to know how to create a query or more if needed, to obtain the following:

The data separated into 2 tables (Open Items and Offset) using the following logic

Location = Location
Customer = Customer
C Code = C Code
Date = Date
System ID <> System ID (more specifically G1 must offset with G2)
Amount = -Amount (The amount must add to 0)

Example of Results:
Merged Table
ID Location Customer C Code Date System Amount
1 Denver 456 AB 11/12/2003 G1 50.00
2 Denver 456 AB 11/12/2003 G2 -50.00
5 Denver 456 AB 11/12/2003 G2 -50.00
6 Denver 456 AB 11/12/2003 G1 50.00

Open Items
ID Location Customer C Code Date System Amount
3 Denver 456 AB 11/12/2003 G2 -50.00
4 Denver 456 AB 11/12/2003 G1 50.00
7 Denver 456 AB 11/13/2003 G1 50.00

The problem I am having is that I do not understand how to just pull offsetting items for example to obtain the above results. For example how to I limit the query to just finding 1 offsetting entry?

 

[BNPMike]

This is not easy. There are two properties of relational databases that you are contravening here. Firstly there is no concept of row order whereas your task is about matching pairs of rows. Secondly you have tables rather than relations. What I mean by that is if you ignore the Access-supplied ID (which doesn't have any real-World meaning) you have rows which are identical to other rows eg Row 1 and Row 6 in Merged table. SQL prefers relations which are tables which don't contain duplicates so it will have greater difficulty handling this.

Another problem I see is that you might have a series like this

1 Denver 456 AB 11/12/2003 G1 50.00
2 Denver 456 AB 11/12/2003 G2 -25.00
5 Denver 456 AB 11/12/2003 G2 -25.00

or even worse like this

1 Denver 456 AB 11/12/2003 G1 50.00
2 Denver 456 AB 11/12/2003 G2 -49.00
5 Denver 456 AB 11/12/2003 G2 -2.00

Your customer has cleared the 50 dollars but not at the boundary of any single transaction. I'm sure that happens in real life.

I can't see how you can avoid a lot of VBA programming here.

 

[Quehay]

BNPMike's right--if I understand you correctly you want to match compensatory transactions, e.g. where the debit = credit.

It's easy enough to do gross balance querying by account, but there is no transaction code by which to match intelligently, such as 120103D and 120103C, which would be ISO dates with a transaction type code tagged on.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[sjck]

Thank you for your responses, while I understand that there may exist the other type of series, I would like to eliminate the offsetting series. By eliminating these types of account initially, we will be able to reconcile the exceptions manually. In another account I was able to create the above logic in excel - however this account is much to large, over 300,000 transactions. (Additionally, This is a quick interim solution until this account is converted to be reconciled automatically, in our system.)

Please let me know if there is any query or series of queries that would allow me to remove 2 offsetting entries. I have tried to run the duplicate records query, however all possible duplicated are reported, not just ones where the amount net to zero.


I thought then I could run the unmatched query wizard against the original table and the duplicate query.

Do you have any other suggestions?

 

[JonFer]

No guarantees because I didn't try it but you could create a table with two Long Integer fields. Call one PosID and the other NegID. Each should have a unique index on it.

Create a new query that joins two instances of the table with the criteria you mention above but make sure one table only includes positive amounts. Make the query append the ID from the positive amount table to your new table's PosID field and similarly for the ID for the negative amount.

You should get records indicating which pair of records can be deleted from your original table. See how many records you get. You can verify you are only removing offsetting entries by doing a summary query that excludes the IDs in the new table. It should match a summary query of ALL records in the table.

In your delete query use this Condition:

Where ID In (Select PosID from Table) OR
ID In (Select NegID from table)

 

[BNPMike]

&quot;Create a new query that joins two instances of the table &quot;

I don't think this would work. For one you are assuming there are only two entries for any one day, and secondly that the matching entries actually occur on the same day.

Really what is needed here is some sort of eg order number. If you had that you could forget all problems of matching. All you would need to do would be to SUM all order numbers and select the ones the don't sum to zero.

 

[JonFer]

Multiple instances of matches for one day is okay. It is true that the query will produce output results for each combination but each ID can only be appended once to the PosID/NegID table because of the indexes.

The problem setup included the condition that the G1/G2 offsetting entries occur on the same day. Even if they were on different days, you could still find offsetting entries (although you'd probably have a lot more combinations).

An order number, like you mention, would help.

 

[verchung]

OK, so i deal with this problem everyday. I actually have created several different match passes based on the following logic. First I created two extra fields in the &quot;Open Items&quot; table...One called MatchDate, the other called MatchCode. I then created a query that went to that table and summed up all accounts with a NULL matchcode or matchdate (make sure you do a count field on the primary key). For one match pass I limit the &quot;matches&quot; where the sum of amount is = zero and the count is = to any even number( 2..4...6..etc). I then use an update query to update the match date and match pass column with the date and the &quot;match pass module&quot;. You then have the option of either leaving the matched in the table, deleting them, or moving them to another table. For another match pass, I match any amounts off where one debit = sum of many credits. In another I match one credit amount to many debits and so on and so forth. I learned that once you can figure out the queries (summing and counting), the rest is basic looping logic. Hope I helped..

 

[BNPMike]

verchung
I don't see how you can join items together if there are more than one on a day - you'll get multiple occurrences, a cross-product.

You say:
&quot;. First I created two extra fields in the &quot;Open Items&quot; table...One called MatchDate, the other called MatchCode. I then created a query that went to that table and summed up all accounts with a NULL matchcode or matchdate&quot;

Firstly how do you create Matchcode? Secondly if you have created it how can it then be null?

 

[sjck]

verchung,

Do you have a sample of the code for each of the &quot;match&quot; passes that you have mentioned? I do not completely understand the MatchCode and MatchDate process. How is this created and does it equals any code or date is the table)? Plus, I do not understand how to incorporate the summing and counting logic into my query.

Thank you for your help this seem to be exactly what I need by I am a little foggy on how to implement, as I am very new to Access.

Thank you again for you assistance.