Errors using Analyze

[Guest]

I am using analyze to normalize a table with 101000 records into 4 tables and
I constantly get an error that says "file sharing lock count exceeded.
Increase maxlocksperfile registry entery." I have increased the settings to
100000 but with no avail. I could really use some Ideas. is there a better
way to normalize an existing table. It will get no larger. Actually I
expect the file to have only 25000 lines when done. There is many duplicate
part numbers over many vendors with different vendor #.

 

[John W. Vinson]

I am using analyze to normalize a table with 101000 records into 4 tables and
I constantly get an error that says "file sharing lock count exceeded.
Increase maxlocksperfile registry entery." I have increased the settings to
100000 but with no avail. I could really use some Ideas. is there a better
way to normalize an existing table. It will get no larger. Actually I
expect the file to have only 25000 lines when done. There is many duplicate
part numbers over many vendors with different vendor #.

In my opinion, normalization requires more intellect than any computer program
yet has.

I'd suggest coming up with a properly normalized design yourself (with help
from the good folks here if need be), based on the logical structure of the
data. Each Entity - guessing here, you might have a Parts entity, a Suppliers
entity, a Sales entity, probably others - will have its own table.

Create the new tables, just in table design view, empty. Then run a series of
APpend queries to migrate the data from the current table into the new one.

Note that the purpose of normalization is NOT to decrease the number of
records in any particular table! It may indeed give you *larger* (but probably
narrower) tables, depending on whether you now have repeating field groups.


John W. Vinson [MVP]

 

[Guest]

I think I need to get a little more specific. The table is for looking up
info about tractor parts. There is Part#, Manufacturer, Tractor model, Engine
Model, part description, Notes, Picture link, Price, Etc. there is actually
only 20 colomns. The issue is that each part# is repeated at least once and
one of them 400 times due to different manufacturers, models and engines per
part. How do I use append query to seperate that and leave the relationship
so that when I do a query I get the 101000 differnet Records that I started
with on the original table? Each Record is different in some way but LOTS of
duplicated data. That is why I was using the Analyze Table and selecting
what I wanted seperated into different tables.

If there isn't a way to seperate this with the Analyze tool is there a
service that can be recomended to get this done qucikly?

Steve Barnes

 

[John W. Vinson]

I think I need to get a little more specific. The table is for looking up
info about tractor parts. There is Part#, Manufacturer, Tractor model, Engine
Model, part description, Notes, Picture link, Price, Etc. there is actually
only 20 colomns. The issue is that each part# is repeated at least once and
one of them 400 times due to different manufacturers, models and engines per
part. How do I use append query to seperate that and leave the relationship
so that when I do a query I get the 101000 differnet Records that I started
with on the original table? Each Record is different in some way but LOTS of
duplicated data. That is why I was using the Analyze Table and selecting
what I wanted seperated into different tables.

If there isn't a way to seperate this with the Analyze tool is there a
service that can be recomended to get this done qucikly?

Well, I'd start with the free, volunteer supported service
microsoft.public.access <g>.

It sounds like you need (at least) the following tables:

Manufacturers
MfgrID <Autonumber Primary Key>
Manufacturer <text>
<address, contact info, etc.>

Parts
PartNo (don't use # in names; primary key, manually assigned or autonum)
Description
PictureLink
<other info about the part as a thing-in-itself>
<NOTHING about prices!!>

Tractors
ModelNo <Primary Key>
MfgrID
<other info about the tractor>

Engines
EngineNo <Primary Key>
<other info about the engine>

PartsList
PartNo <link to Parts>
MfgrID <link to Manufacturers>
ModelNo <link..
EngineNo <link..
Price (for *this* part by this manufacturer on this tractor on this engine>
EffectiveDate (date the price was set)

Your PartsList table will still have your 101000 records - but instead of
storing all of the manuacturer information or even the manufacturer's name
over and over, it will just have a 4-byte long integer foreign key to the
Manufacturers table.

This is just a sketch of the final design; you're in a better position to
flesh it out and verify it than I am. Post back if you'ld like to discuss.

John W. Vinson [MVP]