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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|