Left join doesn't work correctly

[jpunja]

I have a database (table1) with:

ID	Name	Status
1	John	Accepted
2	John	Removed
3	John	Transferred
4	William	Transferred

I want a table where I count the number of times someone is in the database, but don't count the status status accepted. The results should be:

ID	Name	Status	# rows without status accepted
1	John	Accepted	0
2	John	Removed	2
3	John	Transferred	2
4	William	Transferred	1

I have the following query:

xxx


LEFT JOIN 
		(SELECT [table1].[id], [table1].[name], [table1].[status], COUNT(*) as [Number rejected]
			FROM [table1]
			WHERE [table1].[status] <> "Accepted"
			GROUP BY [table1].[id], [table1].[name], [table1].[status]
		) as SubQuery1
		ON ([table1].[id] = SubQuery1.[id] AND [table1].[name] = SubQuery1.[name] AND [table1].[status] = SubQuery1.[status]))

However, the problem is that with this query all rows with status "accepted" are removed. What do I wrong?

[Bulzie]

I believe I got what you need with 2 queries. Create Query1 and save, then use it in Query2 and link it on Name back to Table1.

Query1:
SELECT Table1.Name, Count(Table1.Name) AS CountOfName
FROM Table1
WHERE (((Table1.Status)<>"Accepted"))
GROUP BY Table1.Name;

Query2:
SELECT Table1.ID, Table1.Name, Table1.Status, IIf([Status]="Accepted",0,[CountOfName]) AS vCountOfName
FROM Table1 INNER JOIN Query1 ON Table1.Name = Query1.Name;

[jpunja]

Thanks Bulzie. Works perfect!

[ssanfu]

Be aware that "Name" is a reserved word in Access and shouldn't be used as an object name.
Plus it is not very descriptive: "Name" of what?? Company Name, First Name? Component Name? The dog's name??


List of reserved words in Access/Jet/ACE/SQL: http://allenbrowne.com/AppIssueBadWord.html