Need help in Close Accounting Period

[Aeric]

Hello, i am new here.
I hope you guys can give some suggestion here. I am in the end of developing a software for my company. I am not sure what to call it, an ERP/CRM/MIS system with accounting function. The system is developed mainly using Visual Basic 6, MS SQL Server 2000 and Crystal Reports 8.5 Developer. My boss then raised out a problem regarding the closing account and bring forward. After the system is implemented, and run for 2 years or more, the database will be full of transaction and records. The report generation will be slower due to this reason. Me and another senior are the only programmers in the company and we are not familiar with Accounting concepts. We only use basic skills i.e like View or temporary tables for some Report generation and we want to avoid using Triggers or Store Procedures. Please advise. Thanks.

[ganeshmoorthy]

when you run the Year-End process you need to close all the accounts and open new year account.

Calculate the closing balances for each account separately
create a new database for the new year
Update the account with the opening balance as the previous closing balance
Close all the closed/finished transactions
transfer unfinished transactions to the new database...

[szlamany]

MS SQL 2000 doesn't have issues with lots of rows...

How many do you expect?

Having different tables for different years is no way to run a database...

Our accounting systems and payroll systems have data, at some customer sites, going back to 1993! We made sure to build our primary keys as COMPOUND KEYS - such as FISCAL YEAR + TRANSACTION NUMBER, instead of just a TRANSACTION NUMBER (which would be typical in an accounting system from 25 years ago).

By having the FISCAL YEAR in the KEY and making the primary key's CLUSTERED we basically have each FISCAL YEAR in the TABLE in it's own area.

All reports and queries specify the FISCAL YEAR in the WHERE clause. You never have to worry about other FISCAL YEAR rows in the table - they do not slow down the operation at all.

If you are at the end of the development cycle how are you going to deal with this?

btw - MS SQL 2005 celebrates this techique to the point where you can actually have some FISCAL YEARS reside on different disks - even different servers - so that your "current" FISCAL YEAR is on the main server. All transparent to the queries...

[Aeric]

Wow, thanks. I hope I can rush to modify the database and coding by adding the Fiscal year field. I agree it is not a good idea to make separate database as we need to generate cross year report and joining multiple database is not a good way i think.

[szlamany]

...as we need to generate cross year report and joining multiple database is not a good way i think.

That is one of the huge benefits of having a single DB/set-of-table for an accounting system that crosses multiple years...

Good luck!