Sorting and Filtering Text-Based Fields

Sorting Records on Empty Fields   Introduction In the previous lesson, we saw how to create queries to isolate or select a list of columns from a table or another query and we saw that data analysis could be performed on queries. In reality, queries are not the only objects used to analyze data. The main reason for using a query is that it can save its results to be used when necessary. The analysis done on a table or a form is usually temporary and may be lost when you close the table or the form. Other than that, most data analysis operations you can perform on a query are also available on tables and forms. Data analysis can be performed on tables, queries, and/or forms. To analyze data, display the table or query in Datasheet View, or the form in Form View. To assist you with data analysis, when a table, a query, or a form is displaying in Datasheet View, the Ribbon is equipped with a section titled Sort & Filter in the Home tab: Also, when a table or a query displays in Datasheet View, each column header displays a down pointing arrow button on its right side:

Introduction to Data Analysis

Querying a Database   Introduction After creating one or more tables in a database and populating it (them) with values, one of the next steps you would take is to examine or analyze the values in the database. Analyzing the values in a table is also referred to as querying. To query the records of a database, you can use Boolean algebra combined with some operators. Boolean Algebra works on logical statements. A statement is a sentence that acknowledges a fact or a possibility. That fact is eventually evaluated as being true or false. There are three main types of logical statements: Some statements can clearly be evaluated as being true or as being false (or at least they would hardly change). Examples include: "An hour contains 80 minutes", "Student A is a girl", "Gabon was colonized by Portugal", or "Argentina won the 1998 World Cup" Some statements are true at one time but can change to false at another time. Examples include: "Melbourne is the capital of Australia", "It is now 12:00 PM", or "It is raining" Some statements cannot be easily evaluated. Such statements could be vague. Examples include: "This person is too young", "It is going to be a sunny day tomorrow". Some other statements, although they can logically be evaluated, because of the way they are stated, they appear as vague. Examples include: "Santiago is a large country" (first of all, Santiago is not a country; even if Santiago were a country, what is the criterion for evaluating that a country is large?) These are the types of evaluations you make when analyzing the records of your database. Introduction to SQL

Grouping Records on a Report

Introduction to Grouping Records   Overview Consider the following table that represents a list of students:   When creating this type of table, you may have values that can be considered in categories, such as sold and unsold items, discounted and not discounted items, or males and females. When preparing a report, you can show its records by each category. For example, on a table of students, you can show the list of girls only first. Then, after the group of girls, you would show the list of boys. This is referred to as grouping the records. Practical Learning: Introducing Groups

Characteristics of a Report

The Page Setup of a Report   Introduction Page setup consists of customizing some of the behind-the-scenes aspects of a piece of paper resulting from printing. These include the size of a report and the orientation, etc. You have various options. After displaying a report, you can click File, click Print, and click Print. On the Print dialog box, click the Setup... button. You can then make modifications on the dialog box and click OK. If you don't want to print, click Cancel on the Print dialog box but the changes you would have made on the Page Setup dialog box would be kept. When a report is displaying in the Design View, the Ribbon is equipped with a section named Page Setup: When the report is displayed in Print Preview, the Ribbon is equipped with a section labeled Page Layout: The Page Layout section is equipped with a button labeled Page Setup. When clicked, this button would open the Page Setup dialog box that provides the same options of the Page Layout section and more. Practical Learning: Introducing Page Setup

Creating a Report

Report Design Fundamentals   Introduction So far, we have spent a great deal of time on forms and their design. This was because the users spend most of their time using them. Then comes the time to print data. In Lesson 3, we saw that you could print from a table or a form. The best way to print is through a report and we had a simple introduction in that same lesson. Like a form, a report should be meticulously designed. Unlike forms, some details present a different concern on reports. As mentioned in previous lessons, a report shares many characteristics with a form. In the Navigation Pane, a report is represented by a green icon  that you can use to open the report. Like a form, once opened, a report is represented by a tab or a title bar (for an overlapped database) that displays its name. By its definition, a report mimics a piece of paper and therefore it uses a white background. Like a form, a report can be displayed in different views but the report has more varieties. A Review of Report Creation

Database Date and Time Values

Fundamentals of Times   Introduction to Times A time is a non-spatial measure of the number of seconds that have elapsed in a set period. Starting at a specified period and counting incrementally, if the number of seconds reaches 60, the measure is called a minute. If the number of minutes reaches 60, the measure is called an hour. If 24 measures of hours occur, the measure is called a day (actually a day is 24 hours plus a few more seconds but the seconds are taken into consideration only every 4 years). Practical Learning: Introducing Time Values Start Microsoft Access To create a new database, click File Name and type Georgetown Dry Cleaning Services Click Create Right-click the Table1 tab and click Design View Set the name to Cleaning Orders and press Enter Replace ID with CleaningOrderID Click the empty box under CleaningOrderID and create the following fields:   Field Name Data Type Field Size Format Default Value CleaningOrderID         Customer Name   50     Customer Phone   20     Unit Price Shirts Number Double Fixed 1.25 Quantity Shirts Number Integer   0 Unit Price Pants Number Double Fixed 1.95 Quantity Pants Number Integer   0 Other Item1   50   None Unit Price Item1 Number Double Fixed 0.00 Quantity Item1 Number Integer   0 In the top section of the table, set the Data Type of the Other Item1 field to Lookup Wizard... In the first page of the wizard, click the second radio button: I will type in the values that I want Click Next In the second page of the wizard, click the first empty field under Col1 Create the following items

Database Conditional Values

Boolean Values   The Boolean Data Type A value is referred to as Boolean when it is either true or false. In other words, the value can only be one or the other, never both, and not any other value. To support Boolean values, Microsoft Access provides a data type named Boolean (but we will not explicitly use that data type in our lessons).   Practical Learning: Introducing Boolean Types Start Microsoft Access and, to create a new database, click Blank Database Set the File Name to Real Estate4 and click Create Double-click ID, type PropertyID, and press Enter Right-click the Table1 tab and click Design View Set the name to Properties and click OK Click the empty field under PropertyID and type Property # Click the empty field under Property # and type p For its Data Type, select Lookup Wizard... In the first page of the wizard, select the second ration button and click Next Click under Col1 and type Single Family Press the down arrow key and type Townhouse Press the down arrow key and type Condominium Press the down arrow key and type Unknown Click Next Set the lookup column label to Property Type and click Finish Change the following properties: Field Size: 40 Default Value: "Unknown" In the top section of the table, click the first empty field under Property Type and type p For its Data Type, select Lookup Wizard... In the first page of the wizard, select the second ration button and click Next Click under Col1 and create the following items:   Unknown Excellent Good Condition Needs Some Repair Bad Shape Click Next Set the lookup column label to Condition and press Enter Change the following properties: Field Size: 40 Default Value: "Unknown" Complete the list of fields with the following (ignore any information that is not provided):

Numeric Values

Integer-Based Data Types   Introduction to Numeric Fields A number is a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), a combination of digits, or a combination of one or more digits, a separator, and one or more digits. Microsoft Access supports three categories of numbers and there are various ways you can apply one to a field. You can work in either the Datasheet View or the Design View of a Table. To create a field that supports numbers:  If you are working in the Datasheet View, click Click to Add. In the list that appears, click Number. Then type the name of the field If you are working in the Design View. Specify the name of the field. In the corresponding Data Type, select Number Introduction to Natural Numeric Fields A natural number is one that contains either only one digit or a combination of digits and no other character, except those added to make it easier to read. Examples of natural numbers are 122, 8, and 2864347. When a natural number is too long, such as 3253754343, to make it easier to read, the thousands are separated by a special character. This character depends on the language or group of languages and it is called the thousands separator. For US English, this character is the comma. The thousands separator symbol is mainly used only to make the number easier to read. You should be careful to use it in your database. Microsoft Access suppports different types of natural numbers.

Strings

String-Based Data Types   Introduction to Strings A string is text made of a readable letter, a symbol, a word or a group of words. When working on a table in the Datasheet View, to create a Text-based field, on the table, click Click to Add and select Text. If some columns exist already, to insert a text-based field between two columns, click the column header, or a cell under the column, that will precede it. On the Ribbon, click Fields. In the Add & Delete section, click Text . If a column has been created already and it is using a data type other than text, to change it, click its column header or a cell under its column. On the Ribbon, click Fields. In the Formatting section, click the arrow of the Data Type combo box and select Text If you are creating the table in Design View, to configure a field that can hold any type of string, specify its Data Type as Text: A Text data type allows the user to type any kind of characters or group of characters. This field can hold up to 255 characters. Practical Learning: Introducing Strings

Getting Assistance With Data Entry

Introduction to Built-In Functions   Character Retrieval In order to create expressions that complete a database as we saw in previous lessons, you can use various functions available from Microsoft Access. These are referred to as built-in functions. The Chr() function is used to retrieve a character based on an ASCII character number passed to the function. It could be used to convert a number to a character. It could also be used to break a line in a long expression. The syntax of this function is: Chr(Number)   Based on the table of ASCII characters, a call as Chr(65) would produce the letter A. Not all ASCII characters produce a known letter. For example, when Chr(10) is used in a string, it creates a "new line". The Input Box Although most of the user's data entry will be performed on fields positioned on a form, you can display a special form to the user and expect a value. This form (actually a dialog box) is called an input box. To support this, Microsoft Access provides a function called InputBox. The basic syntax of the InputBox() function is: InputBox(prompt)