 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / March 2005I need to Merge Multiple Tables
Ok... I have a fairly complex set of databases that I use in a Call center. Each user has a separate database (I did this because after about 6 users it got VERY slow). All of the databases (about 15 in all now) have the same fields with different data. What I want to do is create a single table/query that holds the data from all of the separate tables in each database. EX. DB1 has data stored in table Leads ID LastName FirstName Phonenumber 04298382 Smith John 614-555-1212 DB2 has data stored in table Leads as well ID LastName FirstName Phonenumber 04223382 Jones John 614-555-1213 I want to merge the data so that I will have a new table/query with data as follows ID LastName FirstName Phonenumber 04223382 Jones John 614-555-1213 04298382 Smith John 614-555-1212 The ordering of the data is really not important. The most important thing is eas of pulling that data so we can do searches. Also I would like to know if the data is updated by linked tables will changes in the new table/query update the source tables. Thanks for any help! Mark Create a new (empty) database. Link to the tables in each of your (?!) 15 independent databases. Create a new table to hold the data in the 15. Create append queries to go from #1 to New, from #2 to New, ... By the way, if the independent databases use an AutonumberID for the contact record, you will NOT want to try to append with those IDs -- they could conflict among the 15. If it is critical to know from which independent db the record came, add a field to the new db table that holds the "name" of the owner of the independent db, and modify your append queries accordingly.  SignatureGood luck Jeff Boyce <Access MVP> > Ok... I have a fairly complex set of databases that I use in a Call center. > Each user has a separate database (I did this because after about 6 users [quoted text clipped - 26 lines] > > Thanks for any help! Ok I was actually playing with this while waiting on a response... the problems I see with this solution (please excuse my ignorance) 1) The append is being done to "look up" data across the multiple databases, the databases are being updated continuously, so every update I would need to run the append again to get the data. 2) When I rerun the append I get an error because it is trying to re import the same data a second time. 3) any updates would only exist in the appended table, they would not drop down to the origional tables. Is there any way to not have these problems? Also, I want to explain my madness.... I used to have all this data in one table. I had approx 4000 records and 15 users accesing the data. It got VERY VERY slow, so slow that it was almost unbearable. Also we were loosing records. I am not sure if people were deleting them or overwriting them or what but they were dissapearing. I would be all for moving the data back into 1 table and leaving it there but I have a server/client environment that will be growing to over 50 reps. As slow as it was at 10-15 I was afraid of what 50 would be like. If I do get stuck with the append query method do I have to use 15 different queries? >Also, I want to explain my madness.... > >I used to have all this data in one table. I had approx 4000 records and >15 users accesing the data. It got VERY VERY slow, so slow that it was >almost unbearable. Umm... then you need to redesign your forms and your queries, not your tables. If you had slow performance with 4,000,000 records I'd suggest moving the data to SQL/Server but - with decent indexes and form design - 4000 records is TINY and should give fractional-second response. In NO case would I recommend splitting the table into duplicate-structured segments. >Also we were loosing records. I am not sure if people were deleting them >or overwriting them or what but they were dissapearing. Again... form design. Did you have Deletes enabled on your form, or a delete button? Did you have some sort of checking? You can stop user deletions altogther, restrict deletions to administrators, or put doublechecks in the delete process. >I would be all for moving the data back into 1 table and leaving it there >but I have a server/client environment that will be growing to over 50 >reps. As slow as it was at 10-15 I was afraid of what 50 would be like. If you'll have 50 concurrently updating users, then true client/server (e.g. SQL or MySQL or Oracle) might be worth looking into - but even then, Access should be able to handle it. If you have fifteen separate databases then you can use a UNION query or an APPEND query to merge the data; however, in *neither* case will you be able to edit all fifteen tables simultaneously working on one recordset. Managing these fifteen databases (preventing duplicate entries across databases, finding and updating a record in any database, etc.) is going to be an absolute NIGHTMARE! I'd strongly suggest recombining your databases into one, and working on query and form design. You're solving your problem from the wrong end! John W. Vinson[MVP] Thanks... the info you gave is very helpful. 2 last questions... I do not have indexing turned on.... If I just index the records it sounds like that will make a huge difference?? Would running a "Front End" version of the program from the client computers with linked tables from the 1 big table help performance? >Thanks... the info you gave is very helpful. > >2 last questions... > >I do not have indexing turned on.... If I just index the records it sounds >like that will make a huge difference?? Would shifting your sports car out of first gear make a huge difference? I'd say so. You don't index "records"; you index fields. ANY field you're using for searching or sorting should be indexed; every table should have a Primary Key (which will automatically be indexed); any tables that are in a one-to-many relationship should have that relationship defined in the Relationships window, relational integrity enforced, which will also create an index. You should also use Tools... Database Utilities... Compact to compact the database. >Would running a "Front End" version of the program from the client >computers with linked tables from the 1 big table help performance? YES!!!!!! Any multiuser system *MUST* be split; otherwise you risk bloat, slow performance, and frequent corruption. See Tony Toews' performance FAQ at http://www.granite.ab.ca/access/performancefaq.htm for a whole raft of suggestions. John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.