Cleaning up a database?

[Zorkmid]

I built a database acouple years ago, but having had to convert it from a series of excel files to an access database. Almost all of the information is stored in a single table.

Is there a simple way for me to pull apart this table into different categories?
Would this help speed up the database?

-Z

 

[GaryPanic]

how many fields are we talking about ?....
and how many rows of data ...

 

[gaDave]

Is this a performance issue? How many fields and rows is your table and what type of data? If you dumped the data from multiple spreadsheets into one table, your database likely would benefit from normalization, i.e. finding duplicate data repeated in multiple records and separating that data out into a different table, then linking records between tables using foreign keys.

Post back with details if you can.

Dave

 

[The_Doc_Man]

OK, a bit of heresy here...

For small enough databases with narrow enough tables and simple enough math, reports, or forms, it almost doesn't make a difference.

For instance, if you kept a personal address book in Access, it almost wouldn't matter if that was all it did.

If the table was only say 50 records deep, it almost wouldn't matter because of speed issues.

You need to split/redefine your tables when things get very big. What is very big? Only you can really decide that.

There is a "pride of authorship" issue that can ALWAYS be a driving force to do things correctly. You would design it properly even for the shortest tables.

There is a "want to learn" factor that can be a driving force to do things correctly. You would investigate proper design even for the shortest tables.

There is a "this damned thing won't work" factor that can be an IMMEDIATE and URGENT driving force to get things done correctly. You would investigate proper design no matter what your tables contain.

But if none of those three reasons apply, then the old engineer's rule applies: If it ain't broke, don't fix it.

Now, to answer your question about "how difficult" ... that depends on where you are starting and where you are going. This IS the proper topic to ask your question. May I suggest that you read a bit on the subject of Database Normalization as a starting point? Look through older posts in this topic heading to see other questions about design issues and how to approach the problem.

Given the vagueness of your original statement, it is nearly impossible to get a quantitative answer on how difficult it would be to do what you want. But it is almost NEVER impossible to split your tables to improve structure, quality, or performance.

 

[gemma-the-husky]

the problem is, that if you have a "spreadsheet layout" then you might find that you have what is REALLY the same identifier (ie value in a given column) entered in more than 1 way

but basically for each (set of) columns which belong together, you need to break out all the unique values (sets of values) into a separate table.

there is a table analyser in access that will try and analyse your data into nor,malised form - but it may achieve different results to those you would do behind - its still worth doing to see what happens

this process is called normalisation - and it is product of art and science - to achieve an optimal analysis of your data.

but if you are seeing values that should be the same - but which arent consisitent throughout the database - then you have a problem that needs resolving