Use Excel or Access.Which way to go?

[Rats]

I need to design an application which will store client data including financial information such as loan details and budget details. I then need to do a number of calculations based on cashflows up to 40 years into the future and produce reports and graphs etc. I am very familiar with excel and can do the calculations, graphs etc in a spreadsheet and I can build a database to collect and store the personal data etc but I am not familiar with how to create all the calculations. What I am uncertain of is the best way to go with the task. Do I:-
1. Build a database and a spreadsheet and link the two and if so can I keep the spreadsheet activity transparent to the user.
2. Just do the lot in a database. If so any directions on where I should look for methods of doing the calculations.

Appreciate your thoughts on the matter.

Thanks
Rats

 

[Ron_dK]

I would say that, since your business process is mainly outputting financial data, based on calculations, use Excel. I would suggest to store the base client data in a Dbase, depending on the size of the total number of accounts/clients. If you're talking about hundreds/thousands of accounts/clients I would use a Dbase , if < 100 use Excel.

Excel is somewhat better in producing graphs and the like and allows more complex mathematical calculations than Access.

Hope this helps,

Ron

 

[Rats]

Ron
Thanks for your answer. I am leaning that way myself but wondered if I would be heading in the wrong direction.

Cheers

Peter

 

[Pat Hartman]

Access is far more suitable as a database than Excel. When Creating a database you need to think about the tables differently than you think about your spreadsheets. In general a database table will be "long" where a spreadsheet is "wide". Databases such as Access do not provide aggregate functions that work "accross". So if you had columns named Jan, Feb, Mar, there would be no function to sum them. You would need to write the calculation manualy. Do some reading on normalization before you start. It will save you time and trouble in the long run.

The best thing is that Access will require far less maintenance than your spreadsheets. Typically, spreadsheets need to be recreated or at least modified for subsequent years. They also need to be modified when you add more people or customers etc. Since these things would be rows in a table, there is no modification required to add data for subsequent.