Tech Talk 1 by BrainStorm, Inc.
Putting Your Data to Work
OpenOffice.org Base
April 2008
Features
- 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.
Departments
- 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.
If you've been following along with this series, it's likely that you're now immersed in data. You've used OpenOffice.org Base to build tables, create relationships between those tables, and whip up a few powerful forms to ease data entry. It's been a lot of work—and it's taken a good amount of time. But the next step will prove that all your time and effort was worth it, that you've reached the point where the benefits of a great database come to fruition. This article will help you create queries that filter specific information, perform calculations and functions on data within your database, and generally use the numbers you've entered to answer questions about your data.
The Scenario
Before we get started, let's review our scenario: You're the IT director for a company with more than just a few employees, and you have a lot of hardware to monitor. You're building an inventory database to help manage and track the printers, laptops, desktops, scanners, fax machines and other important equipment floating around the office. You've created at least one great form in which an intern or secretary can enter employee names and the products each employee is using, as well as all the pertinent data about those products. You've also created a few other simple forms to enter data about vendors. Now you're ready to put your data to work. That's where queries come in.
Let Your Computer Do the Work
Queries enable you to filter, extrapolate and calculate data quickly and efficiently. For example, you can use a query to list the laptops being used by the accounting department, to calculate the dollar amount spent on those same laptops over an 18-month period, or to list the employees who have had the same laptop for more than two years and may be in need of an upgrade. Once a query is designed, the click of a button runs the query and displays the results in a table you can save for future reference.
Creating a Simple Query with the Wizard
Queries come in many different forms. A simple query might pull data from one table. A more complex query might take data from two separate tables to find a sum or other function. Let's start by running a simple query using the Wizard. The results of this query will be a list of all the laptops in your company's inventory. The inventory database is already open, so we simply need to follow these steps:
- Click the Queries icon in the left column.
- Choose Use Wizard to Create Query.
- From the Tables drop-down field, choose the table that contains the information you want. In this case, it's the Inventory Details table.
- Click the double arrows to move all of the fields in the Available fields list to the Fields in the Query list and click Next. Of course, if you don't want to use all the fields, you don't need to. In that case, simply select the fields you want and add them one at a time.
- Select your sorting order. I'd like my query to list the laptops in the order in which they were purchased, so in the first Sort by field, I've clicked the drop-down arrow and selected Date Acquired. (See Figure 1.) I'd like the dates to appear in ascending order, so I've also selected that option. If you'd like to further define how the query is sorted, use the Then by fields. Click Next when you're ready to move on.
- In this step, you'll define the search conditions, which means you'll define that you want the query to display only the laptops in your inventory. From the Fields drop-down field, select Inventory Type (or whatever field contains the information you want to isolate).
- From the Condition field, select is equal to. Other options allow you to set conditions for numerical values, such as is smaller than, is equal to or greater than, and so on.
- Enter the value in the Value field. In this case, the value is Laptop. Note that the value must be spelled exactly as it appears in the table. Entering laptop instead of Laptop would return no results.
- Set any other conditions and click Next.
- Choose whether to show a Detailed or Summary query. I want to see all records in my query, so I chose to see a Detailed query. Click Next.
- In this step, you can assign aliases to the fields if desired. This is useful if the result you want is better explained by a term other than what is used in the field. Click Next.
- An overview of your query will display. You can now name your query (or keep the computer-generated name) and choose to display or modify your query before clicking Finish. For our purposes, I named the query Laptops in Inventory and chose to display the query.
- Be sure to save the database itself after closing the query.
- 01
- 02
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.
