Tough Accounting Issue - Can You Recommend a Solution?

[UNC_Access]

Hi!

I have the following two-column table (TABLE1):

Account, Difference
111, $30
222, $150
333, -$100
444, -$60
555, -$20

As you can see, the total of the differences adds to zero. This table implies that the following 5-line accounting entry needs to be made:

Account, Amount
111, -$30
222, -$150
333, $100
444, $60
555, $20

(The accounting entry is the same thing as TABLE1, except the amounts are reversed.)

My goal is to split this single, 5-line accounting entry into multiple smaller, 2-line accounting entries. I think I need to create a second table - TABLE2 - to house these 2-line entries.

Conceptually, I would create the entries in a cascading fashion:

1. Take the 1st and 2nd accounts in TABLE1:

Account, Amount
111,
222,

2. Assign the 1st account (111) the negative of its TABLE1 number:

Account, Amount
111, -$30
222,

3. Assign the 2nd account (222) the opposite number:

Account, Amount
111, -$30
222, $30

This is the first of the smaller 2-line accounting entries.

This should be appended to TABLE2.

4. Take the 2nd and 3rd accounts in TABLE1:

Account, Amount
222,
333,

5. Assign the 222 account the negative of:

a. the sum of the previous 222 entries and
b. the original amount

Account, Amount
222, - ($30 + 150) = -$180
333,

6. Assign the second account (333) the opposite number:

Account, Amount
222, -$180
333, $180

This should be appended to TABLE2

7. Repeat the process for all remaining accounts

TABLE2 would ultimately look like this:

Account, Amount
111, -$30
222, $30
222,-$180
333,$180
333,-$80
444,$80
444,-$20
555,$20

The trick is that the number of accounts in TABLE1 fluctuate from day-to-day. So the next day, this procedure would include accounts 666 and 777. The day after might only involve accounts 111,222, and 888. Etc.

How should I go about doing this in Access in an automated fashion?

Is this possible?

Any help is appreciated!

 

[Pat Hartman]

You can do pretty much whatever you want to do but you need to take off your Excel hat and put on your relational database hat. Position is meaningless. You need to describe how accounts are related to each other some way other than record order.

In the accounting applications I have worked with we frequently had tables that were used to split one accounting entry into many or take many and summarize them to one. Nothing was ever hard-coded.

 

[UNC_Access]

Thanks for the reply!

Yeah, your recommendation is accurate.

However, the accounts are not really related to one another. All of the accounts can thought of as separate, unrelated cash accounts, for example, and the accounting entries are necessary to conduct transfers.