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.
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.
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.