database with yearly split data

"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.
--
Dave Hargis, Microsoft Access MVP


"cyrus05 via AccessMonster.com" wrote:

> 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.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200808/1
>
>

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

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 Hargis, Microsoft Access MVP


"Fred" wrote:

> 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
>
>
>
>
>
>
>
>

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


Fred wrote:
>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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200809/1

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?

Klatuu wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200809/1

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


"cyrus05 via AccessMonster.com" wrote:

> 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?
>
> Klatuu wrote:
> >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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200809/1
>
>