Urgent Query Help Needed.

[kbolick]

I am in urgent need of some help with a query. I have the following tables in which I need to compile data in a format similar to a pivot table in Excel, but need this in an MS Access report format. My tables are Employee (list of employees), Activity (activities performed), Time Tracking (time logged per task, date, employee), and Volume (data comes from another system that collects volume data). I need to be able to determine, within a date range specified, the tasks performed, the total number number of items, and the total amount for each task by employee. I need to be able to show all tasks and all employees regardless if the task was performed or if the employee performed any tasks in the date range. The end format would be:

Order Items|Order Amt|Deposit Items|Deposit Amt|Ship Items|Ship Amt
Employee



Employee
Id | Name
1 | David
2 | John
3 | Debbie
4 | Jason

Activity
A_Id | Activity
1 | Orders
2 | Deposits
3 | Shipment

Time Tracking
Employee_Id | Activity_Id | Date | Hours
1 | 1 | 10/1/2008 | 5
1 | 2 | 10/1/2008 | 3
2 | 2 | 10/2/2008 | 10
3 | 2 | 10/2/2008 | 8


Volume Data
Date | Activity | Amount | Items | Employee_Id
10/1/08 | 1 | 50.00 | 5 | 1
10/1/08 | 1 | 1000.00 | 20 | 1
10/1/08 | 2 | 750.00 | 20 | 1
10/2/08 | 2 | 100.00 | 10 | 2
10/2/08 | 2 | 100.00 | 1 | 3


Result should be for time frame 10/1/08 – 10/14/08

Orders Amount | Orders Items | Deposit Amountt | Deposit Items | Shipment Amountt | Shipment Items
1 | David 1050.00 | 25 | 750.00 | 20 | 0 | 0
2 | John 0 | 0 | 100.00 | 10 | 0 | 0
3 | Debbie 0 | 0 | 100.00 | 1 | 0 | 0
4 | Jason 0 | 0 | 0 | 0 | 0 | 0

If you notice, no one performed a shipment. Also, Jason did not perform any of the tasks. Your assitance would be greatly appreciated.