Product Database - Microsoft Community

hi

i have access database 3 tables, first 1 called producttb , has fields productid autonumber , productname short text shown figure below

the second table called purchasetb , has following fields: purchaseid autonumber, productid number, purchaseqty number, purchaseprice number, p_payment  as number, purchasedate date , p_invoiceno number shown figure below

the third table called saletb , has following fields: saleid autonumber, productid number, saleqty number, saleprice number, s_payment  as number, saledate date , s_invoiceno number shown figure below

i made relationships between tables shown figure below

i create continuous form 3 fields, first field show productnames (productname), second field show product numbers (productid), third field show difference between sum of purchaseqty specific product , sum of saleqty of same product. example below shows form

product name                              product number                     difference

custard                                                       1                                           2

dalya                                                           2                                           0

fish                                                              3                                           3

biscuit                                                         4                                          11

 

thanks in advance.

best regards

need use outer join. may reason sepoarate tables need use outer join in third table.

Office / Access / Windows 10 / Office 2016