I need to Merge Multiple Tables

[Mark Carlyle via AccessMonster.com]

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!

 

[Jeff Boyce]

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.

 

[Mark Carlyle via AccessMonster.com]

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?

 

[Mark Carlyle via AccessMonster.com]

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.

 

[Mark Carlyle via AccessMonster.com]

If I do get stuck with the append query method do I have to use 15
different queries?

 

[John Vinson]

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]

 

[Mark Carlyle via AccessMonster.com]

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?

 

[John Vinson]

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]