I am working on Stock database for the company I am working for. We are currently using Access from 365 package. My task is to set up the inventory result for the company’s merchandises at the end of the year 2024 soon. Everything seems fine until I have found out that there is something wrong within my data base… There are 3 tables involved.
Purchase Table. Abbreviation Tax invoice Sales Table. Full Tax invoice Sales Table.
I firstly put the “-” sign in front of each records on both Tax Invoice tables to designate the “stock out” action before aggregate quantity (Q) for each product id…

The problem here is that when I would start to UNION those three tables before going to aggregate their quantity… Access starts to omit duplicates some of my records per say…

Purchase 1 has 2 similar products we bought within the same purchase ID example
purchase_id : 1
product_id : 1 with 10 units
product_id : 1 with 10 units
The fact is that within that tax invoice from my supplier, there are two times transportation for our merchandise therefore in some occasion the supplier put two lines in the same invoice for us not to avoid confusion within the data entry process corresponding to how delivery notes work between us.
However! Access thought it is duplicates and omit one of the record for us!…. which is not good because when I aggregated the result yielded me -10 unit for product_id 1!

How can I fix this? Thank you so much for your readings and kindness. I’d really don’t know how to do it.