design query

[tizwaz]

I have been asked to take over development of a financial tracking database. the gist of what is required is that each financial year a programme is given a budget. Then during the course of the year invoices are submitted which are paid. They want to track such things as how many invoices are paid each month and how long it took to pay from receipt of invoice and also to track how much of the budget has been paid at any point in time during the year.

How is it best to approach this - a fresh database for each financial year, a fresh bunch of tables for each year or one set of tables with varying fields for this year's budget, last years budget etc. The person who started the database intended a separate database for each year but I'm wondering is that necessary?

 

[BNPMike]

I would have one database and tables which don't get re-loaded every year, so you have to have a field "Year Number". The reason is it is quite likely you are going to have leakage from one year to the next. Maybe each year archive off the records more than 1 or 2 years old.

 

[willir]

tizwaz

I have be to wonder how your company is paying invoices -- If you work for a modest size or larger organization, I assume that they use some type of financial application.

If this is the case, then the most obvious solution would involve accessing the data on the financial database. This can usually be done with a database connectivity plug-in such as ODBC. This means that you would access your data directly from the real data instead of doing double entry.

If you work for a smaller ogranization, then if you don't have one, I would suggest that you consider purchasing a third party application such as PeachTree or QuickBooks or even AccPac. These applications should be able to give you what you want.

...Moving on

As suggested by BNPMike, you do not have to recreate the database every year. It makes much more sense to include the transaction dates as part of the table, and then purge the data, not the tables when required.

Richard

 

[tizwaz]

Thanks to both of you. The company does have a financial database which contains some of the information eg invoice no and paid dates the rest of the info has up until now been held in a number of spreadsheets. Now they want a database to pull all of the spreadsheet info together.

I don't really know anything about linking with ODBC - how do you go about this?

 

[BNPMike]

What dbms does your financial database run on eg Oracle, DB2, Informix, SQL Server,etc etc?

 

[tizwaz]

oracle

 

[BNPMike]

You should be alright then. You need an ODBC driver for Oracle. You need your dba to set you up with access to the data you need. Then you can link to the tables and use them in much the same way as you would native Jet tables. You can for example join your Jet tables to Oracle tables in a single SQL statement. Generate your queries in the QBE grid or use Jet SQL. ODBC translates them into Oracle calls.