Tech Talk 1 by BrainStorm, Inc.
Putting Your Data to Work
- Tech Talk 01 With all your data in your OpenOffice.org Base database, how do you access it to help make decisions? Query your database to help you analyze your data
- Tech Talk 02 If you didn't make it to BrainShare or just want a recap of the keynote messages this article is for you. The Novell executives walk you through the latest and greatest in Novell strategy and technology.
- Tech Talk 03 Attention Web enterprise developers: Mono is ready to take on the Microsoft-dominated ASP.NET stack. You can now deploy your software on Linux-based servers without the powers that be feeling a thing.
- Tech Talk 04 Lengthy release cycles are a thing of the past. We want to know what you want, and we want to deliver it to you now.
- Proof Point As a non-profit organization, Sesame Workshop has a small IT staff with a big job. They've found the extra help they need with Novell ZENworks Asset Management and SUSE Linux Enterprise Server.
- Trend Talk Are tightening budgets forcing you to consider other options? Start considering other office productivity suites (and some are online offerings) to release the strangle hold proprietary vendors might have on you-and your budget.
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:
- Open the database.
- Click the Queries icon in the left pane.
- Choose Create Query in Design View. The Design View will open, with the Add Table or Query window displaying.
- 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.
- 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.
- 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.
- 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.
- Click in the Sort field of the sixth column—the Date Acquired column—and select Ascending.
- 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.
- 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.
- 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.
- Make further edits as desired, clicking the Run Query button when finished to display the latest results.
- When finalized, click the Save button, name the query, and click OK.
- 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:
- 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.
- 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.
- 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.
- The Purchase Price value is our “many.” In that column, click inside the Function field and select Sum.
- The Department is our “one.” In that column, click the Function field and select Group.
- 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.
- In the Alias field of the first column, enter Dollars Spent.
- Click Run Query to see that the field heading in the query is renamed.
- Right-click the new Dollars Spent heading as displayed in the results area and select Column Format.
- Change the format to Currency and click OK.
- 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.
- Click Save.
- Name the Query and click OK. The query should look like (See Figure 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.