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.

  • Although Akina's answer has sufficient SQL to demonstrate what you need, in the future please include the details of queries that you attempted. Not only can that give others somewhere to start, but then beside the "correct" answer, you can receive feedback so that you can better learn how to solve the "perplexing" bugs. Best of luck!
    – C Perkins
    Jun 7 '18 at 19:39
  • Thank you. I wanted a fresh unbiased solution rather than starting with something flawed. I appreciate and will follow your suggestion in the future.
    – jmcafee
    Jun 8 '18 at 3:23
  • I would have guessed that your problem was not with identifying the NZ() function, rather understanding different joins and subqueries. Soliciting a fresh (i.e. unbiased) solution is good for larger design, but these type of issues are often technical, so narrowing in on a very particular problem is usually the best approach here. The philosophy here is the get help for work you've already attempted. Failing to include your own attempted work can look to others as fishing for a full solution. You run the risk of getting voted down or the question marked as "off topic". Glad it worked for you!
    – C Perkins
    Jun 8 '18 at 5:34

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.

  • The key to the solution is the NZ function. I knew I had to find some way to return a value with a null variant, but I didn't know how/where to find it. Thank you.
    – jmcafee
    Jun 8 '18 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.