SQL: Join with multiple criteria

[Joost de Vries]

I'm trying to generate an overview for quality control coordinators using two
tables, "employees" and "inspections". To check whether monthly targets are
met, I'm trying to display the numer of inspections per employee.

My query would be somthing like: "SELECT [fields] FROM employees LEFT OUTER
JOIN inspections ON employees.id=inspections.employee AND
DATEPART("m",inspections.date)=DATEPART("m",Date());"

The problem is however that Access doesn't accept this type of Join query.
When I put the month selection criterium in the WHERE clause, not all
employees are listed (I want all of them to be listed so I can calculate the
amount of inspections that have to be done per employee).

Any suggestions how I can tackle this little problem..
I've thougt about using a Filter, but after a while there will be thousands
of inspections in the system, resulting in an extremely inefficient report..

Thanks in advance.
Kind Regards,

 

[Allen Browne]

Explicitly accept Nulls as well.

This kind of thing:
SELECT employees.*, inspections.*
FROM employees LEFT JOIN inspections
ON employees.id=inspections.employee
WHERE Month(inspections.date) = Month(Date())
OR inspections.date Is Null;

Hopefully "date" is just an example, and you don't really have a field by
that name.

 

[John Spencer]

Try using a subquery in the from Clause

SELECT [Field List]
FROM Employees as E LEFT JOIN
(SELECT Inspections.Employee, Count(Employee) as InspectionCount
FROM Inspections
WHERE DATEPART("m",inspections.date)=DATEPART("m",Date())
GROUP BY Inspections.Employee) as I
ON E.Employee = I.Employee

An alternative would be to use a coordinated subquery in the SELECT clause.
This assumes you only need the count
SELECT Employees.*,
(SELECT Count(*) FROM Inspections
WHERE Inspections.Employee = Employees.Employee
and DATEPART("m",Inspections.Date)=DATEPART("m",Date())) as ICount
FROM Employees

By the way using Date as a field name can cause problems since Access could
confuse the Date() function with the Date field.

If you have the Inspections.Date field indexed, you might consider changing
the where clause to the following. It should be faster if you have a large
number of records.

WHERE Inspections.Date >= DateSerial(Year(Date()),Month(Date()),1)
And Inspections.Date < DateSerial(Year(Date()),Month(Date())+1,1)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Joost de Vries]

Works like a charm. Thanks a buch. I was just reading your tutorial on
Subqueries to see whether I could solve the problem that way, but this is the
logical solution.

The date field of course isn't called date. It's caled "datum" (Dutch for
date..) Is that risky as well? I assume your comment was because of the fact
that "date" is a function as well?

Anyway,
Thanks again.

----------------
Joost de Vries
R&D Student

Explicitly accept Nulls as well.

This kind of thing:
SELECT employees.*, inspections.*
FROM employees LEFT JOIN inspections
ON employees.id=inspections.employee
WHERE Month(inspections.date) = Month(Date())
OR inspections.date Is Null;

Hopefully "date" is just an example, and you don't really have a field by
that name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to generate an overview for quality control coordinators using
two
tables, "employees" and "inspections". To check whether monthly targets
are
met, I'm trying to display the numer of inspections per employee.

My query would be somthing like: "SELECT [fields] FROM employees LEFT
OUTER
JOIN inspections ON employees.id=inspections.employee AND
DATEPART("m",inspections.date)=DATEPART("m",Date());"

The problem is however that Access doesn't accept this type of Join query.
When I put the month selection criterium in the WHERE clause, not all
employees are listed (I want all of them to be listed so I can calculate
the
amount of inspections that have to be done per employee).

Any suggestions how I can tackle this little problem..
I've thougt about using a Filter, but after a while there will be
thousands
of inspections in the system, resulting in an extremely inefficient
report..

Thanks in advance.
Kind Regards,

 

[Allen Browne]

Date is a reserved word. I don't know whether the Dutch word for date could
cause problems.

There's a list of the possible problem words here:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Works like a charm. Thanks a buch. I was just reading your tutorial on
Subqueries to see whether I could solve the problem that way, but this is
the
logical solution.

The date field of course isn't called date. It's caled "datum" (Dutch for
date..) Is that risky as well? I assume your comment was because of the
fact
that "date" is a function as well?

Anyway,
Thanks again.

----------------
Joost de Vries
R&D Student

Explicitly accept Nulls as well.

This kind of thing:
SELECT employees.*, inspections.*
FROM employees LEFT JOIN inspections
ON employees.id=inspections.employee
WHERE Month(inspections.date) = Month(Date())
OR inspections.date Is Null;

Hopefully "date" is just an example, and you don't really have a field by
that name.

message

I'm trying to generate an overview for quality control coordinators
using
two
tables, "employees" and "inspections". To check whether monthly targets
are
met, I'm trying to display the numer of inspections per employee.

My query would be somthing like: "SELECT [fields] FROM employees LEFT
OUTER
JOIN inspections ON employees.id=inspections.employee AND
DATEPART("m",inspections.date)=DATEPART("m",Date());"

The problem is however that Access doesn't accept this type of Join
query.
When I put the month selection criterium in the WHERE clause, not all
employees are listed (I want all of them to be listed so I can
calculate
the
amount of inspections that have to be done per employee).

Any suggestions how I can tackle this little problem..
I've thougt about using a Filter, but after a while there will be
thousands
of inspections in the system, resulting in an extremely inefficient
report..

Thanks in advance.
Kind Regards,

 

[Joost de Vries]

John,

Thanks for your reply. I'm using Allen's solution right now because it works
just fine and I really want to keep my SQL as simple as possible.
Your post does help me with understanding subqueries, so thanks again!

----------------
Joost de Vries
R&D Student

Try using a subquery in the from Clause

SELECT [Field List]
FROM Employees as E LEFT JOIN
(SELECT Inspections.Employee, Count(Employee) as InspectionCount
FROM Inspections
WHERE DATEPART("m",inspections.date)=DATEPART("m",Date())
GROUP BY Inspections.Employee) as I
ON E.Employee = I.Employee

An alternative would be to use a coordinated subquery in the SELECT clause.
This assumes you only need the count
SELECT Employees.*,
(SELECT Count(*) FROM Inspections
WHERE Inspections.Employee = Employees.Employee
and DATEPART("m",Inspections.Date)=DATEPART("m",Date())) as ICount
FROM Employees

By the way using Date as a field name can cause problems since Access could
confuse the Date() function with the Date field.

If you have the Inspections.Date field indexed, you might consider changing
the where clause to the following. It should be faster if you have a large
number of records.

WHERE Inspections.Date >= DateSerial(Year(Date()),Month(Date()),1)
And Inspections.Date < DateSerial(Year(Date()),Month(Date())+1,1)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm trying to generate an overview for quality control coordinators using
two
tables, "employees" and "inspections". To check whether monthly targets
are
met, I'm trying to display the numer of inspections per employee.

My query would be somthing like: "SELECT [fields] FROM employees LEFT
OUTER
JOIN inspections ON employees.id=inspections.employee AND
DATEPART("m",inspections.date)=DATEPART("m",Date());"

The problem is however that Access doesn't accept this type of Join query.
When I put the month selection criterium in the WHERE clause, not all
employees are listed (I want all of them to be listed so I can calculate
the
amount of inspections that have to be done per employee).

Any suggestions how I can tackle this little problem..
I've thougt about using a Filter, but after a while there will be
thousands
of inspections in the system, resulting in an extremely inefficient
report..

Thanks in advance.
Kind Regards,