G
geskerrett
I am hoping someone can get me pointed in the right direction as I seem
to be drawing a blank on the best method of solving this problem.
I have the task of recreating a financial statement printing
application from an existing accounting solution. It is not an "SQL"
based system, so file structures don't seem to lend themsleves easily
to MS-Access, joins etc -- or atlaeast not simply !
In this system, there is a transaction table and a summary table and it
is the summary table that is used for reporting and the source of my
puzzlement.
A simplfied structure of the summary table would be;
AcctNumber
Period (mm/yy)
PeriodValue_Current
PeriodValue_1mth
PeriodValue_2mth
PeriodValue_3mth
PeriodValue_4mth
..... etc
PeriodValue_150mth
The values are the 'ending' balances of the accounts, the last time the
account was posted to. Do the value of "period" is the last accounting
period that that specific account was posted to and the value of
"current period" is value at that time. All other values are
"relative" to the Period value.
So the data might look like this;
1000, 05/06, 100.00, 200.00, 200.00, 300.00 ....
2000, 04/06, 200.00, 100.00, 300.00, 400.00 ...
3000, 02/06, 300,00, 200.00, 100.00, 500.00...
The challenge is that as the records are processed, I "think" the
values need to be "shifted" to reflect the period the user is running
the report for.
In other words using the same data as above, if I wanted to run a
financial statement for the current period (05/06), I would need to
alter the data to look like this;
1000, 05/06, 100.00, 200.00, 200.00, 300.00 ....
2000, 05/06, 0.00, 200.00, 100.00, 300.00, 400.00 ...
3000, 05/06, 0.00, 0.00, 300,00, 200.00, 100.00, 500.00...
Note that the values for 2000 and 3000 have shifted to reflect the
values relative to the run date and their last updated period. ie. the
value of the current period of 05/06 for acct 2000 is 0.00 as nothing
has been posted to it.
I have built a procedure in VBA that does the shifting perfectly using
arrays, but my question are;
1) how do I get the new array values written back out to a table
-- I will need to use this adjusted table to build a relation to the
chart of accounts
-- I *think* i need to use a temporary table to create these adjusted
records but not sure how to do that.
2) Can anyone think of a better way to do this using plain old SQL ?
Thanks bunchs and look forward to your advice and guidance.
to be drawing a blank on the best method of solving this problem.
I have the task of recreating a financial statement printing
application from an existing accounting solution. It is not an "SQL"
based system, so file structures don't seem to lend themsleves easily
to MS-Access, joins etc -- or atlaeast not simply !
In this system, there is a transaction table and a summary table and it
is the summary table that is used for reporting and the source of my
puzzlement.
A simplfied structure of the summary table would be;
AcctNumber
Period (mm/yy)
PeriodValue_Current
PeriodValue_1mth
PeriodValue_2mth
PeriodValue_3mth
PeriodValue_4mth
..... etc
PeriodValue_150mth
The values are the 'ending' balances of the accounts, the last time the
account was posted to. Do the value of "period" is the last accounting
period that that specific account was posted to and the value of
"current period" is value at that time. All other values are
"relative" to the Period value.
So the data might look like this;
1000, 05/06, 100.00, 200.00, 200.00, 300.00 ....
2000, 04/06, 200.00, 100.00, 300.00, 400.00 ...
3000, 02/06, 300,00, 200.00, 100.00, 500.00...
The challenge is that as the records are processed, I "think" the
values need to be "shifted" to reflect the period the user is running
the report for.
In other words using the same data as above, if I wanted to run a
financial statement for the current period (05/06), I would need to
alter the data to look like this;
1000, 05/06, 100.00, 200.00, 200.00, 300.00 ....
2000, 05/06, 0.00, 200.00, 100.00, 300.00, 400.00 ...
3000, 05/06, 0.00, 0.00, 300,00, 200.00, 100.00, 500.00...
Note that the values for 2000 and 3000 have shifted to reflect the
values relative to the run date and their last updated period. ie. the
value of the current period of 05/06 for acct 2000 is 0.00 as nothing
has been posted to it.
I have built a procedure in VBA that does the shifting perfectly using
arrays, but my question are;
1) how do I get the new array values written back out to a table
-- I will need to use this adjusted table to build a relation to the
chart of accounts
-- I *think* i need to use a temporary table to create these adjusted
records but not sure how to do that.
2) Can anyone think of a better way to do this using plain old SQL ?
Thanks bunchs and look forward to your advice and guidance.