Need multiple manager tables that can be sent to individuals

[Karen Burke]

Hi.

I'm trying to create a database for our use in HR that has separate tables
for each manager. Ideally, we'd have one Master table that contains all
employee data (name, job history, salary info, etc). I'd also have one table
with all the same fields for each manager to key in his/her employees' data.
I'd send each manager a blank table into which they can key their data. Once
it's complete, they'd email it back to me. I would then import it into the
Master table.

When I set up the Master table and then two individual managers tables, I
tried using the database splitting feature to get just one manager's table
out as a separate file. Unfortunately, it create an exact copy of my whole
database, including the Master table, the other manager's table, all the
reports, etc. Given the confidential nature of this data, I can only send a
manager's individual table data to him/her.

The first time we do this exchange with the managers, it won't be an issue
as all the tables will be empty. But once we have data in the tables, this
will be a real issue for us.

So is there any way to extract one table from a database, send it to a
manager (ideally as a form), have the manager complete the info and send it
back for import into the database?

Thanks!

 

[Philip Herlihy]

Hi.

I'm trying to create a database for our use in HR that has separate tables
for each manager. Ideally, we'd have one Master table that contains all
employee data (name, job history, salary info, etc). I'd also have one table
with all the same fields for each manager to key in his/her employees' data.
I'd send each manager a blank table into which they can key their data. Once
it's complete, they'd email it back to me. I would then import it into the
Master table.

When I set up the Master table and then two individual managers tables, I
tried using the database splitting feature to get just one manager's table
out as a separate file. Unfortunately, it create an exact copy of my whole
database, including the Master table, the other manager's table, all the
reports, etc. Given the confidential nature of this data, I can only send a
manager's individual table data to him/her.

The first time we do this exchange with the managers, it won't be an issue
as all the tables will be empty. But once we have data in the tables, this
will be a real issue for us.

So is there any way to extract one table from a database, send it to a
manager (ideally as a form), have the manager complete the info and send it
back for import into the database?

Thanks!

This must be a really common scenario, but it's one I haven't
encountered myself for many years. Keep a look out for other
suggestions here!

What we did (about 10 years ago) was issue the remote users with a Word
template containing a table which they were asked to fill in and email.
On arrival, we had programmed Outlook to detect the document, and
extract the contents table-cell by table-cell, updating the contents
into our database by allowing Outlook to interact with it. All worked a
treat, but it's a bit complex for your situation.

You'd probably want the remote managers to have their own database, and
if you do, you can use their copy to validate anything they try to
enter, so that dates are valid, and so on. The task is to exchange
groups of records between their databases and your master. Until 2007,
Access had a feature called Replication (which I've never used) which
was intended for this purpose. Provided you didn't need real-time
updates (i.e. they could bubble through over time) it reportedly worked
well. Since 2003, Office has a facility called InfoPath
which allows the creation of custom forms to validate and communicate
data. Never used that either!

One question - are these offices linked by a reasonably fast network
(better than a VPN)? If so, you could split the database into front and
back-ends (recommended anyway) and provide the managers with different
selections of data by linking their front-ends to separate back-end
tables, which you'd be able to combine using a Union query.

One way of exchanging groups of records is first to select the records
to be sent using a query or filter, and then use the Office Links
facility (under Tools menu in Access 2003) to "Analyse it with Excel" -
effectively exporting to an Excel file which can then be emailed and
imported into your Master. The reverse process could be used to send
the managers an up-to-date full copy of what the Master holds. To make
selection easier, you could provide a form which selected records by date.

HTH

Phil, London

 

[Ron2006]

An alternative that we used because, although we had access to the mdb
of tables on another network, the connection was way too slow to allow
actual updates.

1) Develop the application that the managers use for their updates and
have their application split between FE and BE.

2) Assuming that you have some type of connection between your network
and theirs, develop a batch file or execute VBA from your master
application to copy the entire tables mdb over onto your server with a
different name for each manager. This is messy since you will need
changes whenever managers change or you add new ones. Although, if you
use VBA you could have a master table that gave you the mappings and
names of each of the managers and that way you could easily change/add
managers. If you did the move one by one and copied the remote mdb to
a single mdb and did the append queries right then, it would be no big
deal to add or change or subtract managers. Could also use that as a
control table and do any subset of the moves you wanted with an
"Import now" check box. All sorts of variations come to mind when
using that approach.

3) Use a union query in your app or some append/update queries to
combine the tables.

We did this with a large mdb that was residing on a server in Manila
and we were in Colorado. In that process we copied it over, did a
merge and update of information into our files, and then copied our
tables MDB back to Manila. Worked like a charm.

Ron

 

[Noëlla Gabriël]

Hi Karen,

Two easy ways to solve this :

1/ if you can put the database on a network:
-------------------------------------------
Use the Access security (or a self written security system) and give them
only access to a form based on a query that returns only the data of the
logged in manager. In this way they can add, or update their data directly
on the database and that's it.

2/ without network:
---------------------
Make for each manager an empty database.
Use make table queries to populate this database with new tables containing
only those data the manager can view.
When the database returns: use an append query to add the new records, and
an update query to alter the existing, changed data

 

[Fred]

Noella's #2 is a good idea, here's a couple thoughts under that.

What's implied is that you have a field in your master table which
identifies which manager "owns" this record, and then using that to select
records to make a table for that manager.

Noella's "append query" not implies that the managers will also be adding
new records. That's going to make your Primary Key field handling
complicated. If you have something like a corporate employee number
available to use as a natural key, that would solve that.

A simple answer (if it doesn't create too much work) is that NEW records
are added only on the master copy, and that the managers only add/edit the
data for records that are in there.

 

[Philip Herlihy]

Noella's #2 is a good idea, here's a couple thoughts under that.

What's implied is that you have a field in your master table which
identifies which manager "owns" this record, and then using that to select
records to make a table for that manager.

Noella's "append query" not implies that the managers will also be adding
new records. That's going to make your Primary Key field handling
complicated. If you have something like a corporate employee number
available to use as a natural key, that would solve that.

A simple answer (if it doesn't create too much work) is that NEW records
are added only on the master copy, and that the managers only add/edit the
data for records that are in there.

This is an interesting puzzle. I think the greatest risk is of
inconsistency between the Master and slave databases.

Here's a possible approach:
Managers enter their changes (add/update/delete) via a form which
provides any necessary validation. This generates "Change-request"
records which are saved to a dedicated table pending application to the
local database. On click of a "commit" button, a VBA module applies the
contents of the table to the main database as Append, Update or Delete
SQL statements, and also emails the contents of the table (as an
exported database or as a series of textual SQL statements) to Head
Office, where the same module can be run. There you have a mechanism
which goes some way to guaranteeing that the databases are kept in step.
Similarly, when HQ needs to distribute changes, they could send a
table of change requests to be run against the local databases. Needs
working out, but this might be a solution?

Of course, you need a mechanism to detect (at least) when an email has
been lost - a sequence number might be enough.

Phil