First create a new query and use "Design View".

Add the two tables "DBA_prdtl1" and "DBA_orderd" and then close the window. Your names for the tables may be different if you renamed the tables when you added them to the access database. We will need the ending balance from the first table and the order quantities from the later.

Add "prdperno" (the catalog code) and "act_end" (the ending balance) from the "DBA_prdtl1" table. From the "DBA_orderd" table add "curqty".

Click the "Totals" button on the toolbar to enable grouping since we will likely have the same products on different orders.

Change the "Group By" for the "curqty" column to "Sum". This will make it so that we are summing up all the quantities in each order for each individual product.

First of all save your query so that when we build an expression we will have easy access to the previous columns data. Right click on the top cell in the fourth colum and select "Build".

In this screen we can build our expression that will subtract the sum of all the orders for each product from it's ending balance. So double click on the "act_end", press the minus sign button, and then double click on "SumOfcurqty". Press "Ok" to exit the window.

Since we now have an expression in the fourth column we need to change the "Group By" into "Expression" to reflect the change. So right click on "Group By" and select "Expression".

I wanted to rename the column title so I replaced "Expr1" with "Amount Left". This is optional but will make things easier to remember later.

It would also be good to sort the results by the product catalog code so select ascending for sorting in the first column.

Finally, it would be a good idea to change the join so that we show all the products instead of only the ones that are being shipped. To do so right click on the line linking the two tables together and select "Join Properties".

On the "Join Properties" screen just select option 2 and press "Ok" to exit.

And here's the final result. Unfortunately without using some complex SQL we can't eliminate the blank values from the table. And of course we would likely place this into a report so that we can add more to it and change some column titles.

Back to tutorials index