Using BETWEEN function with MAX(Completion_Dates)


G

Guest

SELECT EMPLOYEE.Emp_ID, Max(DATE_COURSES.DATE_FIN) AS LAST_COURSE_TAKEN,
DateAdd("yyyy",-2,nz(Max([DATE_COURSES.DATE_FIN]),0)) AS PAST
FROM DATE_COURSES INNER JOIN EMPLOYEE ON DATE_COURSES.Emp_ID = EMPLOYEE.Emp_ID
GROUP BY EMPLOYEE.Emp_ID
UNION SELECT EMPLOYEE.Emp_ID, NZ(Max(DATE_COURSES.DATE_FIN),0),
DateAdd("yyyy",-2,nz(Max([DATE_COURSES.DATE_FIN]),0))-DateAdd("yyyy",-2,nz(Max([DATE_COURSES.DATE_FIN]),0))
FROM DATE_COURSES RIGHT JOIN EMPLOYEE ON DATE_COURSES.Emp_ID= EMPLOYEE.Emp_ID
WHERE EMPLOYEE.Emp_ID<>ALL (SELECT DATE_COURSES.Emp_ID FROM DATE_COURSES)
GROUP BY EMPLOYEE.Emp_ID;

Hello,
The query you see above works. Essentially, it gives me the date of the
last course taken by each employee, that same date - 2 years, and their
employee id #.

However, when I query the above query (and the relating tables [see below])
to try to pull up the courses in between these two dates I get a data type
mismatch error. I believe using the MAX functionality is the root of my
problem, because it was in my last post as well.

SELECT EMPLOYEE.Emp_ID, SUM(Nz([EDUCATION.Hours],0)) AS Hours,
EDUCATION.Completion_Date, DATE_LAST.PAST
FROM ((EMPLOYEE INNER JOIN DATE_LAST ON EMPLOYEE.Emp_ID = DATE_LAST.Emp_ID)
INNER JOIN EDUCATION ON EMPLOYEE.Emp_ID = EDUCATION.Emp_ID) WHERE
(((EDUCATION.Completion_Date)>=All (SELECT (DATE_LAST.PAST ) FROM DATE_LAST)))
AND (((EDUCATION.Completion_Date)<=All (SELECT (DATE_LAST.LAST_COURSE ) FROM
DATE_LAST)))
GROUP BY etc;

Any help or explanations would be greatly appreciated.

Thanks in Advance,
David
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top