Cannot Figure this query out

[ryan1313]

I have attached a simplified version of what i am trying to achieve in one of my databases.

In this example, i have 4 tables.

tblEmployee
tblTask
tblBudget
tblSpent

I am trying to write a query that will return all budgets and hours spent for each of the tasks. I want the results to show Total budget and spent hours per employee per task including Employees that have spent hours against a task that they do not have budgets for.

So with the tables i have attached The results would need to show

Task Employee BudgetHours SpentHours
--A-----1----------10----------5
--A-----2----------0-----------2
--B-----1----------10----------0
--C-----1----------10----------0

Thank you, and I apologize if I made this sound confusing.
Ryan

[ajetrumpet]

this is almost impossible until you establish relationships with your tables. you need to relate the common fields together so you don't have to constantly use lookup functions in the query, if that's even possible.

get that first then someone I'm sure will be able to help you out with the data pull

[ryan1313]

There are relationships in my main database, like i said this one is an example i quickly through together. Not sure why it is impossible to do this without them since there are secondary keys in each table that can be linked in joins in the query or in SQL. I don't see the point of setting an inner join in the relationship tables if i have to change it in a query to a an outside join or vice versa to get the results i want.

[ryan1313]

Ok, i figured out what i can do. Not sure if it is the most efficient, but it will work.

I will just create an append query when spents come in that will append a 0 budget to the budget table for that employee on that task if they do not already have a budget.

Thanks to ajetrumpet and all others that took a look at this!

---ETA: How do i mark this as solved? Checked forum FAQs and didnt see anything there.

Ryan

[jzwp11]

You can do this with a series of queries with the proper join types. This is the approach I used.

First, create two queries that get the sum of hours by task and employee for each type of hours (budget or spent). I assume you will need a total of hours of each type even though your example data did not have multiple records with the same employee/task combination.

BTW, since the two tables (tblSpent and tblBudget) have essentially the same structure, you should only have 1 table to hold all of the data. I would add a field to distinguish the type of hours (spent or budget).

Continuing using your current structure...

query name: qryBudgetTaskEmployee
SELECT tblEmployee.EmployeeID, tblBudget.TaskID, Sum(tblBudget.Hours) AS SumOfHours
FROM tblEmployee LEFT JOIN tblBudget ON tblEmployee.EmployeeID = tblBudget.EmployeeID
GROUP BY tblEmployee.EmployeeID, tblBudget.TaskID
HAVING ((Not (tblBudget.TaskID) Is Null));

query name: qrySpentByTaskEmployee
SELECT tblEmployee.EmployeeID, tblSpent.TaskID, Sum(tblSpent.Hours) AS SumOfHours
FROM tblEmployee LEFT JOIN tblSpent ON tblEmployee.EmployeeID = tblSpent.EmployeeID
GROUP BY tblEmployee.EmployeeID, tblSpent.TaskID
HAVING ((Not (tblSpent.TaskID) Is Null));

Now create another query that gets the Cartesian product between the task table and the employee table since an employee might work on a task for which they are not budgeted.

query name: qryTaskEmployeeCartesianProduct
SELECT tblTask.TaskID, tblTask.Task, tblEmployee.EmployeeID, tblEmployee.EmployeeName
FROM tblTask, tblEmployee;

Now create another query that joins the Cartesian product query with one of the earlier two queries (I picked the budget one)


query name: qryTaskEmployeeBudgetHours
SELECT qryTaskEmployeeCartesianProduct.TaskID, qryTaskEmployeeCartesianProduct.Task, qryTaskEmployeeCartesianProduct.EmployeeID, qryTaskEmployeeCartesianProduct.EmployeeName, IIF(IsNull( qryBudgetTaskEmployee.SumOfHours),0,qryBudgetTaskE mployee.SumOfHours) AS BudgetHours
FROM qryTaskEmployeeCartesianProduct LEFT JOIN qryBudgetTaskEmployee ON (qryTaskEmployeeCartesianProduct.EmployeeID = qryBudgetTaskEmployee.EmployeeID) AND (qryTaskEmployeeCartesianProduct.TaskID = qryBudgetTaskEmployee.TaskID);

Now create a final query that uses the qryTaskEmployeeBudgetHours and the qrySpentByTaskEmployee

query name: qryFinal

SELECT qryTaskEmployeeBudgetHours.TaskID, qryTaskEmployeeBudgetHours.Task, qryTaskEmployeeBudgetHours.EmployeeID, qryTaskEmployeeBudgetHours.EmployeeName, qryTaskEmployeeBudgetHours.BudgetHours, IIF(isnull(qrySpentByTaskEmployee.SumOfHours),0,qr ySpentByTaskEmployee.SumOfHours) AS SpentHours
FROM qryTaskEmployeeBudgetHours LEFT JOIN qrySpentByTaskEmployee ON (qryTaskEmployeeBudgetHours.TaskID = qrySpentByTaskEmployee.TaskID) AND (qryTaskEmployeeBudgetHours.EmployeeID = qrySpentByTaskEmployee.EmployeeID)
WHERE Budgethours<>0 or IIF(isnull(qrySpentByTaskEmployee.SumOfHours),0,qr ySpentByTaskEmployee.SumOfHours) <>0;

I've attached your database with all of the queries I suggested above.

[ryan1313]

jzwp11,

Thanks a lot for this! Works much better than my work around!

Ryan

[jzwp11]

You're welcome. Glad I could help out.