SUM querying

[cajsoft]

Hi I'm having a bit of a problem with the sql query below

SELECT [Table 1 Personal Details].Surname, Sum([Table 3 Daily Activity].[Public Holiday]) AS [SumOfPublic Holiday], [Table 3 Daily Activity].[Week Commencing], Sum([Table 3 Daily Activity].[Authorised Absence]) AS [SumOfAuthorised Absence], Sum([Table 3 Daily Activity].Holiday) AS SumOfHoliday, Sum([Table 3 Daily Activity].Sickness) AS SumOfSickness, Sum([Table 3 Daily Activity].[Unauthorised Absence]) AS [SumOfUnauthorised Absence], Sum([Table 3 Daily Activity].[Hours Per Week]) AS [SumOfHours Per Week], [Table 9 Funding].Project
FROM [Table 1 Personal Details] INNER JOIN ([Table 3 Daily Activity] INNER JOIN [Table 9 Funding] ON [Table 3 Daily Activity].[ID Number] = [Table 9 Funding].[ID Number]) ON [Table 1 Personal Details].[ID Number] = [Table 3 Daily Activity].[ID Number]
GROUP BY [Table 1 Personal Details].Surname, [Table 3 Daily Activity].[Week Commencing], [Table 9 Funding].Project
HAVING ((([Table 3 Daily Activity].[Week Commencing]) Between [Enter Start Date] And [Enter End Date]) AND (([Table 9 Funding].Project)="NETWORKS"))
ORDER BY [Table 3 Daily Activity].[Week Commencing];

My problem is that the Surname is displaying for each record.. what I mean is that I want the surname to be grouped so that I get the SUMofHours summed correctly?

here's an example of what I'm getting

SURNAME, WEEK COMMENCING, SUM OF HOURS, PROJECT

Brown,13/02/2006,35,NETWORKS
Brown,20/02/2006,35,NETWORKS
Malcolm,13/02/2006,35,NETWORKS
Malcolm,20/02/2006,35,NETWORKS

Here's what I'm really looking for though

Brown,13/02/2006,70,NETWORKS
Malcolm,13/02/2006,70,NETWORKS

I need the Hours SUMMED with no replication of surname.

Thanks
CJ


Thanks
CJ

 

[Brianwarnock]

Your problem is caused by grouping on week commencing, I think you should try changing that to a MIN,

Brian

 

[cajsoft]

Your problem is caused by grouping on week commencing, I think you should try changing that to a MIN,

Brian

thanks for the reply,
That has fixed my duplicate surname issue, but the totals are all wrong now..

 

[Brianwarnock]

Why are you selecting week commencing if you are not grouping on it?

I think that you may need to use a report or more than one query to achieve what you appear to want.

The first query ignores the dates but extracts sums and groups the data, then this joins with table3. I think, to find the MIN date for the user/project.

I would put your initial query into a report.

Brian