Making a magical Query

[Nic]

I've been brainstorming how this would work for awhile now, so decided maybe
the really nice newsgroup people can help me.

Here is the scenario:
I've got the following tables

tbl_Casts
| Cast ID | .....other non-important (to this post) fields

tbl_Casted_Employees
| Cast ID | | Employee ID | | Budgeted Hours |

tbl_Casted_Cases
| Cast ID | | Case ID |

tbl_Employees
| Employee ID | | FirstName | | Last Name| .....ect

tbl_Cases
| Case ID | | Case Number | ....other fields

The idea is you make a (Budget) "cast", put in the Cases that are part of
the cast, and then add employee's that are working in those cases with their
expected number of hours.

So one to many relationships between tbl_Casts to tbl_Casted_Employees and
tbl_Casted_Cases

My current query takes each Employee ID, and Budgeted Hours, and compares it
against their actual hours which is a function that goes through each Case
ID within the cast and adds up the hours worked on per employee.

So lets say Cast 1 includes cases 03H001 and 03H001-D, in which the
following employee's are budgetted for and they have entered time in both of
those cases that combined add up to the Actual Hours column.

So my query looks like:

| Cast ID | | Employee ID | | Budgeted Hours | | Actual Hours |
1 1 8
5
1 2 10
12
1 3 2
20

I've got this to work fine, but now here is where some magic comes in. I'd
like to also add in Actual hours worked by Employee's that weren't in the
original "Cast". So the Cast may have Employee's 1,2 and 3, but with our
billing system, Employee 4 may have added time to a Case that is in the
cast. Which the system needs to be flexable enough to handle Employee's
that weren't in the original budgetted cast.

The result from my now magical query is:

| Cast ID | | Employee ID | | Budgeted Hours | | Actual Hours |
1 1 8
5
1 2 10
12
1 3 2
20
1 4 0
10

The problem is, since Employee ID 4 would not be in the child table
(tbl_Casted_Employees) this becomes more difficult.

Any ideas? Can I pragmatically generate the query using a function? Can a
function return a table of information to be supplied in a chart?

I hope all of this makes since. Let me know if you need more information.
Thank you so very very much for reading all of this!
-Nic

 

[Sam]

I think I get the idea, I just can't see where you're storing the actual
hours worked by employees on each case. Is there another table holding
CaseID, EmployeeID and ActualHours? Or have I missed something?

Perhaps you could post the SQL from your "non-magical" query to provide
further info.

Sam