View source for EN/documentation/ooo3 user guides/getting started/creating queries and reports

<section begin=title /><noinclude>{{DISPLAYTITLE:</noinclude>Creating queries<noinclude>}}</noinclude><section end=title />

{{Documentation/GS3BaseTOC
|ShowPrevNext=block
|PrevPage=Documentation/OOo3_User_Guides/Getting Started/Entering data in a form
|NextPage=Documentation/OOo3_User_Guides/Getting Started/Creating reports
}}__NOTOC__
Queries are used to get specific information from a database. Using our CD-Collection table, we will create a list of albums by a particular artist. We will do this using the Wizard. The information we might want from the Fuel table includes what our fuel economy is. We will do this using the Design View.

{{Documentation/Note|Queries blur the differences between a database and a data source. A database is only one type of data source. However, searching for usable information from a data source requires a query. Since the query (one part of a database) does this, the data source appears to become one part of that database: its table or tables. Query results, themselves, are special tables within the database.}}

[[#top|Top of page]]

==Using the Wizard to create a query==

Queries created by the wizard provide a list or lists of information based upon what one wants to know. It is possible to obtain a single answer or multiple answers, depending upon the circumstances. Queries which require calculations are best created with the Design view.

In the main database window, click the Queries icon in the Databases section, then in the Tasks section, click ''Use Wizard to Create Query''. The Query Wizard window opens. The information we want is what albums are by a certain musical group or individual (the album's author). We can include when each album was bought.

{{Documentation/Note|When working with a query, more than one table can be used. Since different tables may contain the same field names, the format for naming fields in a query is Table name and field name. A period (.) is placed between the table name and the field name.}}

[[Image:QueryWizard.png|thumb|none|500px|''First page of the Query Wizard.'']]
[[#top|Top of page]]

===Step 1: Select the fields.===

# Select the CD-Collection table from the dropdown list of tables.
#* If the Tables selection is not ''Table: CD-Collection'', click the arrow (circled in red in figure above).
#* Click ''Table: CD-Collection'' in the list to select it.
# Select fields from the CD-Collection table in the ''Available fields'' list.
#* Click ''AlbumTitle'', and use the '''>''' button to move it to the ''Fields in Query'' list.
#* Move the ''Artist'' and ''DatePurchased'' fields in the same manner.
# Use the up arrow to change the order of the fields: artist, album, and date purchased.
#* Click the ''CD-Collection.Artist'' field.
#* Click the up arrow to move it above ''CD-Collection.AlbumTitle.''
# Click '''Next'''.
:: [[Image:QueryFields.png|thumb|none|500px|''List of fields added to the query.'']]

{{Documentation/Tip|To change the order of the fields, select the field you want to move and click the up or down arrow to move it up or down.}}

[[#top|Top of page]]

===Step 2: Select the sorting order.===
Up to four fields can be used to sort the information of our query. A little simple logic helps at this point. Which field is most important?

In our query, the artist is most important. The album title is less important, and the date purchased is of least importance. Of course, if we were interested in what music we bought on a given day, the date purchased would be the most important.

: [[Image:BaseSortBy.png|thumb|none|500px|''Sorting order page.'']]

# Click the first ''Sort by'' dropdown list.
#* Click ''CD-Collection.Artist'' to select it.
#* If you want the artists to be listed in alphabetical order (a-z), select ''Ascending'' on the right. If you want the artist listed in reverse order (z-a), select ''Descending'' on the right.
# Click the second ''Sort by'' dropdown list.
#* Click ''CD-Collection.ArtistTitle''.
#* Select ''Ascending'' or ''Descending'' according to the order you want.
# Repeat this process for ''CD-Collection.DatePurchased''.
# Click '''Next'''.

[[#top|Top of page]]

===Step 3: Select the search conditions.===

The search conditions available are listed below. They allow us to compare the name we entered with the names of the artist in our database and decide whether to include a particular artist in our query or not.

* ''is equal to'': the same as
* ''is not equal to'': not the same as
* ''is smaller than'': comes before
* ''is greater than'': comes after 
* ''is equal or less than'': the same as or comes before
* ''is equal or greater than'': the same as or comes after
* ''like'': similar to in some way

{{Documentation/Note|These conditions apply to numbers, letters (using the alphabetical order), and dates.}}

# Since we are only searching for one thing, we will use the default setting of ''Match all of the following.''
# We are looking for a particular artist, so select ''is equal to''.
# Enter the name of the artist in the ''Value'' box. Click '''Next'''.

[[#top|Top of page]]

===Step 4: Select type of query.===

We want simple information, so the default setting: ''Detailed query'' is what we want. Click '''Next''' at the bottom of the window.

{{Documentation/Note|Since we have a simple query, the ''Grouping'' and ''Grouping conditions'' are not needed. Those two steps are skipped in our query.}}

===Step 5: Assign aliases if desired.===

We want the default settings. Click '''Next'''.

===Step 6: Overview.===

Name the query (suggestion: ''Query_Artists''). To the right of this are two choices. Select ''Display Query''. Click '''Finish'''.

===Step 7: Modify the query.===

We are skipping this step since we have nothing to modify. If you select the Modify Query choice, the query would open in Design view. To make modifications, follow the instructions in the next section, “Using the Design View to create a query".

[[#top|Top of page]]

==Using the Design View to create a query==

Creating a query using Design View is not as hard as it may first seem. It may take multiple steps, but each step is fairly simple.

What fuel economy is our vehicle getting (miles per gallon in the USA)? This question requires creating two queries, with the first query being used as part of the second query.

{{Documentation/Caution|The procedures we will be using only work with relational databases. This is because of how relational databases are constructed. The elements of a relational database are unique. (The primary key insures this uniqueness.) That is, there are no two elements which are exactly alike. This allows us to select specific elements to place into our queries. Without the elements of the relational database being unique from all other elements, we could not perform these procedures.}}

[[#top|Top of page]]

===Step 1: Open the first query in Design View.===

Click '''Create Query in Design View'''.

===Step 2: Add tables.===

[[Image:BaseAddQT.png|thumb|none|500px|''Add Tables or Query window.'']]

# Click ''Fuel'' to highlight it.
# Click '''Add'''.
# Click '''Close'''.

{{Documentation/Tip|Move the cursor over the bottom edge of the fuel table. The cursor become a single arrow with two heads. Drag the bottom of the table to make it longer and easier to see all of the fields in the table.}}

[[Image:FuelTable.png|thumb|none|500px|''Fuel table in query.'']]

[[#top|Top of page]]

===Step 3: Add fields to the table at the bottom.===

# Double-click the ''FuelID'' field in the Fuel table.
# Double-click the Odometer field.
# Double-click the FuelQuantity field.

The table at the bottom of the query window should now have three columns.

[[Image:QueryTable3.png|thumb|none|500px|''Query table.'']]

[[#top|Top of page]]

===Step 4: Set the criterion for the query.===

We want to the query's FuelID to begin with the numeral 1.

# Type ''>0'' in the Criterion cell under FuelID in the query table
# Click the ''Run Query'' icon in the Query Design toolbar. This icon is circled in red below.
:: [[Image:QueryDesignToolbar.png|thumb|none|500px|''Query Design toolbar.'']] 

The figures below contain the Fuel table with my entries and the query results based upon the Fuel table.

[[Image:QueryTable2.png|thumb|none|500px|''Fuel table.'']][[Image:QueryTable.png|thumb|none|500px|''Query of the fuel table.'']]

===Step 5: Save and close the query.===

Since this query contains the ending odometer reading for our calculations, name it End-Reading when saving it. Then close the query.

[[#top|Top of page]]

===Step 6: Create the query to calculate the fuel economy.===

<ol>
<li>Click '''Create Query in Design View''' to open a new query.</li>
<li>Add the Fuel table to the query just as you did in step 2: Add tables. But, '''do not''' close the Add Tables window.</li>
<li>Add the End-Reading query to this query.</li>
<ol style="list-style-type:lower-alpha">
<li>Click the ''Query'' radio button to get the list of queries in the database.</li>

[[Image:SelectQuery.png|thumb|none|500px|''Selecting queries to add to another query.'']]

<li>Click End-Reading.</li>
<li>Click '''Add''', and then click '''Close'''.</li>
</ol>
</ol>

[[#top|Top of page]]

===Step 7: Add fields to the table at the bottom of the query.===

[[Image:TablesInQuery.png|thumb|none|500px|''Tables in this query.'']]

We are going to calculate the fuel economy. To do this we need the FuelQuantity and distance traveled. Since the FuelQuantity we want to use is at the ending odometer reading, we will use the End-Reading query to get it. We will also use the Odometer field from the Fuel table and End-Reading query.

# Double-click ''FuelQuantity'' in the End-Reading query.
# Double-click ''Odometer'' in the End-Reading query.
# Double-click ''Odometer'' in the Fuel table.
: [[Image:AddFieldsToQ.png|thumb|none|500px|''Added fields to the query.'']]

[[#top|Top of page]]

===Step 8: Enter the FuelID difference field.===

We want the difference between the FuelID value of the Fuel table and FuelID value of the End-Reading query to equal one (1).

<ol>
<li>Type ''"End-Reading".FuelID - Fuel.FuelID'' in the field to the right of the Odometer field of the Fuel Table''.<br />
Type the numeral 1 (one) in the Criterion cell of this column.</li>

: [[Image:BaseCalcFields.png|thumb|none|500px|''Typing in calculation of fields.'']]

{{Documentation/Caution|When entering fields for these calculations, you must follow this format: table or query name followed by a period follow by the field name. For hyphenated or multiple-word names (table or query), use double quotes around the table or query name. The query will then add the rest of the double quotes as in the figure above.

Use the arithmetical symbol between the two. More than one calculation can be done by using parentheses to group the arithmetical operations.}}

<li>Calculate the distance traveled:</li>
<ul>
<li>Type ''"End-Reading".Odometer - Fuel.Odometer'' in the Field cell.</li>
<li>Type ''>0'' in the Criterion cell.</li>
[[Image:DistTravel.png|thumb|none|500px|''Field for distance traveled calculations.'']]
</ul>
<li>Calculate fuel economy:<br>
Type ''("End-Reading".Odometer - Fuel.Odometer)/"End-Reading".FuelQuantity'' in the Field in the next column to the right.</li>
[[Image:FuelEcon.png|thumb|none|500px|''Fuel economy calculation field.'']]
</ol>

[[#top|Top of page]]

===Step 9: Run the query and make some modification.===

After we run the query to make sure it works correctly, we will hide all of the fields that we do not need.

<ol>
<li>Click the Run Query icon in the Design Query toolbar. The results are below.<br>
Notice that not all of the last column label is visible because some of the labels are long. We can fix this problem by using an alias for many of the fields. The labels are replaced by their aliases.</li>
[[Image:Query1.png|thumb|none|600px|''Result of running the fuel economy query.'']]

<li>Add Aliases: Type in aliases as they are listed below.</li>
[[Image:QTAlias.png|thumb|none|500px|''Query table with aliases added.'']]

<li>Run the query again. The results are below.</li>
[[Image:Qrunwithalias.png|thumb|none|500px|''Query run with aliases.'']]

We really do not need the column showing the difference between the FuelID fields from the table and query. So, we will hide it. While it will not be visible, it will still be used in the calculations.

<li>Hide a field that does not need to be seen.<br>
Remove the check in the box of the Visible cell.</li>

[[Image:MakeFieldInvis.png|thumb|none|500px|''Making a field invisible in a query run.'']]

<li>Rerun the query.</li>
[[Image:QwithAliases.png|thumb|none|500px|''Query run with aliases.'']]
</ol>

[[#top|Top of page]]

===Step 10: Close, save, and name the query.===

My suggestion for a name is ''Fuel Economy''.

There are obviously other calculations that can be made in this query such as cost per distance traveled and  how much of the cost belongs to each of the payments types.

{{Documentation/Note|To fully use queries requires a knowledge of mathematics and specifically set operations (''unions, intersections, and, or, complements,'' and any combinations of these). For example, we listed all of our criteria in one row. That means that all of these criteria have to be met before a row of values will be created in the query. This is how the ''and ''operator works on sets.

It also requires having a copy of the ''Hsqldb User Guide'' available from  http://hsqldb.org/ }}

[[#top|Top of page]]


{{CCBY}}
[[Category:Getting Started (Documentation)]]