SQL SubQuery Syntax - MS Access

Hi,

I seem to be making a mistake in a subquery routine for MS Access. If I start with this:

SELECT t.employee, t.project, t.subproject, t.phase, t.notes, Sum(t.duration) AS hours, employee.fname, employee.lname, project.pname, subproject.subname
FROM ((timerecord AS t INNER JOIN employee ON t.employee = employee.empid) INNER JOIN project ON t.project = project.projectid) LEFT JOIN subproject ON t.subproject = subproject.subid
WHERE (((t.date)>=DLookUp("[startdate]","[Control]") And (t.date)<=DLookUp("[enddate]","[Control]")))
GROUP BY t.employee, t.project, t.subproject, t.phase, t.notes, employee.fname, employee.lname, project.pname, subproject.subname, employee.category
HAVING (t.project=83)
ORDER BY t.employee;

I get a summation of t.duration(AS hours) as expected. Then I add a subquery routine to the select clause (see below) to pick up one other piece of information. When I do that, the subquery returns the right value, but the summing values are changing in the outer query. For example: In the top query, the t.duration returns 17; in the bottom query it returns 68. 17 is the right number.

SELECT t.employee, t.project, t.subproject, t.phase, t.notes, Sum(t.duration) AS hours, employee.fname, employee.lname, project.pname, subproject.subname, (SELECT baserates.rate FROM baserates WHERE baserates.empid=t.employee AND baserates.begindate <=DLookUp("[startdate]","[Control]") And baserates.enddate >=DLookUp("[startdate]","[Control]")) AS rate
FROM (((timerecord AS t INNER JOIN employee ON t.employee=employee.empid) INNER JOIN project ON t.project=project.projectid) LEFT JOIN subproject ON t.subproject=subproject.subid) LEFT JOIN baserates ON employee.empid=baserates.empid
WHERE (((t.date)>=DLookUp("[startdate]","[Control]") And (t.date)<=DLookUp("[enddate]","[Control]")))
GROUP BY t.employee, t.project, t.subproject, t.phase, t.notes, employee.fname, employee.lname, project.pname, subproject.subname, employee.category
HAVING (t.project=83)
ORDER BY t.employee;

I'm not sure how much sense my description made. Let me know if you need any further information.

ThanksStart Free Trial