Database Revamp

[nkafke]

Basically, I've started working at an office that uses Access pretty heavily. We process insurance claims, and put that data into the database. It currently has over 11,000 application records, 500 agents, and a plethora of other misc. data.

The problem is, the guy who initially set it up only had a working knowledge of access. It works, but I'm finding duplicated data, unused data, data linked to other data that links back to the original, and all sorts of other bad practices.

I've created a few new queries and reports so that some of our tasks are simply faster (loading only today's apps versus all 11,000 for instance). But I'm slowly learning that this thing needs to be revamped. I'm more of a C++/VB guy, and I know enough about Access to get my work done...my weakest point with Access is Forms that update multiple Tables, which this thing seems to do quite a bit.

My plan is to cut out the extraneous data, then break apart the two main tables into more relevant sections. Given the amount of data, and the fact that we're continually updating it, I'm wondering if it would be easier to create an entirely new database and move the data over manually.



I know this is kind of vague, and apologize. But any hints, tips, tricks, etc that I can get will be appreciated.

[orange]

What does management think? Is this a trouble spot that is slowing "work"? Do they have any plans on revamping/reviewing the system?
Get some support before you take on a larger task than you think, that can negatively affect business, and you don''t have the people or funds to do the job.

[nkafke]

Management is pretty laid back. So long as the job gets done, basically. It's the 3 of us in the office that primarily use the DB. My concern is that if we need to add/remove a field, it's going to be a nightmare with the current system. Programming background pushes me to compartmentalize things to make them more accessible and easier to manage. I know it's a heavy undertaking, but in 3-6 months it will be much easier to keep it under control and verify data.

*EDIT*
A lot of what I'm doing is to speed up our processes. I've written VB scripts that automate some of our email tasks, something that took 15-30 minutes now takes less than 5, for example.

[bigroo]

Hi Nkafle,

Sounds like you need to put together a business case plan as to why this database needs revamping. Outline the time they could save, discuss what they could get from the database which it is not providing them now, etc.
Maybe begin yourself re-desgining the database if time permits of course, just be proactive I guess this what I am getting at, it does sound like a big project.

Just my 2 cents worth!!

James
Austin, Texas

[nkafke]

Quick update: Got me a big ole book of Access, and three weeks later we have a brand new database. It runs quicker, it's easier to navigate, and has less room for information corruption. Where an application in the old DB could access as many as 11 tables, this one uses 3.

That and it just looks so much better.

[orange]

Good stuff. Keep it up!