To make things simpler this time we're just going to create a query and not a report to retrieve the data. However, once you have created a query it is very easy to use that query later in a report.

To start things off first create a new query by pressing the "New" button on the query screen

Select "Design View" and continue.

Add the two tables "DBA_orderd" and "DBA_plants" 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 use the plant names from the first table and the order quantities from the later.

Add "pltnme" (the ending balance) from the "DBA_plants" table. From the "DBA_orderd" table add "prdperno" (the catalog code) and "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.

Finally, to see the finished result go to the "View" menu and select "Datasheet View".

Back to tutorials index