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 2 3846
On 10 Apr 2007 19:40:59 -0700, gl**********@gmail.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
On Apr 10, 10:40 pm, glen.ridd...@gmail.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]. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: david liu |
last post by:
access 2000 query:
here's what i want to do. from an asp page, perform a search on a
table in access. i have used sql code in the asp page itself, but i'd
rather execute a query in access. i...
|
by: Puneet Murgai |
last post by:
I am trying to run an access query which works when I write it
directly in the database. However, it fails when an SQL script
containing it is run from C++.
It doesn't recognize the Iff and Nz...
|
by: Art |
last post by:
Hi,
Can anyone point me to an example of how I would execute a query I've created in an Access DB. I've copied the SQL down to my VB.net application and that works fine, but it's ugly. I'd like...
|
by: s_wadhwa |
last post by:
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,...
|
by: ShastriX |
last post by:
Getting a weird error while trying out a query from Access 2003 on a
SQL Server 2005 table.
Want to compute the amount of leave taken by an emp during the year.
Since an emp might be off for...
|
by: zwasdl |
last post by:
Hi,
I'm using MS Access to query against Oracle DB via ODBC. Is it
possible to use HINT in Access?
Thanks,
Wei
|
by: =?Utf-8?B?bXNjZXJ0aWZpZWQ=?= |
last post by:
Has anyone successfully used an Access query from .NET? I am trying to do
this and am getting a weird error. .NET calls queries 'stored procedures'.
The error I am getting says "Schema could not be...
|
by: k-man |
last post by:
Hi:
I have an MS Access query for a table called MyTable. One of my
fields in the query is a custom field that looks like "MyField: =
MyFunction(ID)" where ID is a field in MyTable.
I have...
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one...
|
by: OzNet |
last post by:
I have a query (with calculated fields) in Access (2007) and the data changes depending on the dates and staff person selected. I need to produce a series of graphs based on the data in this query...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |