I need help for a Query in access

[Guest]

Hi, I'm a begginer in Acces, so probably I will ask you with a basic question.

I have a table with the following records:
Table Name is TB_Tst
- Project
- Date
- Hours
And I want to have the total o hours per month:
The query result is:

-Project
- Year
- Month
- Total of Hours

I made the query bellow, but the sum of hours is wrong, could you help me?

SELECT TB_Tst.project as Name,Year(TB_Tst.Date) as Year,
Month(Cdate(TB_Tst.Date)) as Month, sum(Cdate(hour)) as Hours
GROUP BY Name, Year, Month

The hours output is the hours in the first day of month.

Ex:
Project: test
Date: 01-01-2005
Hours: 10:00
Project: test
Date: 01-01-2005
Hours: 5:00

The query output is:
Project:test
Year: 2005
Month:1
Hours: 10:00 (Is the first input in the table, In this field I want 15:00)

Could you help me?

Thanks a lot

 

[Tom Ellison]

Dear Fred:

I may have a clue to what is wrong.

Generally, you cannot GROUP BY aliased values. I would change the query as
follows:

SELECT * FROM (
SELECT TB_Tst.project as Name, Year(TB_Tst.Date) as YearPart,
Month(Cdate(TB_Tst.Date)) as MonthPart, sum(Cdate(hour)) as Hours) X
GROUP BY Name, YearPart, MonthPart

I have also changed the aliases for Year and Month. It is best not to name
columns with names that are also the names of functions. Probably this is
not the source of a problem here, but still I find this to be good practice.

Any luck with this?

Tom Ellison

 

[Guest]

I have the same problem. I cannot have the total of hours.

 

[Tom Ellison]

Dear Fred:

On further review (see referee at replay booth) I see I left out something
important. Duh!

SELECT * FROM (
SELECT TB_Tst.project as Name, Year(TB_Tst.Date) as YearPart,
Month(Cdate(TB_Tst.Date)) as MonthPart, sum(Cdate(hour)) as Hours
FROM YourTable) X
GROUP BY Name, YearPart, MonthPart

I left out the "FROM YourTable" part. Interestingly, you didn't include
this in your original post either. "YourTable" could be the name of a table
or query.

Any better?

Tom Ellison