Duplicate Fields Need to be added together by name

[t5amec]

Hello

I am trying to create a report for a list of employees and the amount of hours worked and by what project.

the layout is as follows

Name
George Adams
Project  AmecProject  TotalHours
C        66502        40
C        66502        1008
C        66502        336
C        66502        40
C        66502        40
C        66502        40
C        66502        40
ADMIN                 8
H        66508        190

But I want it to come up like so;

Name
George Adams
Project  AmecProject  TotalHours
C        66502        1544
H        66508        190

etc

I just want the values within AmecProject field to be calculated per AmecProject code and to not show a value for the AmecProject fields that are blank.

How would I go about getting this done.

Thank you for your help in advance.

Matt



Make Sense? I hope so (-:

 

[ZmrAbdulla]

Hi,
Either you create a query that result only summary and create a report base to it.
or
When you create it by wizard Click Summary Options and check "Summary Only" and you are there.

regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com

 

[scriverb]

I would perform this grouping and summing operation in a query and then use that query as the record source for the report.

Select A.Name, A.Project, A.AmecProject, Sum(A.Totalhours) as TotalHours 
FROM [i][red]yourtablename[/red][/i] as A 
Group By A.Name, A.Project, A.AmecProject 
HAVING Not IsNull(A.Project);

I think query SQL should do it for you. Let me know if this works. Just name and save the query and then idetnify it as the RecordSource for the report.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[t5amec]

thank you very much, but how would i modify it so that the blank fields do not show up?



Make Sense? I hope so (-:

 

[scriverb]

Oh, sorry about that. I used the wrong field to check for nulls. Change the HAVING statement to the following:

HAVING Not IsNull(A.AmecProject);

Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]