CODEWITHSUNDEEP
sqlms-access
Alxan
Alxan

Reputation: 397

Access SQL : How to retrieve all records from 2 tables (no matter they do not have record in the other table)

I'm creating inventory report mainly to show current qty, sum of qty that was reserved from customers and Total Qty that available .

As I used LEFT JOIN and RIGHT JOIN but it couldn't work, so I'm thinking of UNION function but I could't make it work. Could you please help me. Thank you very much. 

tbl_inventory
inv_id | pd_id | inv_qty_act |  inv_date | inv_note
 1     |  001  |  120        |  20-Sep-12| 
 2     |  003  |  387        |  1-Oct-12 |

tbl_reserve
res_id | cust_id | res_date | res_duedate | pd_id | res_qty | if_sent | res_note
 3     |  10     | 01-Oct-12|   17-Oct-12 |  001  |  135    |         | 
 4     |  9      | 01-Oct-12|   24-Oct-12 |  001  |  253    |         |
 5     |  22     | 01-Oct-12|   17-Oct-12 |  001  |  132    |         | 
 6     |  2      | 01-Oct-12|   24-Oct-12 |  002  |  446    |         | 


tbl_product
pd_id  | pd_name 
  001  |  des1
  002  |  des2
  003  |  des3

tbl_pdtn_startup
pdtn_st_id | pd_id | pdtn_qty_est
2         |  002  |  200
3         |  003  |  100

Output that I want :

 pd_id| pd_name| inv_qty_act|pdtn_qty_est| Sum(res_qty)| Total[(inv_qty_est) - Sum(res_qty)]
  001 | des1   |    120     |   0        |   520       |     -400 -->(120-520)
  002 | des2   |     0      |   200      |   446       |     -446 -->(0-446)
  003 | des3   |    387     |   100      |     0       |      387

Upvotes: 0

Views: 222

Answers (1)

Alin I
Alin I

Reputation: 590

what about this?

SELECT
    tbl_product.pd_id,
    tbl_product.pd_name,
    ( SELECT Sum(inv_qty_act) FROM tbl_inventory AS t1
      WHERE t1.pd_id=tbl_product.pd_id) AS SumOfinv_qty_act,
    ( SELECT Sum(pdtn_qty_est) FROM tbl_pdtn_startup AS t2
      WHERE t2.pd_id =tbl_product.pd_id) AS SumOfpdtn_qty_est,
    ( SELECT Sum(res_qty) FROM tbl_reserve AS t3
      WHERE t3.pd_id=tbl_product.pd_id) AS SumOfres_qty,
    IIF(ISNULL([SumOfinv_qty_act]),0,[SumOfinv_qty_act])-
      IIF(ISNULL([SumOfres_qty]),0,[SumOfres_qty]) AS Total
FROM
    tbl_product;

Upvotes: 2

Related Questions