A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table Analyser and more...



 
 
Thread Tools Display Modes
  #1  
Old June 28th, 2004, 05:28 PM
Mary Ann
external usenet poster
 
Posts: n/a
Default Table Analyser and more...

I am using Access 2003

I have a table of nearly 10,000 records which I have
transferred from Excel to Access. The table consists of
contact names, company names, company addresses and sorts
of contact.

I want to split the flat table into 3 related tables -
Contacts, Companies and Sorts and use the Table Analyser
to do this. There will be a one to many relationship
between the Companies and Contacts tables and between the
Sorts and Contacts table. I have previously used the
analyser to split a smaller similar database and it
worked fine. However, when I used the analyser on this
database (and spent hours in the wizard correcting
company address data!!) I was told when I click the
Finish button "File sharing lock count exceeded.
Increase MaxLocksPerFile registry entry". I had no
option but to cancel the Wizard at this point.

I do not work in the registry so have no idea how to do
this and am very cautious about going in there! I think
I can get round this by splitting my original flat table
into two, then analyse the two smaller units and then use
a combination of Update and Append queries to get the two
sets of three split tables back into one set of three.

What is the default maximum number of records the table
analyser can cope with?

I am concerned that even if I do manage to satisfactorily
split my table this error message may occur when I am
doing other things in the database e.g. running an
ordinary select query. Is that the case or is the error
message just to do with the table analyser wizard? In
the normal run of things, with its typical default
settings, can Access manage a table of 10,000 records?

Secondly I would greatly appreciate some reassurance that
I am going about this in an OK way. What I intend to do
is as follows: once I have split the two original flat
tables, I plan to append the second split companies table
into the first split companies table - hiding the id
field so the second batch of companies will get a new
ID. I then plan to update the company foreign key in the
second split contacts table to e.g. [CompanyID] + n where
n is the number of company records in the first split
companies table. I then repeat the process for the Sorts
tables and foreign key. Finally I append the contacts
from the second split table into the first split contacts
table - again hiding the id field.

Does that sound about right????

I apologise for the length of this message but thought it
wiser to explain exactly what happened and what I am
planning.

Any help from you MVPs would be fantastic!

Regards

Mary Ann
  #2  
Old June 28th, 2004, 06:20 PM
Rick B
external usenet poster
 
Posts: n/a
Default Table Analyser and more...

Wouldn't it be easier to simply copy and paste the table two times, go into
design view and delete the fields not needed in each table, then create your
relationship links?

Rick B


"Mary Ann" wrote in message
...
I am using Access 2003

I have a table of nearly 10,000 records which I have
transferred from Excel to Access. The table consists of
contact names, company names, company addresses and sorts
of contact.

I want to split the flat table into 3 related tables -
Contacts, Companies and Sorts and use the Table Analyser
to do this. There will be a one to many relationship
between the Companies and Contacts tables and between the
Sorts and Contacts table. I have previously used the
analyser to split a smaller similar database and it
worked fine. However, when I used the analyser on this
database (and spent hours in the wizard correcting
company address data!!) I was told when I click the
Finish button "File sharing lock count exceeded.
Increase MaxLocksPerFile registry entry". I had no
option but to cancel the Wizard at this point.

I do not work in the registry so have no idea how to do
this and am very cautious about going in there! I think
I can get round this by splitting my original flat table
into two, then analyse the two smaller units and then use
a combination of Update and Append queries to get the two
sets of three split tables back into one set of three.

What is the default maximum number of records the table
analyser can cope with?

I am concerned that even if I do manage to satisfactorily
split my table this error message may occur when I am
doing other things in the database e.g. running an
ordinary select query. Is that the case or is the error
message just to do with the table analyser wizard? In
the normal run of things, with its typical default
settings, can Access manage a table of 10,000 records?

Secondly I would greatly appreciate some reassurance that
I am going about this in an OK way. What I intend to do
is as follows: once I have split the two original flat
tables, I plan to append the second split companies table
into the first split companies table - hiding the id
field so the second batch of companies will get a new
ID. I then plan to update the company foreign key in the
second split contacts table to e.g. [CompanyID] + n where
n is the number of company records in the first split
companies table. I then repeat the process for the Sorts
tables and foreign key. Finally I append the contacts
from the second split table into the first split contacts
table - again hiding the id field.

Does that sound about right????

I apologise for the length of this message but thought it
wiser to explain exactly what happened and what I am
planning.

Any help from you MVPs would be fantastic!

Regards

Mary Ann


  #3  
Old June 29th, 2004, 06:36 PM
Mary Ann
external usenet poster
 
Posts: n/a
Default Table Analyser and more...

Thanks for your reply Rick. I wanted to use the analyser
to help clean up some of the data. And the other problem
is that the original data doesn't include a field that
would uniquely identify the foreign data and I don't
really want to get into composite keys. However....I've
been working on it some more today and am making
progress.

Regards
Mary Ann
-----Original Message-----
Wouldn't it be easier to simply copy and paste the table

two times, go into
design view and delete the fields not needed in each

table, then create your
relationship links?

Rick B


"Mary Ann" wrote

in message
...
I am using Access 2003

I have a table of nearly 10,000 records which I have
transferred from Excel to Access. The table consists of
contact names, company names, company addresses and sorts
of contact.

I want to split the flat table into 3 related tables -
Contacts, Companies and Sorts and use the Table Analyser
to do this. There will be a one to many relationship
between the Companies and Contacts tables and between the
Sorts and Contacts table. I have previously used the
analyser to split a smaller similar database and it
worked fine. However, when I used the analyser on this
database (and spent hours in the wizard correcting
company address data!!) I was told when I click the
Finish button "File sharing lock count exceeded.
Increase MaxLocksPerFile registry entry". I had no
option but to cancel the Wizard at this point.

I do not work in the registry so have no idea how to do
this and am very cautious about going in there! I think
I can get round this by splitting my original flat table
into two, then analyse the two smaller units and then use
a combination of Update and Append queries to get the two
sets of three split tables back into one set of three.

What is the default maximum number of records the table
analyser can cope with?

I am concerned that even if I do manage to satisfactorily
split my table this error message may occur when I am
doing other things in the database e.g. running an
ordinary select query. Is that the case or is the error
message just to do with the table analyser wizard? In
the normal run of things, with its typical default
settings, can Access manage a table of 10,000 records?

Secondly I would greatly appreciate some reassurance that
I am going about this in an OK way. What I intend to do
is as follows: once I have split the two original flat
tables, I plan to append the second split companies table
into the first split companies table - hiding the id
field so the second batch of companies will get a new
ID. I then plan to update the company foreign key in the
second split contacts table to e.g. [CompanyID] + n where
n is the number of company records in the first split
companies table. I then repeat the process for the Sorts
tables and foreign key. Finally I append the contacts
from the second split table into the first split contacts
table - again hiding the id field.

Does that sound about right????

I apologise for the length of this message but thought it
wiser to explain exactly what happened and what I am
planning.

Any help from you MVPs would be fantastic!

Regards

Mary Ann


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.