Showing Null Records in Count Query

[Hildegoat15]

I have a query that counts the number of clients, according to HOTID and by Bill Ref Code within that. My count numbers are all good, except that i want the records to show up where the count is zero. I'm thinking it has to do with my joins, but it won't let me do an outer join between Bill Ref Codes and True Complete Client List No Bill Ref - Start. I get the "there are ambiguous joins" error message. My SQL looks like this:

SELECT CLIENT.HOTID, [Bill Ref Codes].[Bill Ref Code] AS bref, Count(Nz([true complete client list no bill ref - start]![clientid],0)) AS clientid
FROM [Bill Ref Codes] INNER JOIN ([true complete client list no bill ref - start] INNER JOIN CLIENT ON [true complete client list no bill ref - start].clientid = CLIENT.CLIENTID) ON [Bill Ref Codes].[Bill Ref Code] = [true complete client list no bill ref - start].bref
GROUP BY CLIENT.HOTID, [Bill Ref Codes].[Bill Ref Code]
HAVING ((([Bill Ref Codes].[Bill Ref Code])<>&quot;S-01&quot; And ([Bill Ref Codes].[Bill Ref Code])<>&quot;X-88&quot; And ([Bill Ref Codes].[Bill Ref Code])<>&quot;X-99&quot)
ORDER BY CLIENT.HOTID, [Bill Ref Codes].[Bill Ref Code];

can anyone tell me how i can show ALL the records, even if the count is zero?

 

[Philly44]

I'm just curious why you have an ! in this syntax?
\/
Count(Nz([true complete client list no bill ref - start]![clientid],0))

 

[Databaseguy]

Save the query. Start another one and pull the orginal query into the design grid. Make your join now with whatever table or query you couldn't in the first query.