database with yearly split data

[cyrus05 via AccessMonster.com]

hi folks
i am designing a database for a firm that has annual period of the accounting.
where they close all accounts at the end of each year. Balances are then
transfered as the initials (first) records to the next year. e.g

balances of the suppliers and customers accountings
total number of stock in hand for each item
etc.

Does anyone have any idea how to handle the data yearly? Filtering the
records is not a very good idea as for large database it will take time
opening every form that is bounded to data. I have seen one software in
Delphi that creates a seperate database in SQL server for the next year, and
transfer all the data from the previous year database with all the balances
as the first records in the new database accounting tables and stock tables.
bwefore logging in the software, you select the database (year) to work in.
in previous years all the new/edit/delete options are unavailable, only you
can browse the data but the new database can use the data from previous
databases as well as the current one. I have been working in access for years,
Any idea how to manage this, please explain in detail.

 

[Fred]

On the money side, are you running a full GL (General Ledger) accounting in
your application (e.g. includes posting of all income and expenses) or just
the two described transaction categories (A/R & A/P)?

 

[cyrus05 via AccessMonster.com]

Its going to be the full general ledger. the ones i described were only to
specify the need as an example.

On the money side, are you running a full GL (General Ledger) accounting in
your application (e.g. includes posting of all income and expenses) or just
the two described transaction categories (A/R & A/P)?

hi folks
i am designing a database for a firm that has annual period of the accounting.

[quoted text clipped - 16 lines]

databases as well as the current one. I have been working in access for years,
Any idea how to manage this, please explain in detail.

 

[Klatuu]

"Filtering the records is not a very good idea as for large database"

Define large database?
It actually is the better way to accomplish the task. Otherwise, priory
year or even prior period and prior quater reporting become much more
difficult.

A propertly normalized database with correct indexing on search fields is a
much better design than using annual table structures.

 

[Fred]

Dave's response gives you the best and most efficient design to store, and
use your data.

Certain common accounting meanings of "closing out the year" might intrrude
on that. A common meaning for that is all of these:

-Balance the books (=~ find all errors) for the year. In the equation are
year-begging and year-end figures, but nothing from the previous or
subsequent year. I did this excruciating process myself once while I was
trying to hire and accounting manage, and it was the closest that I have ever
come to shooting myself.

-Then sort of "lock up" the year being closed out, making it difficult or
impossible to make any modifications to the entires / postings for that year.


-Start the new year so that only "year-beginning" figures and current
entries affect calculations, I.E. entries / postings from the previous year
are not utilized.


In some cases. removing and archiving the data (or that type) from the
closed out year might be the best practice. In any event, you should
make, tag as such, archive and lock up a copy of the closed out year (or
the entire DB with it in it)


Sincerely,

Fred

 

[Klatuu]

I understand your issues, Fred, but if it is a requirement to lockdown and
archive previous year's data, there are ways to to that.
I have worked with several different general accounting systems on different
platforms, and was heavily involved in writting a GL system from the ground
up in the early days of mini computers.

One approach is to create an archive backup in a different file. In Access,
you would want an archive mdb that you would save the previous year's data in.

There certainly are some year end issues that require some closing entries,
carry forward entries, balancing entries, etc. And, you want those captured
and completed before the closing is complete.

There is also another issue that needs to be addressed and that is how to
handle prior period issues for the first period.

But, in reality, it boils down to the accounting practices of your
organization.

All I am saying is that before you remove data from you main tables, analyze
what you will need to keep from the previous year(s) before you start any
database design or coding. I have yet to see a system that doesn't require
some Prior Year to Current Year reporting.

 

[cyrus05 via AccessMonster.com]

I once have thought of making an archive of the .mdbs yearly by closing all
enteries, copy structure of the database and some tables with the data such
as customers/suppliers, stock tables.
But closig enteries of oarchieved mdb have to be the begining entry for the
nwe mdb, how to accomplish that? It can be done manually for each period
transfer but then what is the use of computer here?
Closing of a period and opening of a new period is the correct way, and that
is not possible by filtering records from the same mdb yearly.
Also as I have mentioned the Delphi / SQL server plateform example in my
earlier post, where the user selects the database year to use that specific
period, is kind of archieving mdbs but how the current archieve access to the
archieved data? for example following a job history of any particular
customer is displayed from the current as well as the archieved data?
I will request you to concentrate on archieving and posibility of retreiving
data from it. HOW, UNDER WHAT STRUCTURE?

I understand your issues, Fred, but if it is a requirement to lockdown and
archive previous year's data, there are ways to to that.
I have worked with several different general accounting systems on different
platforms, and was heavily involved in writting a GL system from the ground
up in the early days of mini computers.

One approach is to create an archive backup in a different file. In Access,
you would want an archive mdb that you would save the previous year's data in.

There certainly are some year end issues that require some closing entries,
carry forward entries, balancing entries, etc. And, you want those captured
and completed before the closing is complete.

There is also another issue that needs to be addressed and that is how to
handle prior period issues for the first period.

But, in reality, it boils down to the accounting practices of your
organization.

All I am saying is that before you remove data from you main tables, analyze
what you will need to keep from the previous year(s) before you start any
database design or coding. I have yet to see a system that doesn't require
some Prior Year to Current Year reporting.

Dave's response gives you the best and most efficient design to store, and
use your data.

[quoted text clipped - 24 lines]

Fred

 

[Klatuu]

As to getting the closing entries into the current data, that is a matter of
timing. You have to do your archiving at the proper time in the workflow.

As to accessing archived data, use a naming scheme for your archived mdbs so
you can programmatically construct a file name based on a user's choices.
Then you can link to tables in the archive data for reporting purposes. It
is perfectly normal to link to multiple backends. Just do not use any table
names that are year specific.
--
Dave Hargis, Microsoft Access MVP

I once have thought of making an archive of the .mdbs yearly by closing all
enteries, copy structure of the database and some tables with the data such
as customers/suppliers, stock tables.
But closig enteries of oarchieved mdb have to be the begining entry for the
nwe mdb, how to accomplish that? It can be done manually for each period
transfer but then what is the use of computer here?
Closing of a period and opening of a new period is the correct way, and that
is not possible by filtering records from the same mdb yearly.
Also as I have mentioned the Delphi / SQL server plateform example in my
earlier post, where the user selects the database year to use that specific
period, is kind of archieving mdbs but how the current archieve access to the
archieved data? for example following a job history of any particular
customer is displayed from the current as well as the archieved data?
I will request you to concentrate on archieving and posibility of retreiving
data from it. HOW, UNDER WHAT STRUCTURE?

I understand your issues, Fred, but if it is a requirement to lockdown and
archive previous year's data, there are ways to to that.
I have worked with several different general accounting systems on different
platforms, and was heavily involved in writting a GL system from the ground
up in the early days of mini computers.

One approach is to create an archive backup in a different file. In Access,
you would want an archive mdb that you would save the previous year's data in.

There certainly are some year end issues that require some closing entries,
carry forward entries, balancing entries, etc. And, you want those captured
and completed before the closing is complete.

There is also another issue that needs to be addressed and that is how to
handle prior period issues for the first period.

But, in reality, it boils down to the accounting practices of your
organization.

All I am saying is that before you remove data from you main tables, analyze
what you will need to keep from the previous year(s) before you start any
database design or coding. I have yet to see a system that doesn't require
some Prior Year to Current Year reporting.

Dave's response gives you the best and most efficient design to store, and
use your data.

[quoted text clipped - 24 lines]

Fred

 

[Fred]

Dave,

GL in anything except the smallest business environment almost inevitably
needs automated ties with and databasing of A/R/ A/P, and invoicing/order
transactions. Combined with that, many transactions (e.g. invoicing) must be
daabased as events. I've never attampteed it, and instead have used
enterprise software. My comments were more from seeing how enterprises
software ( which is a database application) appears to handle these things.

You having written these before means that you know to actually do this far
better than I do.

Sincerely,

Fred

 

[cyrus05 via AccessMonster.com]

thanx for your reply. so you people actually agree with my archieving mdbs
idea? timing and file name for the "to be archieved" mdb will be given by the
user, thats an easy job.
Here is my approach to the problem solution and I am not satisfied with it.
A module will post all closure enteries, then
1. Create another mdb promting user for a new name
(data (name) will also be added to a table containing names of all the
archieved databases)
2. Structure of the old mdb will be copied.
3. Data of some of the tables (requirted) will also be imported, e.g;
database names data
users data (security passwords)
customer/suppliers data
stock data
jobs data
4. Append closure entries in new database' corresponding tables as first
records (opening entry)
5. Archieve the old database and restart the application
6. On the first form, before the user log in security, user will be prompt to
select the database to work in. (retreive the database names from the
databases table mentioned in 1.)
7. Programmatically create links to the selected mdb.

Some drawbacks do exist in my approach or perhaps i dont know exactly how to
figure the solution.
1. How to append the closure entry as opening entry in the new database?
2. Reporting may not be possible concerning new and old data.
3. You name it...

isnt there any other MUCH BETTER approach to this issue? Is there anyone who
already has done this before, please guide me HOW? if different than above

As to getting the closing entries into the current data, that is a matter of
timing. You have to do your archiving at the proper time in the workflow.

As to accessing archived data, use a naming scheme for your archived mdbs so
you can programmatically construct a file name based on a user's choices.
Then you can link to tables in the archive data for reporting purposes. It
is perfectly normal to link to multiple backends. Just do not use any table
names that are year specific.

I once have thought of making an archive of the .mdbs yearly by closing all
enteries, copy structure of the database and some tables with the data such

[quoted text clipped - 40 lines]

 

[cyrus05 via AccessMonster.com]

sorry Fred, my message was suppose to appear after you, but it is displayed
above your message, please read that.
Regards,

 

[Klatuu]

You are correct, even in medium sized organizations, GL does have to
interface with AP, AR, Payroll, and other industry specific modules like
Sales, Inventory, POS, and a host of others. The typical method for handling
this is a posting routine where the external app creates transactions the GL
then picks up. To be truely integrated, the external apps have to have
access to the Chart of Accounts to do it's posting.

 

[Fred]

Dave (Klatuu) would be the more expert one to answer this.

If you'd like my "armchair" thought, the only time that I actually did this
(on a very very small scale, and with minimal automatic integration with
other "modules" ) vs. using enterprise software to do it, once the dust
settled after the end of the year (i.e. a few weeks into the new year) I had
them balance / reconcile the previous year's GL, copied/archived the GL from
the year being closed into a new little year-specific GL applicaiton and also
to a locked archive, made "January 1st" opening entries, and then wiped out
the GL entries for the year being closed. I did not do this with or "close
out" anything except the GL. After that moment, there could be not GL
postings of any type for (with a date of) the previous year.