I have never done it using the analyzer, so I can't give any specifics on how
to use it. I usually do it myself by looking at the data and the business
requirements, and creating my own model, then using append queries to move
the data into the tables I created.

As to what tables you need, it is hard to say. The first thing to look at
is those repeating values you mentioned. That usually indicates a child
table so the repeated value is carried only once in a table and a foreign key
field is used to reference the value.

For sure, you will want an Employee table that contains all the static
information about an employee. And a payroll transaction table that that
carries the value for each pay cycle. If you have hourly employees, then you
probably need an attendance table to show what hours the employee worked.

But, without seeing what you have now, it is not possible to give a complete
answer.
--
Dave Hargis, Microsoft Access MVP


"JWeaver" wrote:

I created a database based on one that the office already had. There is one
table that contains everything instead of several tables therefore some
information is repeated over and over again in each record. I want to make
this database better by getting it "Normalized" so that there is one table
for Employee Info, one for Client Info, one for Payroll Info, etc.

I used the Analyze Table function to separate the big table into smaller
tables. This seems to work OK if I only have a few tables but if I try to
separate them into a lot of different tables for those small pieces of the
table that are different, I get a MaxLocksPerFile error. Even if I increase
this number in the Registry I get the same error so I gave up.

When Access separates the tables, and I go to the subsequent steps, it seems
that there are a great deal of records that show up that it believes has
related data and I have to delete out its proposed correction. If I separate
the table, I don't have to do this as much.

This database is used to gather payroll information. Therefore, some fields
(like Start Date and End Date) are the same for every employee during a
billing period. How finely should I separate the table to make it better?
If I need to add a record, do I need to add info to all tables or would I be
able to add it to one table and the other tables would fill in automatically
based on the lookup fields?

Your help in getting this database normalized would be appreciated!!

--
JWeaver