0 or rejects not displaying

[teiben]

My query tallys the no of reject by plant and displays in a report, and this works great, but now it's a new year, only 3 plants have rejects (which is really a good thing), I need a 0 to display for the other 2 plants but don't know if this can be done, any ideas?

Plant (5 possible) group by;
Expression: Counts # of Rejects
by DateReceived 1/1/08 to 12/31/08

 

[Alc]

I think you'll need two queries.

The first would get a list of all plants.

The second would left join the first query to the table containing details of errors (linked on the Plant field).

You could then ask for the number of errors to be displayed and for all Nulls to be displayed as zeroes. e.g.

Iif(IsNull([Count of Rejects]),0,[Count of Rejects])

 

[neileg]

I agree with Alc, except I would use the Nz() function instead of the Iif statement.

 

[teiben]

Nz()

How would I do that? w/2 queries? could you provide me with an example of the syntax

 

[teiben]

The 2 queries didn't work. I tried all kinds of joins and it messed up the data

 

[neileg]

Post the SQL of your queries and we'll see what we can do.

 

[teiben]

Thank you

SELECT TblNewCustomerConern.Plant, Count(TblNewCustomerConern.ConcernNo) AS [The Value]
FROM TblNewCustomerConern
WHERE (((TblNewCustomerConern.DateReceived) Between #1/1/2008# And #12/31/2008#))
GROUP BY TblNewCustomerConern.Plant, TblNewCustomerConern.Deleted
HAVING (((TblNewCustomerConern.Deleted)=No))
ORDER BY TblNewCustomerConern.Plant;

*I've tried DCount in place or count with no success

 

[neileg]

That should give you the count of ConcernNo. I hope that TblNewCustomerConern.Plant is the ID number of the plant, but I suspect it is the plant name.

You also need a query that gives you a list of all the plants. This can't come from TblNewCustomerConern so you should have another table with all the possible plants. You then create a third query that joins these 2 queries on Plant with a left join (type 2 in Access terms). Where there is no record in [The Value] you will get a null, so this is where you apply Nz() like this:
Nz([The Value])

 

[teiben]

you are correct TblNewCustomerConern.Plant is a plant name not a id. In your opinion would this work if I created a table for plant w/ID and reconnected it?
I tried the 3 query method, w/query 2 being the posted sql and query one being the plants. I used the left join with the instruction and it returned the same data as query 2. Any other idea?

 

[EMP]

Double click on the arrow and select the option to display all records from qryPlants.

To display a numeric zero in the query results, you can change your expression to:-
Expr1: Nz([The Value])+0

^

 

[neileg]

You need to include the plant names in the query. Not clear from the screenshot if this is the case.