Novell Home

Novell Connection Magazine Home

Creating a Query Based on Two Tables with Related Data

Our simple query, shown in Figure 2, provides us with a helpful list of specific inventory, as well as crucial information about that inventory. From this query, I can see that one laptop in my inventory was purchased nearly three years ago and another was purchased two years ago. I can then make a note to check with the employees using these machines to see if any issues have arisen with this older equipment.

However, because the query was based on one table, it doesn't tell me the names of the employees who have these two machines. It lists only the employee ID numbers. I could open the Basic Employee Details table and search for the corresponding ID numbers. Or, I could create a new query that uses data from two related tables. The wizard doesn't allow me to do this, but Design View option does. Here's how:

  1. Open the database.
  2. Click the Queries icon in the left pane.
  3. Choose Create Query in Design View. The Design View will open, with the Add Table or Query window displaying.
  4. Because we want to include information from two tables in this query, we'll make two selections: select the Basic Employee Details table and click Add, then select the Inventory Details table and click Add. Close the Add Table or Query window.
  5. You'll be able to see the relationship between both tables at the top of the screen. (See Figure 3.) But if the tables have not yet been joined, you can click the common field in one table (the Employee ID field in this case) and draw a line to the common field in the other table. The two tables will then be joined.
  6. The next step is to add the fields you want the query to include. To do so, click the Field field in the first column and select the drop-down arrow. From the list that appears, choose the first field you'd like to display. Do this in each column until all desired fields have been chosen. (See Figure 4.) In our example, we used every field from the Inventory Details table except the Employee ID field. This field isn't needed because we used the fields for Last Name, Middle Initial and First Name from the Basic Employee Detail table. We also used the Department and Extension fields in the latter table.
  7. Now, go back to each column and make designation for other fields as desired. In this example, we are not using aliases, but if you did want to change the way a field name was displayed, you would use the Alias field to enter the alias.
  8. Click in the Sort field of the sixth column—the Date Acquired column—and select Ascending.
  9. Make sure the box in the Visible field is checked for each column. If, however, there is a column you don't wish to display in the query but that is needed to perform a calculation, uncheck the box.
  10. Click in the Criterion field of the second column—the Inventory Type column—and enter Laptop. Enter the term exactly as used in the original table. Press Tab to move from the field. The term will automatically be surrounded by quotes. (See Examples of Query Commands for more information on criterion possibilities.) Let's say you want to be even more specific and list only the laptops being used by the accounting department. If that's the case, enter Accounting in the Criterion field of the Department column.
  11. Click the Run Query button. Your new query will display above the Design View editing area. As you can see in (See Figure 5), it now lists the laptops in use in the accounting department, as well as the names of the employees in the department who are using each laptop.
  12. Make further edits as desired, clicking the Run Query button when finished to display the latest results.
  13. When finalized, click the Save button, name the query, and click OK.
  14. Close the query and save the database.

Design View can also be used to generate queries that require functions, such as counting or summing a detail. You might, for example, want to query the total dollar amount used to purchase inventory in each department. This could quickly tell your co-workers in accounting how much to budget per department for inventory needs in future fiscal years. To generate such a query, take these steps:

  1. Follow steps 1 through 5 above. In our example, we want to use the same tables as in the previous exercise because those tables include the information about the department in which a piece of inventory resides and the dollar amount of each piece of inventory.
  2. Click inside the Field field of the first column in the editing area and select the Purchase Price field from the Inventory Details table. Click inside the same field of the second column and select the Department field from the Basic Employee Details table.
  3. Click Run Query to see the results. Looking at (See Figure 6), notice that the resulting query is simply a list of the price paid for each piece of inventory and the department where the inventory resides. Obviously, our query needs to be refined. Each piece of inventory belongs to only one department; but each department is using multiple pieces of inventory. This is a one-to-many relationship. By grouping the “one” and requesting the sum of the “many,” we can refine our results.
  4. The Purchase Price value is our “many.” In that column, click inside the Function field and select Sum.
  5. The Department is our “one.” In that column, click the Function field and select Group.
  6. Click Run Query. The results will provide exactly what you want: the total dollar amount spent in each department. Don't close the query quite yet, though. You'll still want to tweak things a bit.

(See Figure 7) shows our query in it's current form. As you can see, the figures are not shown as currency, and we should probably use an alias for the SUM field, just so it looks nice when we present it to HR.

  1. In the Alias field of the first column, enter Dollars Spent.
  2. Click Run Query to see that the field heading in the query is renamed.
  3. Right-click the new Dollars Spent heading as displayed in the results area and select Column Format.
  4. Change the format to Currency and click OK.
  5. Place your cursor next to the right edge of the Department column heading in the results area. When the cursor changes to two arrows, click the mouse and drag the edge of the column so that the text fits in the column nicely.
  6. Click Save.
  7. Name the Query and click OK. The query should look like (See Figure 8).
  8. Close the query and save your database before continuing.

Summing Things Up

Queries are powerful tools. This article covers only a few elements of this crucial Base feature. Lists and functions are only the beginning. It would be well worth any techie's time to spend a few hours exploring the feature. Those with proficiency in SQL can create SQL-based queries. And almost anyone with a little math sense can learn how to perform calculations to extrapolate even more data from your database. The next article in this series will cover calculations and move from queries to reports. After that, you'll definitely be a Base expert.

  • Figure 1

    In the Query Wizard you can choose a sorting order for the fields in your query. This is helpful if there is a particular field in a table that pertains to your query. In this example, we've created a query that lists all the laptops in our inventory. They are sorted by the date in the Date Acquired field because one purpose of the query was to see how many laptops we have and which ones might need to be replaced soon.

  • Figure 2

    Using the Query Wizard, the simple query was generated from a larger table listing all the inventory in our fictional company. Here, only the laptops are listed, in the order in which they were purchased.

  • Figure 3

    In Design View, you can add two or more tables or previously generated queries to create a new query. The tables must be related, meaning they share a common field. If you have not already created a join between table, you can do so in Design View.

  • Figure 4

    Each column in the Design View will contain the data for a specific field in one of the two tables. Click within the Field field to select a field for each column.

  • Figure 5

    In Design View, you can generate a query that includes fields from related tables. When the Run Query button is pushed, the query displays above the editing area so you can instantly see the results before saving the query or making further changes.

  • Figure 6

    The first step in running a query that involves functions is to isolate the fields in each table that are involved in the calculation. In our example, we want to know the total dollar amount spent on inventory in each department. Our initial step lists the price of every piece of inventory and the corresponding department. Our next step will be to group the departments.

  • Figure 7

    After grouping one value in our query and summing the other, we get these results in our query. The results provide the data we want, but the query still needs some modifications.

  • Figure 8

    To complete our query, we gave the first column an alias that more accurately represents the data contained in the column. We also changed the format of the column so that the figures are displayed as currency. Finally, we modified the width of the second column so that the reader could see the full name of each department listed in the table.



© 2014 Novell