Query does not select all information needed

[Wayne Jordan]

I have had similar problems like this in the past but the old solutions don't seem to work here.
This is an inventory database.
I have two tables in this question, tlbIems and tblTransactions.
tblItems lists all items in my database, i.e. boxes, bubblewrap etc.
tblTransactions records when I receive or pick an item from stock.
I have to report submit a weekly report itemizing what we have used.
To make it simple, let's assume I have two items, A and B.
This week I received and picked some of item A but there are no transactions for item B. So when I ask for a report to show a sum what happened between 1/1/16 and 1/7/16 I want to see something like:
ItemID QtyRcvd QtyPicked
A 10 5
B 0 0

But all I get is
ItemID QtyRcvd QtyPicked
A 10 5

and item B is left off the report.

I select ItemId from tblItems because they are all listed there and I want all items to show up on the report. I use a LEFT JOIN. From past experiences (if I remember correctly which probably do not) I at least got a NULL response for item B and I was able to use NZ to make it a 0. But now I do not even get a null response, i just get nothing if there are no transactions for that item.

Here is the actual query

SELECT Items.ItemID, Sum(Transactions.Consumed) AS SumOfConsumed
FROM Items LEFT JOIN Transactions ON Items.ItemID = Transactions.ItemID
WHERE (((Transactions.TransDate) Between #1/1/2016# And #1/7/2016#))
GROUP BY Items.ItemID;

I am going crazy because I have had similar problems before and came to forum and found solutions. Even after reviewing those posts I cannot make this work.
Can someone please help before I shoot myself or this computer.

 

[bob fitz]

Try changing the relationship between the tables in query design view.

 

[sneuberg]

Somehow the criteria is messing this up, but I don't think it should be. I'm looking into it.

 

[sneuberg]

I'm not sure why this works this way, but what you can do is make a query with the Transactions table that has the WHERE clause in it some like:

SELECT Transactions.ID, Transactions.ItemID, Transactions.Consumed, Transactions.TransDate
FROM Transactions
WHERE (((Transactions.TransDate) Between #1/1/2016# And #1/7/2016#));

Let's call that Query1. Then use Query1 with the Items Table to do the left join and grouping and summing something like:

SELECT Items.ItemID, Sum(Query1.Consumed) AS SumOfConsumed
FROM Items LEFT JOIN Query1 ON Items.ItemID = Query1.ItemID
GROUP BY Items.ItemID;

This should get you what you want. Hopefully someone else can explain this.

 

[CJ_London]

Sneuberg is right - where there are null values, the criteria is not selecting them.

You can modify the criteria to

WHERE (((Transactions.TransDate) Between #1/1/2016# And #1/7/2016#)) OR Transactions.TransDate is null


Alternatively I would write the query as

 SELECT Items.*, ttlConsumed
 FROM Items LEFT JOIN (SELECT ItemID, sum(Consumed) as ttlConsumed FROM Transactions
WHERE TransDate Between #1/1/2016# And #1/7/2016#
 GROUP BY ItemID) AS ttls ON Items.ItemID=ttls.ItemID

 

[Wayne Jordan]

Thanks sneuberg. As part of testing I tried query 1 alone without the Transactions.ID and it didn't work right but when I added it did, so obviously that is a key piece of evidence. I am now getting the results I want. Thank you very much. Thank you for your post CJ London.

 

[plog]

Here's why:

FROM Items LEFT JOIN Transactions ON Items.ItemID = Transactions.ItemID
WHERE (((Transactions.TransDate) Between #1/1/2016# And #1/7/2016#))

Say LEFT JOIN all you want, but when you apply criteria to a datasource that is in the LEFT JOIN you have made it an INNER JOIN. Only matches between the two tables will be shown--because you've told it that the LEFT JOIN table needs to have certain values. The only way to have those certain values is for records to be found, which nullifies the LEFT JOIN...

Unless you specifically say that Nulls are ok. Because a LEFT JOIN will return null values when records are not found.

 

[Wayne Jordan]

Thanks plog. So how do I do that? How do I tell it nulls are OK? Does this mean I can do it with just one query? I do appreciate your post because I want to learn as much as possible. What you said makes sense. Basically I was saying I wanted everything from both tables that fits the criteria I was placing on the right table. By placing criteria on the right table I limited what I got from both.

 

[plog]

Looks like sneuberg and CJ gave you the how. I was providing the why.