query in access

[Guest]

I am new to Access 2003. I work for an engineering firm. I have a table for
project numer and project name. I also have a table for our time sheets
where I input number of hours each engineer spent on each project at the end
of the month. How can I get a report shows all projects that have time for
that month and for each project it needs to show to-date the time have spent,
for example:

This is how I want it to look like:

07001 5/31/2007 10 hours
6/30/2007 8 hours
7/31/2007 20 hours

07002 6/30/2007 4 hours
7/31/2007 5 hours

Please help.

 

[Marshall Barton]

I am new to Access 2003. I work for an engineering firm. I have a table for
project numer and project name. I also have a table for our time sheets
where I input number of hours each engineer spent on each project at the end
of the month. How can I get a report shows all projects that have time for
that month and for each project it needs to show to-date the time have spent,
for example:

This is how I want it to look like:

07001 5/31/2007 10 hours
6/30/2007 8 hours
7/31/2007 20 hours

07002 6/30/2007 4 hours
7/31/2007 5 hours

Does this kind of query do what you want?

SELECT ProjectNumber,
MonthDate,
Sum(hours) As TotalHours
FROM ProjectHours
WHERE Max(MonthDate)) = Forms!someform.txtRptDate)
GROUP BY ProjectNumber, MonthDate

 

[Guest]

Try these two queries ---
Huynh_2
SELECT PROJECT.ProjectNum, Sum(PROJECT.Hours) AS SumOfHours
FROM PROJECT
GROUP BY PROJECT.ProjectNum;

Huynh_1
SELECT PROJECT.ProjectNum, Format([YourDate],"yyyymm") AS [Work Month],
Sum(PROJECT.Hours) AS [Month Hours], Huynh_2.SumOfHours AS [Project Total]
FROM PROJECT INNER JOIN Huynh_2 ON PROJECT.ProjectNum = Huynh_2.ProjectNum
GROUP BY PROJECT.ProjectNum, Format([YourDate],"yyyymm"), Huynh_2.SumOfHours;