Displaying a Null value

[JustAnElf]

Greetings one and all,

I realize this should be an easy query, but for some reason I'm having trouble. If possible I would like to use the query expression builder to make the query.

My goal is to display the total of all of the fruit in the second table even if it's value is null. For example

If Tables 1 and 2 look like this....

Table1


ID Fruit
1 Apple2 Orange
3 Pear
4 Lime
5 Pineapple

Table 2
ID Fruit List
1 Apple
2 Apple
3 Apple
4 Orange
5 Orange
6 Lime

.... I want the query output to look like this

Apple 3
Orange 2
Pear 0
Lime 1
Pineapple 0

I can easily get make the query display the correct values for the Apple, Orange, and Lime. However, it's not working for the Pear, or Pineapple.

Any suggestions?

[June7]

Build a query that joins table 1 to the aggregate query, join type will be "Include all records from table1 and only those from table2 that match". Records for Pear and Pineapple should display with empty fields. Handle the null with calculation using Nz().

Nz([SumOfFruit],0)

[JustAnElf]

Hi June7,

Thank you for the advice. I knew it was something simple. Sadly, I had forgotten the part about the join. Your comment about "Include all records from table1 and only those from table2 that match" put me on the right path.

Thank you!