Calculating where no records exists

[Henryklassen]

In issue I'm having is I have three table, "Description", "History" "Allocation". Description has a one to many relationship to the other two table. What I'm trying to do is subtract "AllocationQty" which is located in the Allocation tbl from "Qty" located in the history tbl. My problem is that sometimes there will be records in the Allocation tbl and sometimes not. When there are records everything works great. I have tried the nz function but it doesn't work because there are no records to display is there anything that can be done to fix this?

 

[Brianwarnock]

You need to alter the join properties sp that you select all records from history and only those that match from allocation, then the nz should work.
Right click on the join line and select properties then select the correct option.

Brian

 

[Henryklassen]

Thank you for replying Brain, it worked like a charm.

 

[Brianwarnock]

Always happy to help.

Brian