Difference between revisions of "Documentation/OOoAuthors User Manual/Getting Started/Creating queries and reports"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Clean up TOC and category references)
 
(9 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 +
{{DISPLAYTITLE:Creating queries and reports}}
 
{{Documentation/GSBaseTOC
 
{{Documentation/GSBaseTOC
 
|ShowPrevNext=block
 
|ShowPrevNext=block
 
|PrevPage=Documentation/OOoAuthors User Manual/Getting Started/Using data sources
 
|PrevPage=Documentation/OOoAuthors User Manual/Getting Started/Using data sources
 
|NextPage=Documentation/OOoAuthors User Manual/Getting Started/Getting_Started_with_Math
 
|NextPage=Documentation/OOoAuthors User Manual/Getting Started/Getting_Started_with_Math
}}
+
}}__NOTOC__
__NOTOC__
+
 
=Creating queries=
 
=Creating queries=
  
 
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.
 
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.
  
'''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.
+
{{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]]
 
[[#top|Top of page]]
Line 19: Line 19:
 
In the main database window (Figure 2), click the Queries icon in the Databases section, then in the Tasks section, click ''Use Wizard to Create Query''. The Query Wizard window opens (Figure 66). 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.
 
In the main database window (Figure 2), click the Queries icon in the Databases section, then in the Tasks section, click ''Use Wizard to Create Query''. The Query Wizard window opens (Figure 66). 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.
  
'''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.
+
{{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|Figure 66]]<br>Figure 66: First page of the Query Wizard.
+
  
 +
[[Image:QueryWizard.png|thumb|none|500px|Figure 66: First page of the Query Wizard.]]
 
[[#top|Top of page]]
 
[[#top|Top of page]]
  
Line 37: Line 36:
 
# Click the up arrow to move it above ''CD-Collection.AlbumTitle.''
 
# Click the up arrow to move it above ''CD-Collection.AlbumTitle.''
 
# Click '''Next'''.
 
# Click '''Next'''.
:: [[Image:QueryFields.png|Figure 67]]<br>Figure 67: List of fields added to the query.
+
:: [[Image:QueryFields.png|thumb|none|500px|Figure 67: List of fields added to the query.]]
  
'''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 (circled in magenta in Figure 66).
+
{{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 (circled in magenta in Figure 66).}}
  
 
[[#top|Top of page]]
 
[[#top|Top of page]]
Line 48: Line 47:
 
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.
 
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|Figure 68]]<br>Figure 68: Sorting order page.
+
: [[Image:BaseSortBy.png|thumb|none|500px|Figure 68: Sorting order page.]]
  
 
# Click the first ''Sort by'' dropdown list.
 
# Click the first ''Sort by'' dropdown list.
Line 65: Line 64:
 
The search conditions available are listed in Figure 69. 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.
 
The search conditions available are listed in Figure 69. 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.
  
: [[Image:QuerySearch.png|Figure 69]]<br>Figure 69: Search conditions in the Query Wizard.
+
: [[Image:QuerySearch.png|thumb|none|500px|Figure 69: Search conditions in the Query Wizard.]]
  
 
* ''is equal to'': the same as
 
* ''is equal to'': the same as
Line 75: Line 74:
 
* ''like'': similar to in some way
 
* ''like'': similar to in some way
  
'''Note:'''  These conditions apply to numbers, letters (using the alphabetical order), and dates.
+
{{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.''
 
# Since we are only searching for one thing, we will use the default setting of ''Match all of the following.''
Line 87: Line 86:
 
We want simple information, so the default setting: ''Detailed query'' is what we want. Click '''Next''' at the bottom of the window.
 
We want simple information, so the default setting: ''Detailed query'' is what we want. Click '''Next''' at the bottom of the window.
  
'''Note:'''  Since we have a simple query, the ''Grouping'' and ''Grouping conditions'' are not needed. Those two steps are skipped in our query.
+
{{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.===
 
===Step 5: Assign aliases if desired.===
Line 109: Line 108:
 
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.
 
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.
  
'''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.
+
{{Warn|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]]
 
[[#top|Top of page]]
Line 119: Line 118:
 
===Step 2: Add tables.===
 
===Step 2: Add tables.===
  
[[Image:BaseAddQT.png|Figure 70]]<br>Figure 70: Add Tables or Query window.
+
[[Image:BaseAddQT.png|thumb|none|500px|Figure 70: Add Tables or Query window.]]
  
 
# Click ''Fuel'' to highlight it.
 
# Click ''Fuel'' to highlight it.
Line 125: Line 124:
 
# Click '''Close'''.
 
# Click '''Close'''.
  
'''Tip:'''  Move the cursor over the bottom edge of the fuel table (Figure 71). 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.
+
{{Tip|Move the cursor over the bottom edge of the fuel table (Figure 71). 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|Figure 71]]<br>Figure 71: Fuel table in query.
+
[[Image:FuelTable.png|thumb|none|500px|Figure 71: Fuel table in query.]]
  
 
[[#top|Top of page]]
 
[[#top|Top of page]]
Line 139: Line 138:
 
The table at the bottom of the query window should now have three columns (Figure 72).
 
The table at the bottom of the query window should now have three columns (Figure 72).
  
[[Image:QueryTable.png|Figure 72]]<br>Figure 72: Query table.
+
[[Image:QueryTable.png|thumb|none|500px|Figure 72: Query table.]]
  
 
[[#top|Top of page]]
 
[[#top|Top of page]]
Line 149: Line 148:
 
# Type ''>0'' in the Criterion cell under FuelID in the query table
 
# 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 in Figure 73.
 
# Click the ''Run Query'' icon in the Query Design toolbar. This icon is circled in red in Figure 73.
:: [[Image:QueryDesignToolbar.png|Figure 73]]<br>Figure 73: Query Design toolbar.  
+
:: [[Image:QueryDesignToolbar.png|thumb|none|500px|Figure 73: Query Design toolbar.]]
  
 
Figure 74 contains the Fuel table with my entries and the query results based upon the Fuel table.
 
Figure 74 contains the Fuel table with my entries and the query results based upon the Fuel table.
  
[[Image:QueryTable2.png]] [[Image:QueryTable.png]]<br>
+
{|
Figure 74: Fuel table and query of the fuel table.
+
|[[Image:QueryTable2.png|thumb|none|500px|Figure 74: Fuel table.]]
 +
|[[Image:QueryTable.png|thumb|none|500px|Query of the fuel table.]]
 +
|}
  
 
===Step 5: Save and close the query.===
 
===Step 5: Save and close the query.===
Line 171: Line 172:
 
<li>Click the ''Query'' radio button to get the list of queries in the database (Figure 75).</li>
 
<li>Click the ''Query'' radio button to get the list of queries in the database (Figure 75).</li>
  
[[Image:SelectQuery.png|Figure 75]]<br>Figure 75: Selecting queries to add to another query.
+
[[Image:SelectQuery.png|thumb|none|500px|Figure 75: Selecting queries to add to another query.]]
  
 
<li>Click End-Reading.</li>
 
<li>Click End-Reading.</li>
Line 182: Line 183:
 
===Step 7: Add fields to the table at the bottom of the query.===
 
===Step 7: Add fields to the table at the bottom of the query.===
  
[[Image:TablesInQuery.png|Figure 76]]<br>Figure 76: Tables in this query.
+
[[Image:TablesInQuery.png|thumb|none|500px|Figure 76: 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.
 
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.
Line 189: Line 190:
 
# Double-click ''Odometer'' in the End-Reading query.
 
# Double-click ''Odometer'' in the End-Reading query.
 
# Double-click ''Odometer'' in the Fuel table.
 
# Double-click ''Odometer'' in the Fuel table.
: [[Image:AddFieldsToQ.png|Figure 77]]<br>Figure 77: Added fields to the query.
+
: [[Image:AddFieldsToQ.png|thumb|none|500px|Figure 77: Added fields to the query.]]
  
 
[[#top|Top of page]]
 
[[#top|Top of page]]
Line 201: Line 202:
 
Type the numeral 1 (one) in the Criterion cell of this column.</li>
 
Type the numeral 1 (one) in the Criterion cell of this column.</li>
  
'''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 Figure 78.<br/>Use the arithmetical symbol between the two. More than one calculation can be done by using parentheses to group the arithmetical operations.
+
{{Warn|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 Figure 78.<br/>Use the arithmetical symbol between the two. More than one calculation can be done by using parentheses to group the arithmetical operations.}}
  
: [[Image:BaseCalcFields.png|Figure 78]]<br>Figure 78: Typing in calculation of fields.
+
: [[Image:BaseCalcFields.png|thumb|none|500px|Figure 78: Typing in calculation of fields.]]
  
 
<li>Calculate the distance traveled (Figure 79):</li>
 
<li>Calculate the distance traveled (Figure 79):</li>
Line 209: Line 210:
 
<li>Type ''"End-Reading".Odometer - Fuel.Odometer'' in the Field cell.</li>
 
<li>Type ''"End-Reading".Odometer - Fuel.Odometer'' in the Field cell.</li>
 
<li>Type ''>0'' in the Criterion cell.</li>
 
<li>Type ''>0'' in the Criterion cell.</li>
[[Image:DistTravel.png|Figure 79]]<br>Figure 79: Field for distance traveled calculations.
+
[[Image:DistTravel.png|thumb|none|500px|Figure 79: Field for distance traveled calculations.]]
 
</ul>
 
</ul>
 
<li>Calculate fuel economy (Figure 80):<br>
 
<li>Calculate fuel economy (Figure 80):<br>
 
Type ''("End-Reading".Odometer - Fuel.Odometer)/"End-Reading".FuelQuanity'' in the Field in the next column to the right.</li>
 
Type ''("End-Reading".Odometer - Fuel.Odometer)/"End-Reading".FuelQuanity'' in the Field in the next column to the right.</li>
[[Image:FuelEcon.png|Figure 80]]<br>Figure 80: Fuel economy calculation field.
+
[[Image:FuelEcon.png|thumb|none|500px|Figure 80: Fuel economy calculation field.]]
 
</ol>
 
</ol>
  
Line 225: Line 226:
 
<li>Click the Run Query icon in the Design Query toolbar. (Figure 73) The results are in Figure 81.<br>
 
<li>Click the Run Query icon in the Design Query toolbar. (Figure 73) The results are in Figure 81.<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>
 
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|Figure 81]]<br>Figure 81: Result of running the fuel economy query.
+
[[Image:Query1.png|thumb|none|500px|Figure 81: Result of running the fuel economy query.]]
  
 
<li>Add Aliases: Type in aliases as they are listed in Figure 82.</li>
 
<li>Add Aliases: Type in aliases as they are listed in Figure 82.</li>
[[Image:QTAlias.png|Figure 82]]<br>Figure 82: Query table with aliases added.
+
[[Image:QTAlias.png|thumb|none|500px|Figure 82: Query table with aliases added.]]
  
 
<li>Run the query again. The results are in Figure 83.</li>
 
<li>Run the query again. The results are in Figure 83.</li>
[[Image:Qrunwithalias.png|Figure 83]]<br>Figure 83: Query run with aliases.
+
[[Image:Qrunwithalias.png|thumb|none|500px|Figure 83: 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.
 
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.
Line 238: Line 239:
 
Remove the check in the box of the Visible cell as in Figure 84.</li>
 
Remove the check in the box of the Visible cell as in Figure 84.</li>
  
[[Image:MakeFieldInvis.png|Figure 84]]<br>Figure 84: Making a field invisible in a query run.
+
[[Image:MakeFieldInvis.png|thumb|none|500px|Figure 84: Making a field invisible in a query run.]]
  
 
<li>Rerun the query (Figure 85).</li>
 
<li>Rerun the query (Figure 85).</li>
[[Image:QwithAliases.png|Figure 85]]<br>Figure 85: Query run with aliases.
+
[[Image:QwithAliases.png|thumb|none|500px|Figure 85: Query run with aliases.]]
 
</ol>
 
</ol>
  
Line 252: Line 253:
 
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.
 
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.
  
'''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.<br/>It also requires having a copy of the ''Hsqldb User Guide'' available from  http://hsqldb.org/.
+
{{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.<br/>It also requires having a copy of the ''Hsqldb User Guide'' available from  http://hsqldb.org/ }}
  
 
[[#top|Top of page]]
 
[[#top|Top of page]]
Line 262: Line 263:
 
For example, a report on vacation expenses divided into categories should probably be a static report because it is based upon specific data that does not change. However, a report on the fuel data should probably be a dynamic report, because this report depends upon data that does change.
 
For example, a report on vacation expenses divided into categories should probably be a static report because it is based upon specific data that does not change. However, a report on the fuel data should probably be a dynamic report, because this report depends upon data that does change.
  
'''Caution:'''  All reports are based upon a single table or query. So you need first to decide what fields you want to use in the report. If you want to use fields from different tables, you must first combine these fields in a single query. Then you can create a report on this query.
+
{{Warn|All reports are based upon a single table or query. So you need first to decide what fields you want to use in the report. If you want to use fields from different tables, you must first combine these fields in a single query. Then you can create a report on this query.
  
An example of this caution is creating a report on vacation expenses. Fuel costs are one part of that report as are meal costs. These values are contained in fields of two different tables: Vacations and Fuel. So this report requires creating a query.
+
An example of this caution is creating a report on vacation expenses. Fuel costs are one part of that report as are meal costs. These values are contained in fields of two different tables: Vacations and Fuel. So this report requires creating a query.}}
  
 
[[#top|Top of page]]
 
[[#top|Top of page]]
Line 300: Line 301:
 
<li>Select Table: Vacations in the Tables or Queries dropdown list.</li>
 
<li>Select Table: Vacations in the Tables or Queries dropdown list.</li>
 
<li>Use the '''>''' to move these fields from the ''Available fields ''list to the ''Fields in report'' list: Date, Motel, Tolls, Miscellaneous, Breakfast, Lunch, Supper, and SnackCost (Figure 86). Click '''Next'''.</li>
 
<li>Use the '''>''' to move these fields from the ''Available fields ''list to the ''Fields in report'' list: Date, Motel, Tolls, Miscellaneous, Breakfast, Lunch, Supper, and SnackCost (Figure 86). Click '''Next'''.</li>
[[Image:AddFieldsToRpt.png|Figure 86]]<br>Figure 86: Adding fields to a report.
+
[[Image:AddFieldsToRpt.png|thumb|none|500px|Figure 86: Adding fields to a report.]]
 
</ol>
 
</ol>
 
<li>Label the fields.<br>
 
<li>Label the fields.<br>
Line 307: Line 308:
 
<li>Group fields.<br>
 
<li>Group fields.<br>
 
Since we are grouping by the date, use the '''>''' button to move the ''Date'' field to the Grouping list. Click '''Next'''.</li>
 
Since we are grouping by the date, use the '''>''' button to move the ''Date'' field to the Grouping list. Click '''Next'''.</li>
:[[Image:Grouping.png|Grouping list]]
+
:[[Image:Grouping.png|thumb|none|500px|Grouping list]]
  
 
<li>Sort options.<br>
 
<li>Sort options.<br>
Line 315: Line 316:
 
We will be using the default settings for the layout. Click '''Next'''.</li>
 
We will be using the default settings for the layout. Click '''Next'''.</li>
  
'''Note:'''  If you feel adventurous, try selecting some of the other layout choices. After selecting a choice, drag and drop the Report Wizard window so that you can see what you have selected. (Move the cursor over the Heading of this window, and then drag and drop.)
+
{{Note|If you feel adventurous, try selecting some of the other layout choices. After selecting a choice, drag and drop the Report Wizard window so that you can see what you have selected. (Move the cursor over the Heading of this window, and then drag and drop.)}}
  
 
<li>Create report.</li>
 
<li>Create report.</li>
Line 335: Line 336:
 
:<tt>BETWEEN #5/25/2007# AND #5/26/2007#</tt>
 
:<tt>BETWEEN #5/25/2007# AND #5/26/2007#</tt>
  
[[Image:Qcriterion.png|Figure 87]]<br>Figure 87: Setting the criterion for a query.
+
[[Image:Qcriterion.png|thumb|none|500px|Figure 87: Setting the criterion for a query.]]
  
 
<li>Save, name, and close the query. (Suggestion: ''Vacation Fuel Purchases.'')</li>
 
<li>Save, name, and close the query. (Suggestion: ''Vacation Fuel Purchases.'')</li>
 
</ol>
 
</ol>
'''Tip:'''  When using dates in a query, enter them in numerical form MM/DDYYYY or DD/MM/YYYY depending upon your language's default setting for dates (my default setting is MM/DD/YYYY).<br/>All dates must have a # before and after it. Hence, May 25, 2007 is written #05/25/2007#.
+
 
 +
{{Tip|When using dates in a query, enter them in numerical form MM/DDYYYY or DD/MM/YYYY depending upon your language's default setting for dates (my default setting is MM/DD/YYYY).<br/>All dates must have a # before and after it. Hence, May 25, 2007 is written #05/25/2007#.}}
  
 
<li>Open a new report.</li>
 
<li>Open a new report.</li>
Line 346: Line 348:
 
<li>Select ''Report Wizard'' from the context menu.</li>
 
<li>Select ''Report Wizard'' from the context menu.</li>
 
</ol>
 
</ol>
'''Note:'''  When a new report is opened in this way, the query used to open it is automatically selected in the Tables or Queries dropdown list.
+
 
 +
{{Note|When a new report is opened in this way, the query used to open it is automatically selected in the Tables or Queries dropdown list.}}
  
 
<li>Create the report.<br>
 
<li>Create the report.<br>
Line 375: Line 378:
 
We will create a report with some statistics on our fuel consumption. To do this, we have to modify two queries: End-Reading and Fuel Economy. We will be adding the FuelCost field to the End-Reading query. Then we will add the FuelCost field from the End-Reading query to the Fuel Economy query.
 
We will create a report with some statistics on our fuel consumption. To do this, we have to modify two queries: End-Reading and Fuel Economy. We will be adding the FuelCost field to the End-Reading query. Then we will add the FuelCost field from the End-Reading query to the Fuel Economy query.
  
'''Tip:'''  When opening a query to edit it, it might appear as in Figure 88.  If you move your cursor over the black line (circled in red), it becomes a double headed arrow. Drag and drop it to a lower position.
+
{{Tip|When opening a query to edit it, it might appear as in Figure 88.  If you move your cursor over the black line (circled in red), it becomes a double headed arrow. Drag and drop it to a lower position.}}
  
[[Image:QueryEditing.png|Figure 88]]<br>Figure 88: Appearance of query when opened for editing.  
+
[[Image:QueryEditing.png|thumb|none|500px|Figure 88: Appearance of query when opened for editing.]]
  
 
<ol>
 
<ol>
Line 385: Line 388:
 
<li>In the Fuel table list, double-click to add ''FuelCost'' to the bottom table (Figure 89).</li>
 
<li>In the Fuel table list, double-click to add ''FuelCost'' to the bottom table (Figure 89).</li>
 
<li> Save and close the query.</li>
 
<li> Save and close the query.</li>
[[Image:AddFieldToQ2.png|Figure 89]]<br>Figure 89: Adding an additional field to the query.
+
[[Image:AddFieldToQ2.png|thumb|none|500px|Figure 89: Adding an additional field to the query.]]
 
</ol>
 
</ol>
 
<li>Add the ''FuelCost'' field from the End-Reading query to the Fuel Economy query.</li>
 
<li>Add the ''FuelCost'' field from the End-Reading query to the Fuel Economy query.</li>
Line 399: Line 402:
 
<tt>cost per mile</tt></li>
 
<tt>cost per mile</tt></li>
 
</ol>
 
</ol>
'''Note:'''  If you use the metric system, cost per km is the appropriate alias.
+
 
 +
{{Note|If you use the metric system, cost per km is the appropriate alias.}}
  
 
<li>Save and close the query.</li>
 
<li>Save and close the query.</li>
Line 427: Line 431:
 
At the end of the last section, we left the Fuel Statistics report open in the edit mode (Figure 90). We will be working on that report. These same steps can be used with any report that you open for editing.
 
At the end of the last section, we left the Fuel Statistics report open in the edit mode (Figure 90). We will be working on that report. These same steps can be used with any report that you open for editing.
  
[[Image:BaseRptEdit.png|Figure 90]]<br>Figure 90: A report in edit mode.
+
[[Image:BaseRptEdit.png|thumb|none|500px|Figure 90: A report in edit mode.]]
  
 
The Author is the name you listed in '''Tools > Options > OpenOffice.org > User Data'''. The date is not correct. The columns need to be moved to the left to give a better appearance. None of the numbers are correct, but their only purpose is to show the number of decimal places.
 
The Author is the name you listed in '''Tools > Options > OpenOffice.org > User Data'''. The date is not correct. The columns need to be moved to the left to give a better appearance. None of the numbers are correct, but their only purpose is to show the number of decimal places.
Line 440: Line 444:
 
<ol style="list-style-type:lower-alpha">
 
<ol style="list-style-type:lower-alpha">
 
<li>Double-click the date field you just inserted. The Edit Fields: Document window opens (Figure 91).</li>
 
<li>Double-click the date field you just inserted. The Edit Fields: Document window opens (Figure 91).</li>
[[Image:BaseEditFields.png|Figure 91]]<br>Figure 91: Modifying a date field.
+
[[Image:BaseEditFields.png|thumb|none|500px|Figure 91: Modifying a date field.]]
  
 
<li>Since this is a dynamic report, change the Select field from Date (fixed) to Date.</li>
 
<li>Since this is a dynamic report, change the Select field from Date (fixed) to Date.</li>
Line 452: Line 456:
 
The column widths can be changed by moving the cursor over the right border of each column so that it becomes a double-headed arrow. Then drag and drop it where you want it. This has to be done for each column in each table in the report. This can also be done with the last column on the right even though there is no black border. It should now look something like Figure 92.
 
The column widths can be changed by moving the cursor over the right border of each column so that it becomes a double-headed arrow. Then drag and drop it where you want it. This has to be done for each column in each table in the report. This can also be done with the last column on the right even though there is no black border. It should now look something like Figure 92.
  
[[Image:RealignCols.png|Figure 92]]<br>Figure 92: Realigned columns in a report.
+
[[Image:RealignCols.png|thumb|none|500px|Figure 92: Realigned columns in a report.]]
  
 
[[#top|Top of page]]
 
[[#top|Top of page]]
Line 466: Line 470:
 
<li>Change the number of Decimal places to 3.</li>
 
<li>Change the number of Decimal places to 3.</li>
 
<li>Click the green checkmark. Click '''OK'''.</li>
 
<li>Click the green checkmark. Click '''OK'''.</li>
[[Image:NumberFormat.png|Figure 93]]<br>Figure 93: Option section of the Number Formating window.  
+
[[Image:NumberFormat.png|thumb|none|500px|Figure 93: Option section of the Number Formating window.]]
 
</ol>
 
</ol>
 
<li>Change the Fuel Cost field.</li>
 
<li>Change the Fuel Cost field.</li>
Line 494: Line 498:
 
Double-click the report. It should now look like Figure 94.
 
Double-click the report. It should now look like Figure 94.
  
[[Image:BaseFinalRpt.png|Figure 94]]<br>Figure 94: Final report.
+
[[Image:BaseFinalRpt.png|thumb|none|500px|Figure 94: Final report.]]
 
+
 
[[#top|Top of page]]
 
[[#top|Top of page]]
 
 
<hr>
 
[[Getting Started: Using data sources| &lt;&lt; Using data sources]] &nbsp;&nbsp;|&nbsp;&nbsp;[[Getting Started: Getting Started with Math|Chapter 11 Getting Started with Math &gt;&gt;]]
 
  
 
{{CCBY}}
 
{{CCBY}}
 
[[Category:Getting Started (Documentation)]]
 
[[Category:Getting Started (Documentation)]]

Latest revision as of 19:23, 14 July 2018


Creating queries

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.png 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 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 (Figure 2), click the Queries icon in the Databases section, then in the Tasks section, click Use Wizard to Create Query. The Query Wizard window opens (Figure 66). 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.png 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.
Figure 66: First page of the Query Wizard.

Top of page

Step 1: Select the fields.

  1. Select the CD-Collection table from the dropdown list of tables.
  2. If the Tables selection is not Table: CD-Collection, click the arrow (circled in red in Figure 66).
  3. Click Table: CD-Collection in the list to select it.
  4. Select fields from the CD-Collection table in the Available fields list.
  5. Click AlbumTitle, and use the > button (black oval in Figure 66) to move it to the Fields in Query list.
  6. Move the Artist and DatePurchased fields in the same manner.
  7. Use the up arrow to change the order of the fields: artist, album, and date purchased.
  8. Click the CD-Collection.Artist field.
  9. Click the up arrow to move it above CD-Collection.AlbumTitle.
  10. Click Next.
Figure 67: List of fields added to the query.
Tip.png 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 (circled in magenta in Figure 66).


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.

Figure 68: Sorting order page.
  1. Click the first Sort by dropdown list.
  2. Click CD-Collection.Artist to select it.
  3. 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 (Figure 68).
  4. Click the second Sort by dropdown list.
  5. Click CD-Collection.ArtistTitle.
  6. Select Ascending or Descending according to the order you want.
  7. Repeat this process for CD-Collection.DatePurchased.
  8. Click Next.

Top of page

Step 3: Select the search conditions.

The search conditions available are listed in Figure 69. 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.

Figure 69: Search conditions in the Query Wizard.
  • 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.png These conditions apply to numbers, letters (using the alphabetical order), and dates.
  1. Since we are only searching for one thing, we will use the default setting of Match all of the following.
  2. We are looking for a particular artist, so select is equal to.
  3. Enter the name of the artist in the Value box. Click Next.

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.png 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 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.png 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 of page

Step 1: Open the first query in Design View.

Click Create Query in Design View.

Step 2: Add tables.

Figure 70: Add Tables or Query window.
  1. Click Fuel to highlight it.
  2. Click Add.
  3. Click Close.
Tip.png Move the cursor over the bottom edge of the fuel table (Figure 71). 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.


Figure 71: Fuel table in query.

Top of page

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

  1. Double-click the FuelID field in the Fuel table.
  2. Double-click the Odometer field.
  3. Double-click the FuelQuantity field.

The table at the bottom of the query window should now have three columns (Figure 72).

Figure 72: Query table.

Top of page

Step 4: Set the criterion for the query.

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

  1. Type >0 in the Criterion cell under FuelID in the query table
  2. Click the Run Query icon in the Query Design toolbar. This icon is circled in red in Figure 73.
Figure 73: Query Design toolbar.

Figure 74 contains the Fuel table with my entries and the query results based upon the Fuel table.

Figure 74: Fuel table.
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 of page

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

  1. Click Create Query in Design View to open a new query.
  2. Add the Fuel table to the query just as you did in step 2: Add tables. But, do not close the Add Tables window.
  3. Add the End-Reading query to this query.
    1. Click the Query radio button to get the list of queries in the database (Figure 75).
    2. Figure 75: Selecting queries to add to another query.
    3. Click End-Reading.
    4. Click Add, and then click Close.

Top of page

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

Figure 76: 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.

  1. Double-click FuelQuantity in the End-Reading query.
  2. Double-click Odometer in the End-Reading query.
  3. Double-click Odometer in the Fuel table.
Figure 77: Added fields to the query.

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

  1. Type "End-Reading".FuelID - Fuel.FuelID in the field to the right of the Odometer field of the Fuel Table (Figure 78).
    Type the numeral 1 (one) in the Criterion cell of this column.
  2. Documentation caution.png 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 Figure 78.
    Use the arithmetical symbol between the two. More than one calculation can be done by using parentheses to group the arithmetical operations.
    Figure 78: Typing in calculation of fields.
  3. Calculate the distance traveled (Figure 79):
    • Type "End-Reading".Odometer - Fuel.Odometer in the Field cell.
    • Type >0 in the Criterion cell.
    • Figure 79: Field for distance traveled calculations.
  4. Calculate fuel economy (Figure 80):
    Type ("End-Reading".Odometer - Fuel.Odometer)/"End-Reading".FuelQuanity in the Field in the next column to the right.
  5. Figure 80: Fuel economy calculation field.

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.

  1. Click the Run Query icon in the Design Query toolbar. (Figure 73) The results are in Figure 81.
    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.
  2. Figure 81: Result of running the fuel economy query.
  3. Add Aliases: Type in aliases as they are listed in Figure 82.
  4. Figure 82: Query table with aliases added.
  5. Run the query again. The results are in Figure 83.
  6. Figure 83: 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.

  7. Hide a field that does not need to be seen.
    Remove the check in the box of the Visible cell as in Figure 84.
  8. Figure 84: Making a field invisible in a query run.
  9. Rerun the query (Figure 85).
  10. Figure 85: Query run with aliases.

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.png 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 of page

Creating reports

Reports provide information found in the database in a useful way. In this they are similar to queries. Reports are generated from the database's tables or queries. They can contain all of the fields of the table or query or just a selected group of fields. Reports can be static or dynamic. Static reports contain the data in the selected fields at the time the report was created. Dynamic reports can be updated to show the latest data.

For example, a report on vacation expenses divided into categories should probably be a static report because it is based upon specific data that does not change. However, a report on the fuel data should probably be a dynamic report, because this report depends upon data that does change.

Documentation caution.png All reports are based upon a single table or query. So you need first to decide what fields you want to use in the report. If you want to use fields from different tables, you must first combine these fields in a single query. Then you can create a report on this query.

An example of this caution is creating a report on vacation expenses. Fuel costs are one part of that report as are meal costs. These values are contained in fields of two different tables: Vacations and Fuel. So this report requires creating a query.

Top of page

Creating a static report

We will create a report on vacation expenses. Certain questions need to be asked before creating the report.

  • What information do we want in the report?
  • How do we want the information arraigned?
  • What fields are required to provide this information?
  • Will a query have to be created because these fields are in different tables?
  • Are there any calculations required in the data before being added to the report?

The expenses for our vacation are motel, tolls, miscellaneous, breakfast, lunch, supper, snacks, and fuel. One possible report would simply list the totals of each of these expense groups. Another possible report would list the expense totals for each day of the vacation. A third possible report would list the totals for each expense group for each type of payment. (This would let us know where the money came from to pay the expenses.) At the present time, using the data from the queries in a spreadsheet is the best way to handle reports like this. In the near future, the report feature will include these abilities.

For our purposes, we will create two reports. The first one will list the expenses each day other than fuel. The second report will list the fuel costs each day.

The fields we will need for the first report from the Vacations table are: Date, Motel, Toll, Breakfast, Lunch, Supper, SnackCost, and Miscellaneous. This report will not require an additional query.

The second report involves the Fuel table. Since fuel was purchased and entered into this table at times other than during the vacation, a query needs to be created that contains only the fuel purchased during the vacation.

Top of page

Vacations table report

  1. Create a new report.
    1. Click the Reports icon in the Database list in the Automobile - OpenOffice.org window.
    2. In the Tasks list, click Use Wizard to Create Report. The Report Wizard window opens.
  2. Select the fields.
    1. Select Table: Vacations in the Tables or Queries dropdown list.
    2. Use the > to move these fields from the Available fields list to the Fields in report list: Date, Motel, Tolls, Miscellaneous, Breakfast, Lunch, Supper, and SnackCost (Figure 86). Click Next.
    3. Figure 86: Adding fields to a report.
  3. Label the fields.
    Shorten Miscellaneous to Misc. Click Next.
  4. Group fields.
    Since we are grouping by the date, use the > button to move the Date field to the Grouping list. Click Next.
  5. Grouping list
  6. Sort options.
    We do not want to do any additional sorting. Click Next.
  7. Choose layout.
    We will be using the default settings for the layout. Click Next.
  8. Documentation note.png If you feel adventurous, try selecting some of the other layout choices. After selecting a choice, drag and drop the Report Wizard window so that you can see what you have selected. (Move the cursor over the Heading of this window, and then drag and drop.)
  9. Create report.
  10. Label the report: Vacation Expenses.
  11. Select Static report.
  12. Click Finished.

Top of page

Vacation fuel report

  1. Create a query containing only fuel bought on the days of the vacation.
    1. Open a query in Design View.
    2. Follow the steps for adding tables in Step 2. Add tables of Using the Design View to create a query. Add the Fuel table.
    3. Double-click these fields in the Fuel table listing: Date and FuelCost to enter them in the table at the bottom of the query.
    4. In the Criterion cell of the Date field, type the following:
    5. BETWEEN #5/25/2007# AND #5/26/2007#
      Figure 87: Setting the criterion for a query.
    6. Save, name, and close the query. (Suggestion: Vacation Fuel Purchases.)
    Tip.png When using dates in a query, enter them in numerical form MM/DDYYYY or DD/MM/YYYY depending upon your language's default setting for dates (my default setting is MM/DD/YYYY).
    All dates must have a # before and after it. Hence, May 25, 2007 is written #05/25/2007#.


  2. Open a new report.
    1. Right-click the Vacation Fuel Purchases query.
    2. Select Report Wizard from the context menu.
    Documentation note.png When a new report is opened in this way, the query used to open it is automatically selected in the Tables or Queries dropdown list.
  3. Create the report.
    Use >> to move both fields from the Available Fields to the Fields in Report list. Click Next.
  4. Label fields.
    Add a space to FuelCost to make it Fuel Cost (two words). Click Next.
  5. Group fields.
    1. Click Date to highlight it.
    2. Use > to move the Date field to the Groupings list. Click Next.
  6. Choose layout.
    We will be making no changes in the layout. Click Next.
  7. Create report (final settings).
    1. Use the suggested name, which is the same as the query.
    2. Select Static report. Click Finish.

Top of page

Creating a dynamic report

We will create a report with some statistics on our fuel consumption. To do this, we have to modify two queries: End-Reading and Fuel Economy. We will be adding the FuelCost field to the End-Reading query. Then we will add the FuelCost field from the End-Reading query to the Fuel Economy query.

Tip.png When opening a query to edit it, it might appear as in Figure 88. If you move your cursor over the black line (circled in red), it becomes a double headed arrow. Drag and drop it to a lower position.


Figure 88: Appearance of query when opened for editing.
  1. Add the FuelCost field to the End-Reading query:
    1. Right-click the End-Reading query and select Edit from the context menu.
    2. In the Fuel table list, double-click to add FuelCost to the bottom table (Figure 89).
    3. Save and close the query.
    4. Figure 89: Adding an additional field to the query.
  2. Add the FuelCost field from the End-Reading query to the Fuel Economy query.
    1. Right-click the Fuel Economy query and select Edit from the context menu.
    2. Double-click the FuelCost field in the End-Reading query list to the table at the bottom.
  3. Add a calculation field to the right of the FuelCost field.
    1. Type the following in the Field cell:
      "End-Reading".FuelCost/("End-Reading".Odometer - Fuel.Odometer)
    2. Type the following in the Alias cell:
      cost per mile
    Documentation note.png If you use the metric system, cost per km is the appropriate alias.
  4. Save and close the query.
  5. Open a new report.
    Right-click the Fuel Economy query and select Report Wizard.
  6. Select fields.
    Move all the fields from the Available fields to the Fields in report list. Use the >> to do so. Click Next.
  7. Label fields.
    Change FuelCost to Fuel Cost by placing a space between the words. Click Next.
  8. Group fields.
    Use > to move the Date field to the Groupings list. Click Next.
  9. Sort options: the wizard skipped this one.
  10. Choose layout.
    Accept the default. Click Next.
  11. Create the report.
    1. Change the report name to Fuel Statistics.
    2. The default setting is Dynamic report, so no change is necessary.
    3. Select Modify report layout. Click Finish.

Top of page

Modifying a report

At the end of the last section, we left the Fuel Statistics report open in the edit mode (Figure 90). We will be working on that report. These same steps can be used with any report that you open for editing.

Figure 90: A report in edit mode.

The Author is the name you listed in Tools > Options > OpenOffice.org > User Data. The date is not correct. The columns need to be moved to the left to give a better appearance. None of the numbers are correct, but their only purpose is to show the number of decimal places.

Top of page

Step 1: Change the date.

  1. Click to the right of the date (4/26/20) so that the cursor is next to the field. Use the Backspace key to erase the date.
  2. Insert > Fields > Date. This places today's date where the original date was.
  3. Change the date formating:
    1. Double-click the date field you just inserted. The Edit Fields: Document window opens (Figure 91).
    2. Figure 91: Modifying a date field.
    3. Since this is a dynamic report, change the Select field from Date (fixed) to Date.
    4. Change the Format to what you desire. (I use the Friday, December 31, 1999 choice.) Click OK.

Top of page

Step 2: Change the column widths.

The column widths can be changed by moving the cursor over the right border of each column so that it becomes a double-headed arrow. Then drag and drop it where you want it. This has to be done for each column in each table in the report. This can also be done with the last column on the right even though there is no black border. It should now look something like Figure 92.

Figure 92: Realigned columns in a report.

Top of page

Step 3: Change the number formating in the cells.

The fuel quantity should have three decimal places. The Begin, End, and Distance should have one decimal place. Fuel Cost should be currency and have two decimal places, and Cost per mile should have three decimal places.

  1. Right-click the cell below Quantity to open the context menu. (The cell is circled in red in Figure 92.)
  2. Select Number format.
  3. In the Options section (Figure 93),
    1. Change the number of Decimal places to 3.
    2. Click the green checkmark. Click OK.
    3. Figure 93: Option section of the Number Formating window.
  4. Change the Fuel Cost field.
    1. Right-click in the cell below Fuel Cost.
    2. Select Number Format.
    3. In the Category list, select Currency. Click OK.
  5. Change the Cost per mile field.
    1. Right-click in the cell below Cost per mile.
    2. Select Number Format.
    3. In the Category list, select Currency.
    4. In the Option section:
      • Set the number of decimal places to 3.
      • Click the green checkmark.
    5. Click OK.

Top of page

Step 4: Save and close the report.

Double-click the report. It should now look like Figure 94.

Figure 94: Final report.

Top of page

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools