Multiple Location Question

[briank]

My company has 30 offices with their own seperate "drive"
on our network system. I would like to consolidate data
into an Access database. I'm not sure whether to set up
one file with 30 tables or 30 files with one table each
and then brought together with one query. How does speed
and accessibility relate in this type of scenario?
Thanks.

 

[Bruce M. Thompson]

My company has 30 offices with their own seperate "drive"

on our network system. I would like to consolidate data
into an Access database. I'm not sure whether to set up
one file with 30 tables or 30 files with one table each
and then brought together with one query. How does speed
and accessibility relate in this type of scenario?

If each office's data consists of essentially the same structure, lean toward
one file with one table (is this the entire application??) and include a field
to identify the "office" to which each record belongs. I can't say anything
about your table structures as I haven't any knowledge regarding what this data
represents or how it is used. If no database has yet been built, then you might
want to think carefully about how you design your application and data structure
as any decisions made now may follow you for some time to come.

 

[Jeff Boyce]

Brian

If your network is a WAN (not a LAN), you risk transmission interruptions
and corruption problems in Access.

You don't mention how many users are involved, nor the use (lookup versus
transaction/data entry), nor the number of records, nor the criticality of
having any data entry transactions "logged", nor the equipment/software, nor
what kind of data, nor ...

Kinda tough to make more meaningful recommendations without more info...

Jeff Boyce
<Access MVP>

 

[briank]

Specifically, my company uses a LAN, for each office
location there is probably one data entry person on a
regular basis and the occasional manager who oversees the
work. The records entered per location is hard to guess
but I would say 20/week. I envision each location needing
about 6 tables with a few queries rounding this data into
a usable format. Time spent in the tables by each data
person could be most of the day.
So I am still wondering if it better for each location to
have their own seperate file with 6 (or so) tables and
thus I can link this data, along with 30 other locations
with exact setups, into a "master" file. Or perhaps I can
set all 30 location into a main file. Doing high powered
math that would be about 180 tables.
Your thoughts.

-----Original Message-----

If each office's data consists of essentially the same structure, lean toward
one file with one table (is this the entire

application??) and include a field

 

[Bruce M. Thompson]

Specifically, my company uses a LAN, for each office

location there is probably one data entry person on a
regular basis and the occasional manager who oversees the
work. The records entered per location is hard to guess
but I would say 20/week. I envision each location needing
about 6 tables with a few queries rounding this data into
a usable format. Time spent in the tables by each data
person could be most of the day.
So I am still wondering if it better for each location to
have their own seperate file with 6 (or so) tables and
thus I can link this data, along with 30 other locations
with exact setups, into a "master" file. Or perhaps I can
set all 30 location into a main file. Doing high powered
math that would be about 180 tables.
Your thoughts.

Performance wise, trying to bring together that many tables into a single
operation would likely be ***really*** slow. I still think that my original
suggestion would apply. If the relevant main records were to contain a field to
identify the "office", then all such main records and their child records could
easily be filtered as needed and you would still only need to deal with 6
tables. I can't even imaging trying to bring together 30 separate Parent/child
data structures in any type of operation/calculation scenario. If this database
is likely to exceed hundreds of megabytes in size or the number of users
performing contemporaneous edits could exceed 20 or so, moving to a more robust
database, such as SQL Server, might be a consideration (I am not, however, the
one to answer questions about SQL Server <g>).