Access and nested joins 1

[Guest_imported]

I'm joining data from various tables concerning Employees and the amount of time they have worked on a particular project.

The below query will return all of the pertinent information, but I want to specify that the Sum of Hours from the Main table be between specified dates and still display all the other information even if an Employee did not register any hours. I tried sticking in "where date between ......." right before the "group bys" but this restricts the query to only display employees with logged hours.

the query as it is currently is below and any help to modify it to work with selected dates would be greatly appreciated.

SELECT Employee.EmployeeID, [Employee].[FirstName]+' '+[Employee].[LastName] AS Name, Employee.DefSchedule AS Schedule, Employee.ClockedInOut, Project.Project, Sum(main.Hours) AS Hours
FROM Project RIGHT JOIN (Employee LEFT JOIN Main ON Employee.EmployeeID = Main.EmployeeID) ON Project.Chargenumber = Employee.CurrChargeNum
GROUP BY Employee.EmployeeID, Employee.DefSchedule, Employee.ClockedInOut, Project.Project, Employee.FirstName, Employee.LastName

ORDER BY Employee.EmployeeID;

Thanks,
Walter Cantrell

 

[SQLwannabe]

I should probably mention that this query is being used in a VB application interacting with Access.

Thanks,
Walter

 

[tlbroadbent]

I would create a query that summarizes hours in Main by EmployeeID with a parameterized criteria for the date range. Then use that query in the place of Main in this query. Doing this will make maintenance much easier than trying to everything in one query.

First query named qMainSummary:

Select EmployeeID, Sum(main.Hours) AS Hours
From Main
Where TheDate Between date1 and date2
Group By EmployeeID

Modified the original query:

SELECT e.EmployeeID, e.[FirstName]+' '+e.[LastName] AS Name, e.DefSchedule AS Schedule, e.ClockedInOut, p.Project,
Hours

FROM Project p RIGHT JOIN
(Employee LEFT JOIN qMainSummaryAs m
ON e.EmployeeID = m.EmployeeID)
ON p.Chargenumber = e.CurrChargeNum

ORDER BY e.EmployeeID;

Terry

http://members.home.net/tlbroadbent/index.htm

 

[SQLwannabe]

Thanks alot! It works great. If you don't mind I'd like to know how you'd pass the dates between VB and Access to give the user control over what date range they wanted to use? I'm kind of new to VB and Database programming.

Thanks,
Walter Cantrell

 

[SQLwannabe]

I'm also wondering if I did want to do this as one big SQL statement how exactly could I embed that subquery into the statement instead of using the query name. I've tried several different types of syntax and can't seem to get it to work.

Thanks,
Walter