Query that returns the number of related records

[asanjose]

I have a Query that counts the related records in a different table.

I was able to make entries that contains 0 records show up by using Join Property 2. However, the record count that's suppose to show 0 records shows up as 1.

Any idea why?

 

[Galaxiom]

The join produces one record for the "one" side even if there is no record on the "many" side.

Add an Is Not Null condition on the joined field from the "many" side and this will remove records with no related record.

 

[asanjose]

I actually I want the all the records to show up. I just need to show them with count = 0.

I found a work around where in the control of the [count of expenses], I just put:
=IIf(IsNull([Sum Of Invoice_InvoiceAmt]),0,[Count Of Expenses])

I still wonder if there's a different way though. I was just lucky this time around that I had that Sum Of Invoice_InvoiceAmt field in there to let me know that there are no related records.