Nz problem in select query (1 Viewer)

[Wayne Jordan]

I have a problem that involves null values that I do not know how to solve.
I have the following tables:


tblOrderDetailsKeyItemIdOrder QtyPO#001A204001002B204001003C304001

tblTransactionsKeyTransDateItemIDReceivedPO#0011/1/2014A1040010021/2/2014B2040010031/3/2014A54001

I also have a table that list the items and on that table the column ItemID is the key.
I want to create a query that will list all items ordered on a PO# and how many have been received so far. With that I will calculate the outstanding qty. I want to see:

qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B202004001C30030

But all I see is this:


qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B20200
It is missing item C because I have not received any yet so there are no records in the Transactions table for it to select. I don’t know how to fix it. I believe the answer has something to do with Nz but I can’t figure it out. :banghead:I don’t know VBA so I do all of my queries in Query Design. The truth is I know enough to get myself in trouble.
Thank you very much to anyone who can help.

 

[Wayne Jordan]

Those tables were done in a spreadsheet for this post and they didn't look like that in excel. Let me type them out.

tblOrderDetails
Key ItemId OrderQty PO#
001 A 20 4001
002 B 20 4001
003 C 20 4001

tblTransactions
Key TransDate ItemID Received PO#
001 10/12/2014 A 10 4001
002 10/12/2014 B 20 4001
003 10/13/2014 A 5 4001

What I want to see is this:
PO# ItemID OrderQty Received Outstanding
4001 A 20 15 5
4001 B 20 20 0
4001 C 20 0 20

What I get is this:
PO# ItemID OrderQty Received Outstanding
4001 A 20 15 5
4001 B 20 20 0

Sorry about freaky looking tables in first post.

 

[Brianwarnock]

I'm not going to be able to help very much as I no longer have Access but you need to look at joins, particularly outer joins. You want to select all records from one table and matching items from the other.

Brian

 

[Wayne Jordan]

Thank you very much. I rebuilt the query and made sure all the data came from tblOrderDetails and just asked for a sum of the qty Received from tblTransactions. Then I switched to SQL mode and changed the word INNER JOIN to LEFT JOIN and it gave me all I asked for but NULL for the Items that I had not received. I changed the field name in the query from Received to Nz(Received) and that changed the nulls to zeros.

 

[Brianwarnock]

Glad you got it sorted.
Note that you can change the Join in Design view by clicking on the join line , a menu appears offering options. Can't remember if it's left or right click as I used to do it automatically.

Brian