MS Access Query summarizing 2 tables doesn't produce expected output I cannot successfully create a query in MS Access 2016 that correctly summarizes data from 2 tables with similar but not completely identical fields.

I have a fruit market. I buy each type of fruit once a season. I dispose of bad fruit multiple times each each season. I separately track purchases and disposal of bad fruit. 

I wish to create a query that summarize the net fruit volume (purchased - bad) for each year and fruit.

Table of Purchased Fruit:

ID  CYear   Fruit   Purchased
3   2017    Apple   50
4   2018    Apple   30
5   2017    Pear    50
6   2018    Pear    60


Table of Bad Fruit

ID  CYear   CMonth  Fruit   Bad
2   2017    11      Apple   3
3   2017    12      Apple   20
4   2017    8       Pear    15
5   2018    2       Apple   10


This is my desired query output:

ID  CYear   Fruit   Purchased   Bad Net
1   2017    Apple   50          23  27
2   2017    Pear    50          15  35
3   2018    Apple   30          10  20
4   2018    Pear    60          0   60


The "Net" field is calculated from "Purchased - Bad". 

I cannot create a query or union that matches the desired output. Most perplexing, because there is no record of "0" bad Pears in 2018, a query doesn't even select the 2018 Pear purchase.
Create the query which calculates the amount of bad fruits per year and save it assigning the name [Bad Fruit Year]:

SELECT CYear, Fruit, SUM(Bad) AS Bad
FROM [Bad Fruit]
GROUP BY CYear, Fruit;


Then create the query obtaining the data You need:

SELECT p.ID, p.CYear, p.Fruit, p.Purchased, NZ(b.Bad,0) AS Bad, (p.Purchased-NZ(b.Bad,0)) AS Net
FROM [Purchased Fruit] As p
LEFT JOIN [Bad Fruit Year] As b ON p.CYear=b.CYear AND p.Fruit=b.Fruit;


As a result You will have 2 queries (to clear the Queries tab of DB window You can make the first query hidden). The second one gives the result You need, it automatically calls the first one (You do not need to call/open it) while opening.

If You want You may to combine these queries into one:

SELECT p.ID, p.CYear, p.Fruit, p.Purchased, NZ(b.Bad,0) AS Bad, (p.Purchased-NZ(b.Bad,0)) AS Net
FROM [Purchased Fruit] As p
LEFT JOIN ( SELECT CYear, Fruit, SUM(Bad) AS Bad
            FROM [Bad Fruit]
            GROUP BY CYear, Fruit) AS b ON p.CYear=b.CYear AND p.Fruit=b.Fruit;


But its text maybe damaged by MS Access if You open it for view/edit after create and store (the problem is old and still irreparable).

PS. I assume there is NOT a situation when some fruit is present [Bad Fruit] table for some year while absent in [Purchased Fruit] for the same year.