If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
database with yearly split data
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/For...esign/200808/1 |
#2
|
|||
|
|||
database with yearly split data
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" 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/For...esign/200808/1 |
#3
|
|||
|
|||
database with yearly split data
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/For...esign/200809/1 |
#4
|
|||
|
|||
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/For...esign/200808/1 |
#5
|
|||
|
|||
database with yearly split data
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 |
#6
|
|||
|
|||
database with yearly split data
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 |
#7
|
|||
|
|||
database with yearly split data
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/For...esign/200809/1 |
#8
|
|||
|
|||
database with yearly split data
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/For...esign/200809/1 |
#9
|
|||
|
|||
database with yearly split data
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 |
#10
|
|||
|
|||
database with yearly split data
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 Klatuu wrote: 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] Fred -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200809/1 |
|
Thread Tools | |
Display Modes | |
|
|