initial database design help

[louis-m]

Hi,
we are a small company with 10 employees in the office (payroll, accounts, orders etc) and 30 remote sites with 2 full time & 5-10 part time employees per site.
all sites are vpn'd into the main office. we've been running weekly excel sheets in which the manager of each site logs on via terminal services and enters wages, costs incoming cash etc on a daily basis.
we knew last year that we would soon outgrow the weekly sheets as we have the data but have no way of comparing it without manually cross checking or using somebody to data input from those.
so naturally, we want to progress to access to make things easier.

are we better to use a seperate database for each site or use just one big database for the lot eg wages, costs, cash with each site having restricted access and the office having more detailed reports. i do not want any site to see the other site data eg wages.
from what i can picture, the database is going to have to be split with each site having a accde or mde file which is customized to that site and will only run queries that are relevant to that site.

last question (i promise)....
am i better to make tables say for each sites wages eg site1 wages, site2 wages OR just make a wages table with a field in it for each site eg site1, site2 that is automatically entered from a customized site form?

any pointers would be appreciated.
thank you
louis

 

[KenHigg]

I would consider doing a similar db for each location. As data is entered it at the remote locations it would not be visible realtime to the main office, but you could replicate the data once a day to a seperate local table and then using vba copy that table to the main office server. Then you have visibilty at the main site to the replicated data.

Would that work?

 

[Rabbie]

Also look at splitting your DB into Front End(Reports,forms,queries etc)/Back End(Data tables). This way the replication will work better.

It is always good practice to make sure each user has their own copy of the Front End. This significantly reduces the risk of Database Corruption.

 

[louis-m]

thank you very much for the prompt replies.

at the current time, i am considering multiple front ends and a single backend in a hidden share.
i envisage having a template of a frontend which i will alter the queries so that site 1 can only extract data that has been entered by site 1.
prior to fe distribution, i will change the site1 queries to site 2 queries and then make the fe an accde or mde file.
i will also have a 2nd template which will be solely used for the office and their reports etc. and this will be made an accde file prior to distribution.

once i get the initial database to how we like it, i should imagine i have to split it and work on the individual fe's from there.
am i thinking in the right direction. is this database likely to be overloaded with say 10 consecutive users during the day?

regards
louis