SQL help please

[Paul]

Hi all,

I hope somone can help me with a query. I have two queries the first
is saved as an access query called qryStdHrs and the SQL is below

SELECT qryStdHrs.Name, Sum(qryStdHrs.Hours) AS SumOfHours
FROM [SELECT tblPeople.Name AS Name, tblTimeSheet.Date,
Sum(tblTimeSheet.Hours) AS Hours FROM tblPeople INNER JOIN
tblTimeSheet ON tblPeople.ID = tblTimeSheet.FKPeopleID
GROUP BY tblPeople.Name, tblTimeSheet.Date, tblTimeSheet.FKStatusID
HAVING (((tblTimeSheet.Date) Between #06/30/2003# And #07/04/2003#))].
AS qryStdHrs
GROUP BY qryStdHrs.Name;

The second is called qryPreHrs and the SQL is below

SELECT qryPreHrs.Name, Sum(Hours) AS SumOfHours
FROM [SELECT tblPeople.Name AS Name, tblTimeSheet.Date,
Sum(tblTimeSheet.Hours) AS Hours FROM tblPeople INNER JOIN
tblTimeSheet ON tblPeople.ID = tblTimeSheet.FKPeopleID
GROUP BY tblPeople.Name, tblTimeSheet.Date
HAVING (((tblTimeSheet.Date) Between #07/07/2003# And #07/07/2003#))].
AS qryPreHrs
GROUP BY qryPreHrs.Name;

Now I can join them using another Query called qryStdPreHrs and the
SQL is below

SELECT qryStdHrs.Name, qryStdHrs.SumOfHours, qryPreHrs.SumOfHours
FROM qryStdHrs LEFT JOIN qryPreHrs ON qryStdHrs.Name = qryPreHrs.Name;

Now this works fine.

But I want to be able to write it as one SQL statement, and I just
cant work out how.

Hope somone can help.

Paul.

 

[HSalim]

Paul,

It seems that you want an output as follows:
Name, Sum(Hours) for week1, Sum(Hours) for week2

Check out CrossTab queries. Here is one that can get you started.

TRANSFORM Sum(TS.Hours) AS SumofHours
SELECT P.Name, Sum(TS.Hours) AS [TotalOfAllHours]
FROM tblPeople P INNER JOIN tblTimeSheet TS ON (ON P.ID = TS.FKPeopleID)
GROUP BY P.Name
PIVOT Format([TS.Date],"ww");

- some tweaking may be needed to correctly define the work week.
For example you could create a simple function that returns the start or end
of the WorkWeek
so that it makes more sense to you.

You can also add a where clause such as
WHERE (((P.Department)="Technology") AND ((Format([TS.Date],"w"))<6))

This will exclude saturdays and sundays and pick geeks like you and me

So there you go. Hope that helped.

Here is some free advice:
Your two queries were unnecessarily complicated. The inner query (or
subquery) is unneeded as the
outer query drops the additional grouping information. It forces Access to
do double computations.

As a test, run the following query instead of your qryStdHrs:
SELECT P.Name, Sum(TS.Hours) AS Hours
FROM tblPeople P
INNER JOIN tblTimeSheet TS
ON P.ID = TS.FKPeopleID
WHERE (((TS.Date) Between #06/30/2003# And #07/04/2003#)
GROUP BY P.Name, P.ID;

It is better to use a WHERE clause when possible. It filters the records
before summing up, whereas the HAVING clause first aggregates all records,
then filters the resultset for records that do not meet the criteria.


Regards
Habib