Show missing records

[Homer J]

Hi!

I'm having a problem with a query where I'm trying to show records from table (tbl Invites) and count the records from another table (Renewal MI) with a number of criteria and these are then sorted by the Inception date.

It all works fine when there is data to calculate, but....

What I want it to do is if the table (QRY Invites) has no invites for a certain date I want my query to show that and have the (Renewal MI) table count 0

ie,
Date/Invites/Renewed
18-05-2004/0/0

I've tried many different methods to get it to work but it wont do it!!!

Here's the SQL I've got so far

SELECT [Renewal MI].inception AS [Inception Date], [Tbl Invites].[No Invited], IIf(Count([Renewal MI]![polno]) Is Null,0,Count([Renewal MI]![polno])) AS [No Renewed]
FROM [Renewal MI] INNER JOIN [Tbl Invites] ON [Renewal MI].inception = [Tbl Invites].[Rnl Date]
WHERE ((([Renewal MI].make)="FOR") AND (([Renewal MI].policy_class)="CAR") AND (([Renewal MI].campaign_class)="Free") AND (([Renewal MI].polstatus)="LIV") AND (([Renewal MI].campaign)="FRE1" Or ([Renewal MI].campaign)="FRE2"))
GROUP BY [Renewal MI].inception, [Tbl Invites].[No Invited];

Please help and stop me from going mad!

 

[FoFa]

Because you have an INNER JOIN, it won't do it, you need an outer (LEFT) join.