|
Ramapo
Masters of Science Tools and Concepts of Data Analysis: Dataweb FrontPage Queries (home) Overview | Getting Started | Step 1 | Step 2 | Step 3 | Step 4 | Step 5 | Results Overview: FrontPage Queries A query can be defined a selected look at some of the data in a database. This information may be drawn from a single table or multiple tables in the database. Most often a query will focus on answering a particular question of set of closely related questions. For example, a query focused on providing information about contacting an individual might include only their first name, last name, email address, and phone number even though many more pieces of information might be available. While a query often presents data in a tabular form, it should not be confused with a table. A table stores data. A query presents a subset of that data for viewing. A query contains no data. It is a stored set of instructions about how to display particular information from a database. If a query were displayed, data was then added to the database, and the query was run again, the new data would be reflected in the results. Deleting a query does not delete data. A query has two major functions used to select and present the data. The first is referred to as ordering or sorting but would be more commonly recognized as alphabetizing. When ordering digits precede letters. You may order in either ascending order (0 to 9 then A to Z) or descending order (9 to 0 then Z to A). A query may be ordered by multiple criteria with each criteria having a priority in relation to the others. For example, If I sort by last name then first name, the second sort criteria will only be applied in the case that there are two or more instances of a particular last name. If I changed this sort to teacher, last name, then first name, students would first be grouped by their associated teachers then alphabetized within these groups essentially creating class lists. Ordering should be used on every query. The second major function of a query is to filter data. Through filtering, data is selected based on criteria. Data that does not meet the criteria is excluded. Let's assume that I have a database containing all students in District X and that District X consists of three schools: Madison, Hamilton, and Jefferson. If I create a query to display student names, and include the criteria of "Hamilton School," only students from that school will be displayed. If I also add the criteria of "Teacher Z," then only students from Teacher X's class at Hamilton School will be displayed. FrontPage can create simple queries through its Results Wizard that contain multiple sort and filter options in order to refine the results displayed. This feature consists of a 5 step wizard with additional options available at some steps and is most convenient when displaying data selected from a single table in the database. This tutorial will explain the basic functions of each of the 5 main steps. Later tutorials will explain options in greater depth. Getting Started: Creating a FrontPage Query A FrontPage Query exists on a web page. It must be saved as an .asp or .aspx page. For purposes of this class, we will be using .asp. Create a new page | Insert | Database | Results
Step One: Selecting a Database Connection The first step of the Database Results wizard involves defining which technology you will be using and selecting a database connection. The options for technologies are asp and the more advanced aspx. We will be using asp. The database connection is a set of instructions contained in the global.asa file that allows the web form or query to locate and interact with the database. This connection was created for you when you created the database. If you have only one database in your project, you should have only one connection. If you have three, you should have three connections. If you created more than one database and then deleted one or more of them, you may have more connections than you need which can lead to confusion. Verify that asp is selected | select the connection you need from the dropdown box | Next
Step Two: Selecting a Record Source At this stage you must select the table or query from which you would like to retrieve data. These are the tables or queries that exist within the Access database. When you first create your database, a single table is created and named "Results." This will be the only table in your database unless you have created additional tables or queries directly in Access. Select the table or Query you wish to use from the Dropdown box | Next
Step Three: Selecting Fields to Display Step three of the wizard is where the bulk of the logic for the query is set. This tutorial will explain only the basics of step three. Subsequent tutorials will go into greater depth at this stage. The first task you must perform at step three is to determine which fields you wish to display. Generally queries display a focused subset of data, but by default FrontPage will try to display all fields from the table you have selected. To modify the fields to be displayed: Edit List
Select the field you wish not to display in the right pane | Remove | Repeat as needed
If you accidentally remove a field and wish to replace it: Select the field you wish to add in the left pane | Add | Repeat as needed
The fields will be displayed on the page in the order that they appear in the list. They can be moved later, but you might want to save time by moving them here. Select the field you wish to move in the right pane | Move Up or Move Down as needed
When the settings have been completed: OK or Cancel to return to main step three window without saving changes
You have returned to the step three main window. To add sorting options - More Options
Setting on this screen effect the number of records returned and associated message. To change the number of records returned, change the number
To allow an unlimited number of records to be returned, uncheck the checkbox
To change the message returned if no records are found, edit the text box
From this screen, you can access sorting and criteria options. Criteria will be addressed in a later lesson. To access the sorting options - Click Ordering
To add sorting criteria, select the field | Add
To remove a sorting criteria, Select the field from the right box | Remove To change the sort order, Select the field | Move Up or Move Down To switch a sort order between Ascending and Descending, Select the field | Change Sort
When sort settings are complete, click OK to save or Cancel to escape without saving
You have returned to the option window, Click OK to continue
You have again returned to the step three main window. We will explore stage three options in greater detail in another tutorial. To advance to step 4 click the "Next" button
Step Four: Formatting Options The fourth step of the wizard relates primarily to how the data will be displayed. The results of the query will be displayed within a table. There is no choice about this. You may, however, choose to hide or show the borders of the table by checking or unchecking the "use table borders" checkbox. You may set the table expand to the width of the page or not by using the "expand table to width of page" checkbox. This is not a terribly important choice as you will need to reformat the table once it has been created. You may choose to include or not include a header row to display a column label for the data beneath it. For example: If you choose to display a header row and have a field called "Teacher," the word Teacher would appear once near the top of the page and the names of the teachers associated with each record would appear in a list below. If you choose not to display a header row, the word "Teacher" would not appear above the list. Set this option with the "include header row w/ column labels" check box. Choose settings for this page | Next
Step Five: Grouping Records This final step of the wizard contains two simple options: "display all records together" or "split records into groups." If you choose the first options and 85 records are returned, all 85 will be displayed together on one long page and would be viewable by using the browsers scroll bar. If the "split records into groups" option is chosen, you must type a number into the "records per group" box. This number represents the maximum number of records that can be displayed at one time. If that number is exceeded, a set of buttons will appear at the bottom of the page to allow users to move through the results. If the "records per group" had been set to 10, the user would need to move through 9 pages of information to view all 85 records. Generally, but with some exceptions, it is best to display all records on a single page. Choose settings for this page | Finish
Results: The Query Created After completing step five, the query will be displayed. The area between the two yellow sections is a template that will be repeated for each record returned. The header row or other text added above or below this area is the same as any other text or graphic on any other web page.
Save the page - File | Save | name the page | Save
Site Designed and
Maintained by rsciorra |
