MS Access and Where clauses in groups with left joins

[mdProgrammer]

I have a query that uses a left join to gather all possibles values, and joins another table that counts the number of values for a particular group. (I know, that was a mouthful). When I use a where clause to filter down a value in the table on the right, it doesn't show all of the fields (it does in SQL Server)

What I'm trying to say is this...

Let's say I have two tables. Table A and Table B. (this is a table that takes a survey of sorts)

Table A has an ID of 1-9, with a description of those values (be it error codes, performance scale or whatnot).

i.e.

1 - Poor
2 - Fair
3 - Good
4 - Great
5 - Wonderful

Table B has rows containings values between 1 and 5. So, there might be 20 rows with "4" and 10 rows with "3".

The SQL I have is something like this:

SELECT A.Code_ID, Count(B.Answers)
FROM TABLE A LEFT JOIN TABLE B ON A.Code_ID = B.Answer
GROUP BY A.Code_ID


This works perfectly, even if Table B has no corrisponding value (it'll just show 0, which is what I want it to do).

it might look something like this:

1 - 0
2 - 0
3 - 10
4 - 20
5 - 15

Now, the problem is when I'm filtering the data, say, for all answers shown in 2006. It'll show something like this:

3 - 10
4 - 20
5 - 15

(without 1 and 2). Now, this works in SQL Server the way it should (including 1 and 2).

Is there a way to make this work in MS Access? Or, should I just leave out the where clause and include it in another query that selects from the main query?

(I know I probably answered my own question)

 

[PHV]

You may try something like this:
SELECT A.Code_ID, Count(B.Answers)
FROM TABLE_A AS A LEFT JOIN (
SELECT Answers FROM TABLE_B WHERE Year([date field])=2006
) AS B ON A.Code_ID = B.Answer
GROUP BY A.Code_ID

Another way:
SELECT A.Code_ID, (SELECT Count(*) FROM Table_B WHERE Answers=A.Code_ID AND Year([date field])=2006)
FROM TABLE_A AS A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886