MS Access sum problem in query

[glen.riddell]

I have 2 queries, for the sake of this post I will refer to them as
query1 and query2. Basically query1 returns a number of results from
tables which are grouped and then query2 queries query1 and is
expected to sum the number of hours.

query1 returns the following rows:

PersonID StartDate EndDate Hours
=============== =============== =============
801 07/09/06 11/12/06 420
801 05/09/06 11/12/06 429
801 13/12/06 04/04/07 651

query2 comprises of the following query:

SELECT [query1].[PersonID], Min([Query1].[StartDate]) AS MinStartDate,
Max([Query1].[EndDate]) AS MaxEndDate, Sum([Query1].[Hours]) AS
SumOfHours
FROM [Query1]
GROUP BY [Query1].[PersonID], [Query1].[EndDate]

The outcome I currently get from query2 is:

PersonID StartDate EndDate Hours
=============== =============== =============
801 13/12/06 04/04/07 651

What I am after is:

PersonID StartDate EndDate Hours
=============== =============== =============
801 05/09/06 04/04/07 1500

Can anyone see why I am not getting this result??

Any advice would be appreciated.

Thanks

[Tom van Stiphout]

On 10 Apr 2007 19:40:59 -0700, gl**********@gm ail.com wrote:

I intuitively feel this sql is wrong, but I can't quite explain why.
My implementation is to create several queries, for example:
select PersonID, Min(StartDate)
from query1
group by PersonID

same for Max

And then create a third query, joining these two queries on PersonID.

-Tom.

>I have 2 queries, for the sake of this post I will refer to them as
query1 and query2. Basically query1 returns a number of results from
tables which are grouped and then query2 queries query1 and is
expected to sum the number of hours.

query1 returns the following rows:

PersonID StartDate EndDate Hours
============== =============== ==============
801 07/09/06 11/12/06 420
801 05/09/06 11/12/06 429
801 13/12/06 04/04/07 651

query2 comprises of the following query:

SELECT [query1].[PersonID], Min([Query1].[StartDate]) AS MinStartDate,
Max([Query1].[EndDate]) AS MaxEndDate, Sum([Query1].[Hours]) AS
SumOfHours
FROM [Query1]
GROUP BY [Query1].[PersonID], [Query1].[EndDate]

The outcome I currently get from query2 is:

PersonID StartDate EndDate Hours
============== =============== ==============
801 13/12/06 04/04/07 651

What I am after is:

PersonID StartDate EndDate Hours
============== =============== ==============
801 05/09/06 04/04/07 1500

Can anyone see why I am not getting this result??

Any advice would be appreciated.

Thanks

[Gord]

On Apr 10, 10:40 pm, glen.ridd...@gm ail.com wrote:

I have 2 queries, for the sake of this post I will refer to them as
query1 and query2. Basically query1 returns a number of results from
tables which are grouped and then query2 queries query1 and is
expected to sum the number of hours.

query1 returns the following rows:

PersonID StartDate EndDate Hours
=============== =============== =============
801 07/09/06 11/12/06 420
801 05/09/06 11/12/06 429
801 13/12/06 04/04/07 651

query2 comprises of the following query:

SELECT [query1].[PersonID], Min([Query1].[StartDate]) AS MinStartDate,
Max([Query1].[EndDate]) AS MaxEndDate, Sum([Query1].[Hours]) AS
SumOfHours
FROM [Query1]
GROUP BY [Query1].[PersonID], [Query1].[EndDate]

The outcome I currently get from query2 is:

PersonID StartDate EndDate Hours
=============== =============== =============
801 13/12/06 04/04/07 651

What I am after is:

PersonID StartDate EndDate Hours
=============== =============== =============
801 05/09/06 04/04/07 1500

Can anyone see why I am not getting this result??

Any advice would be appreciated.

Thanks

Don't GROUP BY [EndDate], just GROUP BY [PersonID].