Append 5 Tables to One Table Nightly

[pete.trudell]

Morning, I have 5 databases in 5 locations collecting data all day long
on 5 seperate databases(done this way because of slowdowns in
Networking). I need to append the daily data to a master table for
special reports and such.

The structure of the tables are the same, and their is no likelyhood of
duplicates. The master Table-Master Database will only be access by a
limited number of staff.

Any suggestions.

 

[Joseph Meehan]

You could automate the process any of several ways, but first I have to
ask. You say you had to do it this way because of speed problems on the
network. Were you working with a split system? That is did you have five
(or more) front end files and one back end file? If not, I suggest that
might be a far better answer.

 

[pete.trudell]

Each location has a Backend and Frontend which about 10 people use all
day, daily.

What I was able to do is design an Append Query which update One Master
Table which each of the locations will update daily (usually before
start of day). One staff members Frontend , has a link to that Master
Table so they can run Reports in each Location which has all the
Locations Data. Now I want to streamline the procedure, if possible.


The reason we have network problems is that we have over 100 people in
each office using a Mainframe connection to do our basic work, this
Database is to handle all the Problem Calls we get. Plus we are
handling work in 5 location some 1000 miles apart. We tried to put
the Backend on one shared Server with Frontends at each office, but we
had to wait up to 20 - 45 seconds for forms and reports to populate....
that's too long, Governor will not accept that. As it is, each location
will get Problem calls from each other, sort of share the problem (keep
the Help Staff busy).

By putting both the Frontend and Backend on local servers at the
location, we speed up the process to an acceptable 2-4 seconds. Now we
just need to get the table data to a Master Table so we can get reports
that separate out the Locations and compiles activity, daily, weekly,
monthly.

Thanks