Your problem is that if you use an inner join to connect the tables, you will
only get those departments that have incomplete workorders. To get all of
the departments (even those with zero incomplete workorders) you are going to
have to use an outer join. The other problem is that as soon as you apply a
criteria ([Date_Complete] IS NOT NULL) the Outer Join gets preempted, giving
you only those departments where there are incomplete workorders.
You can solve this problem two ways. The first way is to create the outer
join, then only count those records where Date_Complete is not null. The SQL
for this looks something like:
SELECT tblDept.Dept, sum(IIF(ISNULL([tblNewMWO].[Date_Complete]), 1, 0) as
Incomplete
FROM tblDept LEFT JOIN tblNewMwo
ON tblDept.Dept = tblNewMwo.Dept
GROUP BY tblDept.Dept
The second method is a little more complicated, but if you remember this
technique it will save you a lot of headaches. This technique uses a nested
sub-query, to first limit the records from tblNewMwo to only those where
Date_Complete is Null. It then wraps this query in a Select statement which
can then be joined to tblDept.
The SQL for this would look something like, assumes you have a field
similiar to WO_ID as the PK in tblNewMwo:
SELECT tblDept.Dept, COUNT(qryIncomplete.WO_ID) as Incomplete
FROM tblDept
LEFT JOIN (SELECT tblNewMwo.Dept, tblNewMwo.WO_ID
FROM tblNewMwo
WHERE tblNewMwo.Date_Complete IS NULL) as qryIncomplete
ON tblDept.Dept = qryIncomplete.Dept
GROUP BY tblDept.Dept
If you are not comfortable writing this SQL, you can use the query grid.
1. Start a new query and add tblNewMwo to it
2. Add the Dept, Date_Complete, and WO_ID (the PK for the table) to the
query grid
3. Add the criteria "IS NULL" in the criteria line of the [Date_Complete]
field
4. Change the view to the SQL view. You should see SQL similiar to the SQL
above that is wrapped in ().
5. This is the tricky part. At the beginning of the existing SQL, type in:
SELECT * FROM (
6. At the end of the existing SQL, type:
) as qryIncomplete
7. Now change back to the query grid view. You should see what looks like
a table called "qryIncomplete"
8. Add tblDept to the query grid join from tblDept.Dept to
qryIncomplete.Dept.
9. Right click on the join line and select "Join Properties", then click on
the radio button that says "include all records from tblDept and only those
records from qryIncomplete where the joined fields are equal".
10. Drag the tblDept.Dept and qryIncomplete.WO_ID fields to the query grid.
11. Change it to a Aggregation query by clicking the Sigma character and
then change the Totals row in the grid for the WO_ID field to Count.
HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.
Dsperry101 via AccessMonster.com said:
Hi
I have a table that has a list of departments (tblDept)(15 entries). I
want to count the number of incomplete work orders in the 15 depatrtments in
tblDept from another table tblNewMwo (2500 entries). The tblNewMwo has a
column called Dept and it has entries that are the same as the table [tblDept.
Dept] , but some of the entries do not match the entries in the table
[tblDept.Dept] . The criteria for incomplete is an empty entry in [tblNewMwo.
Date_Complete]. I would like to end up with the 15 departments and the number
of incomplete work orders for each department in the list.
