Access Query Calculation Based on Field Value

Question

Hi,

I'm hoping someone can help with an Access query I am trying to create.

I have 3 tables.  One is a list of small projects referred to by a unique identifier.  The table includes specifics of the job.  The next is a table of employees who work on the jobs.  Many people may contribute to each job.  This table includes employee specific details including charge out rates.  The third table lists all of the work  (in hours) that the employees contribute to a project.  That is for each project if a person works on it for 4 hours this forms a record in this table.  As I said there can be many resources working on one project.

My difficulty has arisen by a change in the charge out rates for the employees.  I had been using a query to calculate the total cost of a project based on the hours worked by each employee and their charge out rate.  Now I have a need to apply different charge out rates to different projects and still need to calculate the total cost of each project.  I was intending to add a new field to the employees table with the new charge out rates in it.  I was then going to put a new field in the project table that nominated which set of rates applied to each project.  My problem is when I come to use a query to calculate the total cost of each project I can't figure out how to make the query use the appropriate rate set for each project.

If that makes sense could someone who knows a bit more about Access than me suggest how I need to set this up to make it work?

Thanks.

Answer 1

From your description you have a many-to-many relationship type between employees and projects, so this would be modelled by three tables like this:

Employees---<ProjectEmployees>----Projects

The charge out rate is an attribute not of Employees (but see final para below), but of EmployeeProjects, so is a column in the latter:

ProjectEmployees

....ProjectID  (FK)

....EmployeeID  (FK)

....ChargeOutRate

The primary key of this table is a composite one of the two foreign keys ProjectID and EmployeeID.  ChargeOutRate is a non-key column whose value is the hourly charge out rate which can differ per employee per project.  In the language of the database relational model it is functionally determined by the key of ProjectEmployees, rather than by the key of Employees, which appears to be the determinant in your current model.

You then have a one-to-many relationship type between ProjectEmployees and the work log per employee per project, so the latter would be modelled by a table like this:

WorkLog

....ProjectID  (FK)

....EmployeeID  (FK)

....DateWorked

....HoursWorked

In this ProjectID and EmployeeID represent a composite foreign key referencing the composite primary key of ProjectEmployees.  The primary key of this table is a composite one of ProjectID, EmployeeID and DateWorked, with HoursWorked representing a non-key attribute.

With the above you will have no problem returning the total charge out amount per project per employee by joining the tables, grouping by project and employee and summing the HoursWorked*ChargeOutRate values.  A query to compute the total charge out amount per project would join the tables in the same way, but be grouped solely by project.

If there is a current default charge out rate per employee this can be a ChargeOutRate column in Employees and the current value assigned to the ChargeOutRate column in ProjectEmployees when a row is inserted into that table via a form.  The default value can then be edited if necessary.  This is directly analogous to the very common situation in an ordering or invoicing database in which the current unit price of a product is looked up from a Products table and assigned to a UnitPrice column in an OrderDetails table by means of code in the ProductID control's AfterUpdate event procedure.  This value can ne edited if necessary, and will remain static for each order regardless of subsequent changes in the product's unit price.

Answer 2

Scott

Thanks for your reply.

There has been one change in the rates that affects some employees but not all. From now on costs for new projects needs to be calculated based on the new rates but the old rates still apply to old projects. So I need 2 rate sets, and need to be able to nominate which one applies to which project. Does that help?

Answer 3

.........I don't see where a separate EmployeeProjects table is needed. 

I don't see how it can be avoided while satisfying the requirements of the logical model.  There is undoubtedly a relation PE in the model, which should be implemented as a table.  If ProjectEmployees is excluded there are two immediately apparent potential detrimental consequences, and possibly more:

1.  An invalid row can be inserted into WorkLog for an employee not assigned to the project in question.

2.  The fact that an employee assigned to a project only becomes known once they have completed a day's work on the project.  The words chicken and egg spring to mind!

I should perhaps point out that even now the model I proposed is not in fact complete.  There is also a relation PW with attributes project and work date, which could be modelled by a further table with columns ProjectID and ProjectDate.  The columns ProjectID and DateWorked in WorkLog thus become a composite foreign key, so the key of that table is made up of two overlapping foreign keys.  This is a very common pattern which is encountered in many tables which model a relationship type between two further relationship types as here.

Taking it even further there is also a relation EW with attributes employee and work date, so this could be modelled by a table with columns EmpoyeeID and DateAvailable, and the columns ProjectID and DateWorked in WorkLog thus become another composite foreign key, and the key of that table becomes one of three overlapping foreign keys.

All of the above are self evidently valid relations which emanate from an analysis of the logical model, so where do you stop normalizing by decomposition?  A friend of mine who's a Professor at a UK university, but also used to write a databases column in a magazine, once interviewed Chris Date, and posed this question to him. He didn't have a hard and fast answer, but pointed out that theoretically you could go on decomposing tables until every table had only two columns, so you have to use your judgement when to call time.  I would certainly not exclude something as fundamental to the model as the ProjectEmployees table in the current context, but how far I'd go beyond that would be decided on whatever factors are most relevant to the functionality of the database.

As regards the ChargeOutRate column, I don't think it's a question so much of where it goes, but whether it goes in one or two tables as separate attributes functionally determined independently by the key of each.  The OP said in his original post that it was employee determined; the question is whether it is still so, in addition to being employee/project determined.

Answer 4

Ken,

I'm going to disagree here, at least partially. I agree there is a many to many relation, but I don't necessarily agree that a separate junction table is needed. Since the WorkLog table you propose or the existing 3rd table the OP spoke of should have both ProjectID and EmployeeID, I don't see where a separate EmployeeProjects table is needed. 

But I agree the actual rate should be stored not just the FK as I mentioned, since it is most probably time sensitive. I'm still not sure where it should go. That depends on the answer to the question I asked.

Answer 5

So you have a many to many relationship between employees and projects. The third table then functions as your junction table because it has the ProjectID and EmployeeID to model the relationship. This table (I'm assuming) also has fields for the # hours and date those hours are worked.

What I would do is setup a rates table, because I'm guessing that the rates are not different for each single employee and project. The next question is where to put the foreign key for the rate. Is the rate the same by project, by employee or some combination?