adpd

I am a little rusty with the old Access 2000 (to say the least).

I am trying to write a query that, between a user defined date range, will display a list of projects (by project code) and the employees who have been working on that project (showing the time that they have worked on that project and the billable amount that has been accumulated).

The problem is that for each project, there are multiple instances of employees - I need to sum their time together and display it once for each project.

If anybody has any idea how to help me, please let me know.

If you need anymore information, please ask.

Thanks in advance.

SJ_Skyline

Well as a starter I would grab and group the data from the employees table:

SELECT intProjectID, sum(intBillableTime)
FROM tblEmployees
GROUP BY intProjectID

Then link this subquery through to your projects table performing a join on intProjectID

Sorry - monday morning, my brain is not up to speed yet!

adpd

SJ_Skyline, I appreciate all the help.

I will give it a go.

ChrisB

To do it in a report:

Add a Group Footer to a report, grouping on the Project ID (done through the Sorting & Grouping box (on the View Menu)). Leave it set to Group On "Each Value"

In there you can put a Calculated Control which sums up the time field for that project.

Each project ID will have it's own instance of the footer and hence control with the total time.

HTH,

Chris.