I am pretty new to access and database design and the more I read through some of the threads I think my database could be in trouble down the road. I have a database with 1 table storing all the information. When I first designed the database I did not realize that tables should be broken up so I only made one table to store all my data. My database tracks accidents occurring in one of many offices that my office oversees. The database tracks a number of things such as accident location (office, state, etc.), employee information (name, language, age, position, etc.), accident info (event, date, time, shift, injury, multiple eemployees injured, equipment involved, etc.), reporting information (tracks a number of specific forms that need to be submitted, when they are submitted, etc.), description of the accident, etc. In total there is about 60 to 70 fields in the table. At this point it is not creating any major problems (that I know of) but I am a little concerned that having only the 1 table will eventuall cause problems as the database grows. I guess I am looking for some advice on whether I should leave it as is or try to redesign the tables and split them up. If I should split them up, any advice on how to break it up and define relationships would be appreciated. Thanks.

If I am understanding your description correctly, you currently need to enter the same information more than once. For example, you have 1 employee who is in 3 accidents. Each accident is a different record in the table, therefore you need to enter that employee's information 3 different times. This is what relational databasees try to avoid.

The proper method would be to create a seperate table for employees, locations, and any other fields that may have duplicate data, grouping the related fields together.

This method insures, for example, that a particular employee's name is spelled correctly, since it is only entered once, as well as making updates to that information easier, as you need only update it in one location.

That said, depending on how many records you have, and how often the information changes or is added to, you might not need to worry about any of this. If your database will have thousands of records, it's probably a good idea to split it up.

Restructure it immediately. Every bit of effort you put into building reports will have to be redone when you change so the sooner you change the less time you will ultimately waste.

Make sure you fully understand normalization before you do anything.

Then design your new tables and a set of queries that convert the data. This way the users can continue in the existing database until your new one is working smoothly. The data can be synchronised at any moment with those queries and the users smoothly moved over to the new system.

Just to be clear, when you say immediately do you mean 5 years ago?

Just to be clear, when you say immediately do you mean 5 years ago?

I thought he meant before the Y2K Bug jumped out and bit the OP. :eek:

Ah. Never noticed the date of the original posts. A spammer posting brought it back to life yesterday and their post has since been deleted.