Want Zero Count to put "0"

[zooropa66]

Hello Everyone,
Thequery shown below is supposed to show the number of cells having a bin_value = 4 for each silicon_wafer_number. It does this except for when a silicon_wafer_number has no cells with bin_value=4 in which case it just doesn't displays the record (rather than putting a count of "0" which is what i want). I've been looking at other posts in this and other forums to do with NZ(Iff(.. and Left Joins but to no avail. Can anyone help? Thanks

SELECT tbl_silicon_lot.silicon_lot AS [Silicon Lot], tbl_silicon_wafer.silicon_wafer_number AS Wafer, Count(tbl_cells.cells_id) AS CountOfcells_id, tbl_cells.bin_value
FROM (tbl_silicon_lot INNER JOIN tbl_silicon_wafer ON tbl_silicon_lot.silicon_lot_id = tbl_silicon_wafer.silicon_lot_id) INNER JOIN tbl_cells ON tbl_silicon_wafer.silicon_wafer_id = tbl_cells.silicon_wafer_id
GROUP BY tbl_silicon_lot.silicon_lot, tbl_silicon_wafer.silicon_wafer_number, tbl_cells.bin_value
HAVING (((tbl_cells.bin_value)=4));

 

[VilaRestal]

Don;t group by that field if you're filtering for it. That's the problem, it's filtering those and returning no records:

SELECT tbl_silicon_lot.silicon_lot AS [Silicon Lot], tbl_silicon_wafer.silicon_wafer_number AS Wafer, Count(tbl_cells.cells_id) AS CountOfcells_id
FROM (tbl_silicon_lot INNER JOIN tbl_silicon_wafer ON tbl_silicon_lot.silicon_lot_id = tbl_silicon_wafer.silicon_lot_id) LEFT OUTER JOIN tbl_cells ON tbl_silicon_wafer.silicon_wafer_id = tbl_cells.silicon_wafer_id
WHERE (((tbl_cells.bin_value)=4))
GROUP BY tbl_silicon_lot.silicon_lot, tbl_silicon_wafer.silicon_wafer_number;

Edit: And I think it will need a left join to tbl_cells

 

[VilaRestal]

In fact thinking more about it:

SELECT L.silicon_lot AS [Silicon Lot], W.silicon_wafer_number AS Wafer, Count(C.cells_id) AS CountOfcells_id
FROM tbl_silicon_lot AS L
INNER JOIN tbl_silicon_wafer AS W ON L.silicon_lot_id = W.silicon_lot_id 
LEFT OUTER JOIN (SELECT * FROM tbl_cells WHERE bin_value=4) AS C ON W.silicon_wafer_id = C.silicon_wafer_id
GROUP BY L.silicon_lot, W.silicon_wafer_number;

 

[zooropa66]

Hi VilaRestal,
Thanks for your replies. Sorry to trouble you but would you mind repeating your last code block but put in the real table names instead of L, W and C. I did try to do that myself but there may be a problem with your syntax (or more likely my stupidity) - either way Access Query Builder just threw up a "Don't Like Your Syntax" message. Thanks

 

[VilaRestal]

Sorry I used T-SQL syntax for the left join. But anyway here it is without the aliases:

SELECT tbl_silicon_lot.silicon_lot AS [Silicon Lot], tbl_silicon_wafer.silicon_wafer_number AS Wafer, Count(C.cells_id) AS CountOfcells_id
FROM tbl_silicon_lot
INNER JOIN tbl_silicon_wafer ON tbl_silicon_lot.silicon_lot_id = tbl_silicon_wafer.silicon_lot_id
LEFT JOIN (SELECT * FROM tbl_cells WHERE bin_value=4) AS C ON tbl_silicon_wafer.silicon_wafer_id = C.silicon_wafer_id
GROUP BY tbl_silicon_lot.silicon_lot, tbl_silicon_wafer.silicon_wafer_number

I think that should do it.

 

[zooropa66]

I'm still getting a syntax error. You'd still left the letter C in a couple of places but i replaced it with tbl_cells. For simplicity i also stripped out the AS parts. What I'm left with is the following. Can you spot the syntax error?

SELECT tbl_silicon_lot.silicon_lot, tbl_silicon_wafer.silicon_wafer_number, Count(tbl_cells.cells_id)
FROM tbl_silicon_lot INNER JOIN tbl_silicon_wafer ON tbl_silicon_lot.silicon_lot_id = tbl_silicon_wafer.silicon_lot_id LEFT JOIN (SELECT * FROM tbl_cells WHERE bin_value=4) ON tbl_silicon_wafer.silicon_wafer_id = tbl_cells.silicon_wafer_id
GROUP BY tbl_silicon_lot.silicon_lot, tbl_silicon_wafer.silicon_wafer_number;

 

[VilaRestal]

It has to have an alias because it's a subquery and it needs a name (it isn't tbl_cells anymore, it's a filtered version of it) and Access SQL can definitely do that.

So put that back firstly, it needs it.

I notice yours doesn't have an alias field name for the count. I think Access needs that.

Apart from that I see nothing wrong with it. Are we sure all tables and field names are correct?

 

[zooropa66]

If I copy your SQL into Query Builder (and adding a semicolon at the end) then try to run the query i get

"Syntax Error (missing operator)"

SELECT tbl_silicon_lot.silicon_lot AS [Silicon Lot], tbl_silicon_wafer.silicon_wafer_number AS Wafer, Count(C.cells_id) AS CountOfcells_id
FROM tbl_silicon_lot
INNER JOIN tbl_silicon_wafer ON tbl_silicon_lot.silicon_lot_id = tbl_silicon_wafer.silicon_lot_id 
LEFT JOIN (SELECT * FROM tbl_cells WHERE bin_value=4) AS C ON tbl_silicon_wafer.silicon_wafer_id = C.silicon_wafer_id
GROUP BY tbl_silicon_lot.silicon_lot, tbl_silicon_wafer.silicon_wafer_number;

 

[VilaRestal]

I see what you mean. Access SQL is such *(&(!*"&" *(*"*(& *&"*£!!!!!!

I got this to work:

SELECT tbl_silicon_lot.silicon_lot AS [Silicon Lot], tbl_silicon_wafer.silicon_wafer_number AS Wafer, Count(C.cells_id) AS CountOfcells_id
FROM (tbl_silicon_wafer 
LEFT JOIN (SELECT * FROM tbl_cells WHERE bin_value=4)  AS C ON tbl_silicon_wafer.silicon_wafer_id = C.silicon_wafer_id) 
INNER JOIN tbl_silicon_lot ON tbl_silicon_wafer.silicon_lot_id = tbl_silicon_lot.silicon_lot_id
GROUP BY tbl_silicon_lot.silicon_lot, tbl_silicon_wafer.silicon_wafer_number;

Although without data so I'm not certain the logic works.

Access's SQL parsing routine is totally stupid. How can it regard that as correct when the previous more sensible arrangement is "missing an operator"? It never ceases to annoy me.

 

[zooropa66]

VilaRestal, You're a star! It worked. As I only understand basic queries, what you've given me is more advanced than my level of understanding. However, it's opened up a new area of study that will make me a better VBA programmer. Thank you so much for your patience.

 

[VilaRestal]

You're welcome. Glad we got there eventually