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 |
#11
|
|||
|
|||
Merging Two Identical Databases (Access 2000 format)
On Fri, 13 Jun 2008 07:01:01 -0700, bryan
wrote: All I want is for all the records in both tables to transfer into one complete table without having to re-enter all the records again. A synchronization if you will... Again: You have a record for AT&T in one table. You have a record for AT&T in the other table, with *different data*. What do you want in the final table? You say you want all the records. You also say you cannot have any duplicate company names. These two requirements are in conflict. How do you want that conflict resolved? -- John W. Vinson [MVP] |
#12
|
|||
|
|||
Merging Two Identical Databases (Access 2000 format)
Ok. I have two tables. They both consist of these 5 fields: Company Name,
Address, State, Zip Code, Phone Number. Not much of a database but it serves its purpose of having a quick lookup for company info without having to dig through a huge rolodex. There are some companies in one table and some companies in the other. I want all the records in both tables to be put into one table without having to re-enter every record. The reason I say that I don't want any "duplicates" is because some of the companies appear in both tables but the records are exactly the same. I'm not sure how some companies got entered into both tables but like I said I kinda inherited this mess. People are getting confused about which table is which and that's why they want me to put these two tables into one in the first place. Sorry for all the confusion and thanks for all the patience. "John W. Vinson" wrote: On Fri, 13 Jun 2008 07:01:01 -0700, bryan wrote: All I want is for all the records in both tables to transfer into one complete table without having to re-enter all the records again. A synchronization if you will... Again: You have a record for AT&T in one table. You have a record for AT&T in the other table, with *different data*. What do you want in the final table? You say you want all the records. You also say you cannot have any duplicate company names. These two requirements are in conflict. How do you want that conflict resolved? -- John W. Vinson [MVP] |
#13
|
|||
|
|||
Merging Two Identical Databases (Access 2000 format)
On Fri, 13 Jun 2008 07:01:01 -0700, bryan
wrote: All I want is for all the records in both tables to transfer into one complete table without having to re-enter all the records again. A synchronization if you will... This is the general concept. Table2 is merged into table 1, excepting the duplicates. INSERT INTO Table1 (field_a, field_b, field_c) SELECT field_1, field_2, field_3 FROM Table2 WHERE NOT EXISTS (SELECT * FROM Table1 WHERE Table1.field_a = Table2.field_1 AND Table1.field_b = Table2.field_2 AND Table1.field_c = Table2.field_3); |
#14
|
|||
|
|||
Merging Two Identical Databases (Access 2000 format)
On Mon, 16 Jun 2008 10:29:01 -0700, bryan
wrote: Ok. I have two tables. They both consist of these 5 fields: Company Name, Address, State, Zip Code, Phone Number. Not much of a database but it serves its purpose of having a quick lookup for company info without having to dig through a huge rolodex. There are some companies in one table and some companies in the other. I want all the records in both tables to be put into one table without having to re-enter every record. The reason I say that I don't want any "duplicates" is because some of the companies appear in both tables but the records are exactly the same. I'm not sure how some companies got entered into both tables but like I said I kinda inherited this mess. People are getting confused about which table is which and that's why they want me to put these two tables into one in the first place. Sorry for all the confusion and thanks for all the patience. If the company name is the Primary Key as you indicate upthread, you can simply run an append query: INSERT INTO Table1 ([Company Name], [Address], [State], [Zip Code], [Phone Number]) SELECT [Company Name], [Address], [State], [Zip Code], [Phone Number] FROM Table2; Copy and paste this SQL into the SQL window of a new query; change Table1 and Table2 to the actual names of your tables (as written it will add all of the records from Table2 into Table1). When you run the query you'll get a warning message 32 records were not added due to key violations or something of the sort; these are just the duplicates that are being rejected, everything else will be added. -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Merging Two Identical Databases (Access 2000 format)
Perfect! Thank you John and everyone else for all the help and patience. My
new mission is to learn as much as possible about working a database. "John W. Vinson" wrote: On Mon, 16 Jun 2008 10:29:01 -0700, bryan wrote: Ok. I have two tables. They both consist of these 5 fields: Company Name, Address, State, Zip Code, Phone Number. Not much of a database but it serves its purpose of having a quick lookup for company info without having to dig through a huge rolodex. There are some companies in one table and some companies in the other. I want all the records in both tables to be put into one table without having to re-enter every record. The reason I say that I don't want any "duplicates" is because some of the companies appear in both tables but the records are exactly the same. I'm not sure how some companies got entered into both tables but like I said I kinda inherited this mess. People are getting confused about which table is which and that's why they want me to put these two tables into one in the first place. Sorry for all the confusion and thanks for all the patience. If the company name is the Primary Key as you indicate upthread, you can simply run an append query: INSERT INTO Table1 ([Company Name], [Address], [State], [Zip Code], [Phone Number]) SELECT [Company Name], [Address], [State], [Zip Code], [Phone Number] FROM Table2; Copy and paste this SQL into the SQL window of a new query; change Table1 and Table2 to the actual names of your tables (as written it will add all of the records from Table2 into Table1). When you run the query you'll get a warning message 32 records were not added due to key violations or something of the sort; these are just the duplicates that are being rejected, everything else will be added. -- John W. Vinson [MVP] |
#16
|
|||
|
|||
Merging Two Identical Databases (Access 2000 format)
=?Utf-8?B?YnJ5YW4=?= wrote in
: Thank you John and everyone else for all the help and patience. My new mission is to learn as much as possible about working a database John's suggestion is a pretty arbitrary way to resolve the duplicates. How do you know that the second table does not have useful data in it, such as phone numbers that are missing from the first table? -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#17
|
|||
|
|||
Merging Two Identical Databases (Access 2000 format)
On 18 Jun 2008 00:33:36 GMT, "David W. Fenton"
wrote: =?Utf-8?B?YnJ5YW4=?= wrote in : Thank you John and everyone else for all the help and patience. My new mission is to learn as much as possible about working a database John's suggestion is a pretty arbitrary way to resolve the duplicates. How do you know that the second table does not have useful data in it, such as phone numbers that are missing from the first table? True, but I was relying on the accuracy of Bryan's statement upthread: The reason I say that I don't want any "duplicates" is because some of the companies appear in both tables but the records are exactly the same. If there are in fact discrepancies you would certainly need a more complex approach; the worst case would be that both tables have a phone number... but they are DIFFERENT phone numbers. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|