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

 

[Tim1]

Hi Walter,

To get it to list even employees with no hours, you just need an outer join, and an isnull command:


SELECT Employee.EmployeeID, [Employee].[FirstName]+'
'+[Employee].[LastName] AS Name, Employee.DefSchedule AS
Schedule, Employee.ClockedInOut, Project.Project, ISNULL(Sum(main.Hours),0)
AS Hours
FROM Project, Employee, Main
WHERE Employee.EmployeeID =* Main.EmployeeID
AND Project.ChargeNumber = Employee.CurrChargeNu,
GROUP BY Employee.EmployeeID, Employee.DefSchedule,
Employee.ClockedInOut, Project.Project, Employee.FirstName,
Employee.LastName

ORDER BY Employee.EmployeeID;

 

[SQLwannabe]

Thanks Tim,

I still need to know how to restrict the hours to only the specified dates that will be entered as variables so as to be able to display on the totals for the current week.

Thanks for your help and if you or anyone else can help with the remaining task that would definitely be appreciated.

Thanks,
Walter

 

[SQLwannabe]

I should have also specified that this is being used in a VB program interacting with Access. I may have posted this in the wrong forum and I posted it in the Access forum just in case.

Thanks,
Walter

 

[Tim1]

Hi Walter,

Sorry, forgot the date part, but that is very simple.

After the AND Project.ChargeNumber = Employee.CurrChargeNu statement, add
AND table.fieldname BETWEEN [Please enter start date] AND [Please enter end date]

the square brackets tell Access to prompt for a user input at runtime, and the text between the brackets is the prompt that it will use.

I am not a VB programmer, but my understanding is that you should just be able to embed the whole SQL statement into your program, and pass it whole to your SQL server, in this case MS Access.

Tim

 

[SQLwannabe]

Thanks again for the help Tim. I put in the statement for the date and it now reads ....Project.ChargeNumber = Employee.CurrChargeNum and main.tcdate between #4/2/2001# and #5/8/2001# and i get an error from Access that says "Between Operator without And in Query Expression" ?

Not sure about that one since I do have the And in there. I just plugged in some dates just to test it out. In the program these will be represented by variables.

Thanks,
Walter

 

[Tim1]

Hi Walter,

Not quite sure about the error that you are getting, I have cut and pasted a working query from access below, but cannot see anything different to what you have used:

Between #01/01/01# And #05/03/01#

The AND it is referring to in the error message is the one in needs after the between, as a between must be between two somethings, so I am confused. I would suggest that you flag this error up in Access forum, as this is the limit of my access knowledge (I am really a Sybase programmer, but tinkle with Access).

Sorry I can't get you further,

Tim