Access Query Problems - Calculate Total Qty on Site

[Icebot53]

Good day,

I have the following two tables:

tbl_transactionlist

DOC_NR | TRANS_TYPE | SITE | TRANS_DATE
000001 | DELIVERY | PRO1 | 14/02/2017
000002 | DELIVERY | PRO1 | 15/02/2017
000003 | RETURN | PRO1 | 16/02/2017

tbl_transactions

TRANS_NR | DOC_NR | ITEM_CODE | QUANTITY
PRO2017-01 | 000001 | 030801 | 2
PRO2017-02 | 000001 | 030802 | 3
PRO2017-03 | 000002 | 030801 | 2
PRO2017-04 | 000003 | 030801 | 1

Now, I have a 2 x Queries to return the sum of all the deliveries / returns per site and per item_code.

This is the result:

qry_activedeliveries

SITE | ITEM_CODE | QUANTITY
PRO1 | 030801 | 4
PRO1 | 030802 | 3

qry_activereturns

SITE | ITEM_CODE | QUANTITY
PRO1 | 030801 | 1

Now, I need a query to do:

(qry_acctivedeliveries).quantity - (qry_activereturns).quantity

The result should look as follows:

SITE | ITEM_CODE | QUANTITY
PRO1 | 030801 | 3
PRO1 | 030802 | 3

However, it is pulling through item code "030802" as QTY = 0. I think it is subtracting the return note 000001 from both item_codes.

Please assist?

I upload the database as well so you can see.View attachment Prota Services Material Capture Sheet.zip

 

[NauticalGent]

Good afternoon Icebot53,

Your issue can be solved with a couple of modifications to Query1: Establish a Left Join on Item Code, removed the Group By totals (may not have been a problem but not needed) and enclose the calculated filed with the Null (Nz) function.

I attached a screen shot of how to design the query. Give it a shot...

 

[Icebot53]

Good afternoon Icebot53,

Your issue can be solved with a couple of modifications to Query1: Establish a Left Join on Item Code, removed the Group By totals (may not have been a problem but not needed) and enclose the calculated filed with the Null (Nz) function.

I attached a screen shot of how to design the query. Give it a shot...

Hi Man,

Thanks for the help. I tried doing everything you suggested, however, when I add an additional project it freaks out a bit.

See attached screenshots:





It has something to do with the sorting. I would like it:

Project: Itemcode Qty
Itemcode Qty
Itemcode Qty

Project: Itemcode Qty
Itemcode Qty
Itemcode Qty

 

[NauticalGent]

...freaks out a bit....

Industry term?

If you are referring to the blank field in the Site column, it is because you are pulling it from the qry_Returns query: if there is no record of a return against that item, it will be blank.

Change it to the qry_Deliveries and the should stop "freaking out".

If that isn't what you were referring to, then you will have to explain just what "freaking out" is!

John

 

[Icebot53]

Industry term?

If you are referring to the blank field in the Site column, it is because you are pulling it from the qry_Returns query: if there is no record of a return against that item, it will be blank.

Change it to the qry_Deliveries and the should stop "freaking out".

If that isn't what you were referring to, then you will have to explain just what "freaking out" is!

John

Hi John,

Thanks for the feedback. Yes it is a industry thing where I'm from ...

Okay, I changed it to qry_deliveries and that fixed the blank in the column, but some of the records are stilling through as duplicates. If you run the query, you will see that the first two records are duplicated.

 

[NauticalGent]

Shooting from the hip, I would say setting the properties on the query to Select DISTINCT for DISTINCT ROW will fix the problem.

At the risk of insulting your intelligence, this can be done in query design view by setting either the Unique Values (DISTINCT) or the Unique Records (DISTINCT ROW) to "Yes". Try it both ways to see how the results play out...