First create a new query with the CompuPlants Gold database (see previous tuturial). In this example we need data from two tables. From the "prdtl1" table add "prdperno" and "act_end" (the ending balance). From the "orderd" table add "curqty" (the quantity of each product in an order). Click the "Next" button and warning should show up asking you to join the two different tables. Select "Ok" to continue.

A new screen with the different tables should show up. First off we need to join the two tables together so the data matches up. So select "prdperno" in the left table and drag it to the "prdperno" on the right table.

Since we would like to have the totals of all items for each order added together we need to sum each individual "curqty" for each product. To do this select a cell under "curqty" and then press the summation button in the toolbar (one left of the AZ button).

One problem currently with this resulting table is that it doesn't include items that were not in any orders. To correct this we need to change the join properties so it includes all entries for the "prdtl1" table regardless of whether an entry in the table "orderd" exists. So either double click on the link between the two tables or go to the menu "Table" and select "Joins". Unfortunately this brings us to a problem (likely with Excel 97), while we should be able to select option 2 or 3 we cannot. If we could we would select option that contains the text "All values from 'prdtl1..."

Back to tutorials index