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

 

[Ken Snell \(MVP\)]

Don't group on the EndDate in query2:

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

 

[glen.riddell]

Don't group on the EndDate in query2:

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

--

Ken Snell
<MS ACCESS MVP>

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- Hide quoted text -

- Show quoted text -

Thanks Ken, I tried that just before your post and it has resolved the
issue.