Is Access the right tool?

[WLHagen]

I am a financial analyst at a small, local financial institution and responsible for tracking LOTS of data on a monthly/quarterly/annual basis for the executive team. This data involves deposit accounts (checking accounts, savings accounts, certificates, etc) and loan accounts (car loans, mortgages, personal loans, equity loans, etc) both in the "new accounts" and "existing accounts". I get files every month with information on both existing accounts (balance, opening date, interest rate, next due date for payment, maturity date for certificate etc) and new accounts opened in the previous business month (open date, opening amount, who opened it at what branch, etc). I report to my superiors information such as "number of new loans written last month by loan officer and branch", "amount of new loans by Loan officer and branch", "amount of new deposit accounts by type and branch and opening balance", as well as from our exisiting portfolio - delinquent loans by type of loans, days of delinquency, branch of loan, etc.

The "existing accounts"data is fairly similar on a month-to-month basis (think of your own checking account and/or loan - your account number, the date you opened it and the interest rate is the same every month, but the ending balance and next due date on your loan would change).
I've been manipulating this data in Excel for a long time, mostly because "that's what I know". However, now I've taken a few Access courses, and am wondering if I'm using the right tool for the job. The class used the "Northwind Traders" Access database for the class, and I really like the way the data is presented (Interactive graphs and tables). I'm wondering if perhaps Access is the "right tool".

HOWEVER, my institution doesn't have licenses for Access, and I don't want to have them purchase a license for the institution (we are <$300MM in assets and <100 employees) - only to find out that the "right tool for the job" was Excel all along. Therefore, I'm coming to my experts here @ Mr Excel to ask....

1. Do you think Excel is the "right tool for the job"?
2. If you think Access is the "right tool for the job" - and I start building this in Open Office, is there a huge "conversion difference" with converting the file from Open Office to MS Access?
3. If Open Office/Access is the 'right tool' - would you suggest different tables for each month's loan/deposit information, or would you just have one large table - just appending the month end date of the file as a field in each record each month to be able to do calculations?

 

[Montez659]

WLHagen,

Great questions, and one that many people have asked before. Before I give my opinion on anything, I would want you to read the following article. You have asked the right question - "Is Access the right tool?" as opposed to what most people ask - "Is Access better than Excel?" There is a huge difference between the two and this article will help you understand (which you may already know). A good read nonetheless.

http://forums.aspfree.com/microsoft-access-help-18/access-vs-excel-349267.html

 

[WLHagen]

Thanks for the link to the article. Very informative, and pretty much information that I already knew.

The question remains - your opinion please?

 

[Montez659]

Things to consider (which I know you will) before my opinion:
What is the cost/benefit of using Access vs. Excel?
What is the size of the data you are working with?

Since I have both Access and Excel, it is hard for me to answer my own first question. i try to use each one for their appropriate use. As far as number two, I have found that Access calculations of data for reports and the such are quite faster than Excel. If what you are trying to accomplish invloves less user inputs and more data analysis, probably Access is the way to go.

As far as Open office products, I have not worked with them enough to know of any type of answer to your question.

As far as your table structure in Access, I would append to a table instead of creating new tables. Creating new tables would be incredibly more work than is needed. A simple date limiting query can be created to get the info for the most recent month/quarter, etc. Plus if you want to look at a larger swath of info than you previously thought, all you need to do is set the beginning and ending date as opposed to trying to link to a bunch of different tables.

Hope my thoughts have helped. Please wait till others chime in before moving ahead, as this is only one man's opinion! And regardless of the ideas, do what is best for your organization.

 

[Joe4]

What you are describing is a database, and database projects scream "Access" (or some other database program). All things being equal, Access is DEFINITELY the tool I would use here.

I would NOT have separate tables for each month. It should all be in one table. Over time, you can "archive" out old records you don't need/use anymore.

In database design, it is important to build your table correctly and follow the rules of database normalization, or else you might program yourself into a corner. Many into Access books and courses don't talk about these topics enough. Here is a good article on it: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[xenou]

Access is generally great at what you are describing ... but takes a lot more careful design up front, which could be days, weeks, or months depending on your skill and how much time you have to build a reporting database. It's also not as easy to dump on someone else since many people know nothing about Access. If this is intended for other users or might need to be transitioned to someone else, it has to be even more carefully designed so that employees not skilled in Access can still use it (via forms and menus).

I would probably start small - take a piece of it and see how you like it. If it works, you can start expanding more pieces to the database - importing and exporting to Excel as needed. It's probably better to use the same tables, not a new set of tables each month. You may need to archive or clear the data each month as the new data comes in for the current period analysis/review - depending on how much there is (Access isn't for really large sets of data so adding 300,000 rows each month would start to fill it up).

I'm not impressed with OpenOffice Base and have never seen any conversion tools, so I wouldn't use it unless you intend to stay with it. Basically, Access is the best desktop database around (my opinion). There are loads of good databases as such (raw database engines for storage and retrieval), but with Access you can quickly create a good set of forms/reports and have excellent tools to interact with and use your data.

 

[revans]

I have to echo Joe's reply. You want a database not a spreadsheet. Definately go with a single table and filter out the dates you want into your reports. Make sure you've normalized as best/reasonably as you can.

If you're worried about costs you can look at SQL Server Express. It's a "step up" from Access and probably more secure, though that's just my impression. I'm sure there are other free databases out there. Ultimately costs projections should also look at IT support time too, so $150 for Access if you know it vs free db that IT has to spend hours helping you with...

hth,

Rich

 

[WLHagen]

For all: Thanks so much for your opinions. You've validated my thoughts and assisted with pointing me in the right direction. I'm tackling this project on my own, but not without resources - I have y'all to help me! ray:

The data I get monthly comes from our processor - in the same format from month to month, so formatting a table will be easy, it's how I already get the data! Same with the "this month sales" - it comes from our processor in a given format. The only thing would be to add a field in the data prior to appending that indicates what month the data is for, to assist with comparing data over time.

I'm wondering aloud, tho..... "WHY do I create these projects for myself????"

 

[Joe4]

The only thing would be to add a field in the data prior to appending that indicates what month the data is for, to assist with comparing data over time.

One way is to import the data into a "temporary" holding table each month, and then use an Append Query to post the data from this "temporary" table to your final table, and at the same time, have the Append Query populate this month field (make use of a calculated field).

 

[WLHagen]

Joe, you're my HERO!!! ray: