UNC_Access
Registered User.
- Local time
- Today, 00:19
- Joined
- Oct 24, 2012
- Messages
- 42
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!
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!