Isolate recs based on sum of values

  • Thread starter Thread starter Bill Sturdevant
  • Start date Start date
B

Bill Sturdevant

I have a table of resource utlilization records.

Each record has an employee name, a project ID and 36
fields representing 3 years of monthly utilization in
percentages (an employee is going to work 90% of his time
on project X during October, 2004).

Each employee can be assigned to multiple projects, so
there will be one row for each employee/project
combination.

I want to be able to pull only those records where the SUM
of percentages for ANY given month of the 3 year span is
greater than 100% (to show projected over-utilization).

I can create a query to sum all the values for each
employee, and write code to loop through the results
isolating the ones where any month sum is greater than
100%, then go back and pull only those detail records for
those employees, but I would prefer to do this with a
single or combination of queries, if possible.

Any ideas on the simplest way to do this?
 
Bill

First, your table needs some normalization. Access is a relational
database, and its tools (say, Sum(), for example) are predicated on use of
relationally-designed data. What you describe is a spreadsheet.

Is there a reason you aren't using a spreadsheet instead?

An alternate design for the table, and one that would permit easy querying
as you've describe, might be something like:

trelUtilization
UtilizationID
EmployeeID (not name, ID, foreign key from a tblEmployee)
ProjectID (ID, foreign key from a tblProject)
UtilizationDate (full date/time field, formatted to display something
like MMMM, YYYY)

And what you describe sounds a lot like the automatic features in a project
management tool. Have you considered using one?
 
Back
Top