Departmental Database Design

[Guest]

I am designing a database that will include data for several departments but
must be capable of producing combined Company-wide reports. To ease
departmental data input and reporting a user must be able to identify their
department when they open the database and from then on they will only
see/update their own data.

I have a Departments table with department name, etc and had thought of
adding a field to this table to identify which department is currently
active, but then realised that it won't work if two departments are using the
database simultaneously. Is there any way that a private copy of a table can
be made for each user?

An alternative idea is to use a field on a form or a global variable that is
set when the database is opened. Would either of these work in a multi-user
environment?

Thanks,
Graham.

 

[Allen Browne]

Graham, if you have multiple users, you *really* need to split the database
so that each workstation has a local copy of the program, which links to the
tables in a shared location. You can then have a local table in the front
end for the info you wanted to use.

If splitting is a new idea, see:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

 

[John Vinson]

I am designing a database that will include data for several departments but
must be capable of producing combined Company-wide reports. To ease
departmental data input and reporting a user must be able to identify their
department when they open the database and from then on they will only
see/update their own data.

I have a Departments table with department name, etc and had thought of
adding a field to this table to identify which department is currently
active, but then realised that it won't work if two departments are using the
database simultaneously. Is there any way that a private copy of a table can
be made for each user?

An alternative idea is to use a field on a form or a global variable that is
set when the database is opened. Would either of these work in a multi-user
environment?

Allen is *ABSOLUTELY* correct about splitting the database. That's an
essential first step.

Don't think about having "a database for each department". That's the
wrong approach! Instead, have one database with multiple "frontends",
one for each department. All departments will use the same tables, and
the same forms; but each departement can base its forms on a Query
selecting only the data for their department. This can be done by a
very simple customization of each frontend, changing the department
criterion in the query.


John W. Vinson[MVP]