Re: Need multiple manager tables that can be sent to individuals

Karen Burke wrote:
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
.

Relevant Pages

  • Re: Add a default form for start-up
    ... Are you actually splitting the database into 70 separate databases to be ... maintained separately or does each manager daily query the main database to ...
    (microsoft.public.access.modulesdaovba)
  • RE: Need multiple manager tables that can be sent to individuals
    ... Use the Access security (or a self written security system) and give them ... on the database and that's it. ... Make for each manager an empty database. ... we'd have one Master table that contains all ...
    (microsoft.public.access.gettingstarted)
  • Need multiple manager tables that can be sent to individuals
    ... I'm trying to create a database for our use in HR that has separate tables ... we'd have one Master table that contains all ... with all the same fields for each manager to key in his/her employees' data. ...
    (microsoft.public.access.gettingstarted)
  • Add a default form for start-up
    ... I am writing the code to break an employee database down into 70 separate ... databases (one for each manager containing their employees only). ...
    (microsoft.public.access.modulesdaovba)
  • Re: Linked tasks - LOST LEVELS!!!
    ... We actually had a similar, I guess I'll call it fear, with the CAMs at ... scared and wanted to work on their plan off on their local hard drive ... You do NOT have to use copy/paste to create a master from the separate ... then the Program Manager needs to designate ONE ...
    (microsoft.public.project)
Loading