Getting sum for each product name in a layout AND getting value from last record
Hi everyone, I'm having problems on creating a report of quantity of products sold from other table.
Here is my list of tables:
Table 1 with product name and quantity sold with invoice number (the transaction table)
Table 2 with invoice number (linked to Table 1's invoice number) and delivery number and some stuff (invoice table)
Table 3 with delivery number (linked to Table 2's delivery number), cost spent in this period, and other stuff because one delivery is delivering stuff to several customer and attempting to sell to new customer. (delivery table)
Table 4 with product name, quantity product loaded to vehicle, and delivery number (linked to Table 3's delivery number)
I would like to get a report that contain products sold and delivered in one delivery number (thus from Table 1), and I would like to see it as "Product A: total sold xx item" instead of "Product A invoice 1: sold xx, Product A invoice 2: sold xx", product name and quantity loaded, quantity of each products from previous delivery, and quantity of each products after this delivery, shown like table below:
|Product||Quantity form previous delivery||Quantity loaded||Quantity sold||Quantity remaining|
Because the item remaining in the vehicle after delivery much likely not zero (items all sold out), I would like to get quantity of each products after previous delivery with the same vehicle to be automatically written for this delivery (so I don't have to rewrite them and reducing human error probability)
Is there any solution for my problem?