Two way link task sheet - Excel/Access

Question

Currently at work I am trying to create a task sheet system. I work in a small/medium scale residential construction company.

Basically for each job there will be a Excel Master sheet with a variety of tasks for several different people, from this I'm looking to create a task sheet specific to each person. I would like to be able to add a task on the Master sheet and it appear on the respective person's page. As well as this, if the person makes a note on their personal sheet then it would link back to the Master.
However the complicated part is that there is constantly multiple job's with their own Master sheet and I want each of these to contribute to the persons overall task list.
Could anyone please suggest how I go about this? I have some experience with Excel VBA but am pretty new to Access.

I understand that I likely need to link each Job Excel Master Sheet to an Access Database and then somehow distribute these out to each individual task sheets. However I am struggling to understand how it would be possible add a task to the Master, which would be assigned to a person. This task would then appear on that persons sheet where they can add notes to it or mark it as complete and this would reflect back to the Master for that Job.

Hopefully I have been relatively clear and haven't repeated myself too much.
Any help would be great!

Answer 1

Hi Rory,

This sounds like a big job if you're trying to convert an Excel system into Access. You'll have to get familiar with the "Normalization" principles and set up your table structure first because the table structure will become the foundation for your database application. A badly designed table structure will result in a hard to maintain database application.

Answer 2

Spreadsheets and relational databases are very different in both concept and methodology.  The latter models the reality in terms of its entity types and the relationship types between them, with each entity type being modelled by a table, with each column in the table representing an attribute of the entity type which is determined solely by the whole of the table's primary key.  So, a very basic model for what you describe would comprise tables, in broad outline, as follows:

Jobs
….JobID  (PK)
….Job

Tasks
….TaskID  (PK)
….Task
….JobID  (FK)

Employees
….EmployeeID  (PK)
....FirstName
....LastName

The task sheets entity type is, in database terms a many-to-many relationship type between tasks and employees.  Such a relationship type is modelled by a table which resolves the relationship type into two one-to-many relationship types:

TaskSheets
….TaskID  (FK)
….EmployeeID  FK)
….DateCompleted

The primary key of this table is a composite of the two foreign keys.  Note that the table does not include a JobID column as this is determined by TaskID, so to include a JobID column would introduce redundancy as it would be determined by only one part of the composite primary key.  The table would consequently not be normalized to Third Normal Form (3NF) and therefore open to the risk of update anomalies.

You cannot link a set of related tables like the above to a single Excel worksheet.  To structure the data in this way it would be necessary to import it into Access and then decompose it into a set of predefined tables.  You'll find an example of how this is done in DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file data is imported from Excel and then decomposed by means of a set of 'append' queries executed in a specific order.  A brief explanation of each stage in the process is given.

While it would then be possible to edit the data in Access, this would not be reflected in Excel.  The only way in which the Excel worksheet could be updated would be by means of automation, which is far from trivial.  I'm not sufficiently familiar with the Excel object model to be able to advise on this.  It would be far simpler if everything were maintained in Access.  Each employee would then be able to edit their own task sheets, which could be per job, or for all jobs, by means of forms based on queries restricted to the current employee.

---

Ken Sheridan, Stafford, England