My Access Handout 3 Detailed

CREATING A NEW DATABASE Choosing the Right Way to Create your Database Before you create your database, you have an important decision to make. Should you create a blank database from scratch, and then manually create all the tables, reports, and forms you’ll need? Or should you use a Database Wizard, which does all that for you? Database Wizard. Access comes with several Database Wizards. These are mini-programs that question you about your needs and then create a database structure that matches them. (You will enter the actual data yourself). A. CREATING A BLANK DATABASE Creating a blank database is very simple because you’re just creating an outer shell at this point, without any tables, forms, etc. If you just started Access, and the Microsoft Access dialog box is still displayed, follow these steps: 1. Click Blank Database. 2. Click the OK button. If the dialog box is gone, you can’t get it back until you exit from Access and restart it. But you don’t need that dialog box to start a new database. At any time, you can follow these steps: 1. 2. 3.

Select File, New or click the New button on the toolbar. The New dialog box appears. Click the General tab if necessary to bring it to the top. Then double-click the Blank Database icon, and the New Database dialog appears. Type a name for your new database (preferably something descriptive) in the Fine Name box. For example, if you type “Silliman Records.” Then click Create. Access creates the new database. Your database is completely blank at this point. You can click any of the tabs in the database window, but you won’t find anything listed on any of them. Later, you’ll learn to create tables, forms, queries, and reports to fill these tabbed windows.

B. CREATING A DATABASE WITH DATABASE WIZARD A database wizard can create almost all the tables, forms, and reports you will ever need, automatically! The trick is choosing the right wizard to suit your purpose. Follow these steps: 1. 2. 3. 4. 5. 6. 7.

If you’ve just started Access, the Microsoft Access dialog box is still onscreen, click Database Wizard and click OK. Or, if you’ve already closed the dialog box, select File, New Database. Either way, the New dialog box appears. Click the Database tab to display the list of wizards. Click one of the Database Wizards. (They’re the icons with the magic wands across them.) For this example, choose Contact Management. A preview appears in the Preview area. When you’ve found the wizard you want, click OK. The File New Database dialog box appears. Type a name for the database and click Create to continue. The wizard starts, and some information appears explaining what the wizard will do. Click Next to continue. A list of the tables to be created appears. The tables appear on the left, and the selected table’s fields on the right. Click the table and examine its list of fields. Optional fields are in italic. To include an optional field, click it to place a check mark next to it.

If you don’t want those tables and fields, that’s the price you pay for going with a prefabricated wizard. You can’t deselect any fields except the optional (italicized) ones. But you can delete the tables and fields you don’t want later. (Optional) If you are creating this database for a learning experience only, click the Yes, Include Sample Data check box. This tells Access to enter some dummy records into the database so you can see how they will work in the database. 9. Click Next to continue. The wizard asks you what kind of screen display style you want. 10. Click a display style in the list and examine the preview of that style that appears. When you have decided on a style, click it and click Next. The wizard asks you for a style for printed reports. 11. Click a report style and examine the preview of it. When you have decided on a style, click it and click Next. 8.

12. The wizard asks what title you want for the database. The title will appear on reports, and it can be different from the file name. Enter a title. 13. (Optional) If you want to include a picture (such as your company logo) on your forms and reports, click the Yes, I’d like to Include a Picture check box. Then click the Picture button, choose a graphics file (change the drive and/or folder if needed), and click the Open to return to the wizard. 14. Click Next to continue. When you get to the Finish screen, click Finish. The wizard goes to work creating your database. (It may take several minutes). CREATING A TABLE WITH THE TABLE WIZARD Why Create a Table? Tables are the basis for the whole database. Tables hold your data. Everything else is just dress-up. When you create a table, you can create it “from scratch”, or you can use the Table Wizard. A. Creating a Table with Table Wizard 1. 2. 3. 4.

5. 6. 7. 8. 9.

10. 11. 12.

13.

Select Insert, Table from the menu bar along the top of the screen. Or in the database window, click the Tables tab and click New. The New Table dialog box appears. Click Table Wizard and click OK. The Table Wizard now appears. Click a table in the Sample Tables list. Access then displays its fields in the Sample Fields List. There are two separate lists of tables. By default, you see the Business list. To see the Personal list, click the Personal option button below the Sample Tables list. If you see a field that you want to include in your new table, select in the Sample Fields list. Then click the > button to move it to the Fields in My New Table list. To move the entire contents of the selected Sample Table to your list, click the >> button. If you see a field that is close to what you want, but you prefer a different name for it, first add it to your list. Then click the field name to select it, click the Rename Field button, type a new name, and click OK. This renames the field on your list only – not on the original. Repeat steps 3 and 4 to select other fields from other sample tables until the list of fields in your new table is complete. (You can move a field from the list by clicking the < button, and you can remove all the fields and start over by clicking the << button.) When you’re finished adding fields, click Next to continue. Next the wizard asks for a name for the table. Type a more descriptive name to replace the default one. Click Yes, Set a Primary Key for Me to have the wizard choose your primary key field, or No, I’ll Set the Primary Key to do it yourself. If you choose Yes, skip to step 10. Primary Key Field is the designated key field for which every record must have a unique A dialog box appears asking which field will be the primary key. Open the drop-down list and select the field. Choose a data type for the primary key field:  Consecutive numbers Microsoft Access assigns automatically to new records. Choose this if your primary key field is a simple record number. (That is, if you’re numbering records you enter consecutively when you enter them.)  Numbers I enter when I add new records. Choose this if you want to enter your own numbers. Access will not allow you to enter any letters. This choice works well for unique ID numbers such as driver’s license numbers.  Numbers and/or letters I enter when I add new records. Choose this if you want to include both numbers and letters in the field. For instance, if your primary key field will contain vehicle identification numbers for the cards in your fleet, you will need to enter both numbers and letters. Click Next to continue. If you already have at least one table in this database, a screen appears asking about the relationship between tables. Just click Next to move past it for now. At the Finish screen, click one of the following options:  Modify the table design. This takes you into Table Design view, the same as if you had created all those fields yourself. Choose this if you have some changes you want to make to the table before you use it.  Enter data directly into the table. This takes you to Table Datasheet view, where you can enter records into the rows of the table. Choose this if the table’s design seems perfect to you as-is.  Enter data into the table using a form the wizard creates for me. This jumps you ahead a bit in this part of the book; it leads you right into the Form Wizard. Leave this one alone for now if you want to continue following along with the lessons in this part in order. Click Finish, and you’re taken to the area of Access that you indicated you want to go. If you decide you don’t want to work with this table anymore for now (now matter what you selected in step 12), click the Close (X) button for the window that appears.

Now you have a table. In the Database window, when you click the Tables tab, you can see your table in the list. B. CREATING A TABLE WITHOUT A WIZARD WHY NOT USE A WIZARD? Access’s wizards are very useful, but they do not offer the flexibility you have when performing the equivalent tasks “from scratch.” For instance, if you want to create a table that contains special fields not available in a wizard, you are better off creating that table in Table Design view. CREATING A TABLE IN TABLE DESIGN VIEW Table Design Window- is used to define the fields that will create the structure for the new table. The window is divided into three columns, defined as field name, data type, and description. To create a table in Table Design view, follow these steps:

1. Select Insert, Table or from the Database window, click the Table tab and click the New button. The New Table dialog box appears. 2. Click Design View and click Ok. Table Design view opens. 3. Type a field name on the first empty line in the Field Name column. Then press Tab to move to the Data Type column. 4. When you move to the Data Type column, an arrow appears there for a drop-down list. Open the Data Type dropdown list and select a field type 5. (Optional) Press tab to move to the Description column, and then type a description of the field (The table will work fine even without a description.) 6. In the bottom half of the dialog box, you see Field Properties for the field type you selected. Make any changes desired to them. Switching between views. At any time after you’ve entered the first field, you can switch to Datasheet view to see how your table is going to look. Just select View, Datasheet. You may be asked to save your work before you enter Datasheet view. If so, click Yes, enter a name, and click OK. 7.

If you have more fields to enter, repeat steps 3 through 6.

8. Click the Table Design window’s Close (X) button. 9. When you are asked if you want to save your changes to the table, click Yes. The Save As dialog box appears. 10. Type a name for the table in the Table Name text box and click OK. Field Names Each field in a table must be given a unique name. The name can be 1 to 64 characters in length including letters, numbers, and spaces. Access field names must adhere to the following limitations.  The name cannot include periods (.), exclamation marks (!), or brackets ([ ]).  The name cannot begin with one or more spaces.  The name cannot contain control characters (ASCII codes 0-31). DATA TYPES The data type selected for a field controls what can be entered into that field and what special commands and functions can be applied to the data. Access recognizes eight different types of fields:

 Text. Text fields are used for storing text information up to 255 characters in length. This type of field is the most    

common type because it is used for short text entries, such as names and addresses. Memo. Memo fields are used for storing large blocks of text up to 32,000 characters in length. This type of field is used when you want to enter or import paragraph type text. Number. Number fields are used for numeric values that furnish data for mathematical calculations. Date/Time. Values entered into this field type in standard date or time formats. Currency. This field type enables for the storage and precise calculation of numeric values to (+/-) 900 trillion. Currency values provide for accurate calculations of very large numbers.

 Counter. This field type is linked closely with how Access uses index keys to organize and manipulate database  

tables. A counter field provides a unique numeric value for each record added to a table, starting with 1 for the first record, and so on. Counter values are generated by Access and cannot be edited or changed by the user. Yes/no. This field type is used for recording logical yes/no or true/false values. The actual data stored for this field type is a 0 for false, or 1 for true, but the field shows the words Yes or No. By default, the value of a Yes/No field is zero or false. OLE Object. This field type is related to the windows 3.1 feature object linking and embedding, referred to as OLE. OLE is designed to implement the exchange of data between Windows applications.

Description The description column is used to enter a text description of the field. This data has no functional purpose in the Access system but is useful when you want to recall or indicate to others the purpose of a given field. THE PRIMARY KEY Every table must have at least one field that has a unique value for each record. For instance, in a table of the dogs your kennel owns, you might assign an ID number to each dog, and have an ID # field in your table. Or you might choose to use each dog’s AKC (American Kennel Club) registration number. This unique identifier is known as the Primary Key field. A primary key performs three related but different functions:  Record Order. The record in a table appears in order in the table display window, based on the values in the key field or fields. If the key field is Last Name, the records are listed alphabetically by last name.  Prevent Duplicate Entries. Because key fields must contain unique values, designation of a field, such as Social Security Number as a key, eliminates the possibility of entering the same time into two different records.  Speed Up Searches. The fastest way to locate a record in a table is to search on an indexed field. Designating the last name field as a key enables you to quickly search for a specific last name. You must tell Access which field you are going to use as a primary key so that it can prevent you from accidentally entering the same value for more than one record in that field. To set a primary key, follow these steps: 1.

In Table Design view, select the field that you want to use for the primary key.

2. Select Edit, Primary Key or click the Primary Key button on the toolbar. A key symbol appears to the left of the field name SOME DATA ENTRY TRICKS Sure, you can enter all your data with nothing more than the Tab key and some typing. But here are a few keyboard tricks that will make the job easier:   

To insert the current date, press Ctrl+; (semicolon). To insert the current time, press Ctrl+: (colon) If you have defined a default value for a field (in Table Design view), you can insert it by pressing Ctrl+Alt+Spacebar. To repeat the value from the same field in the previous record, press Ctrl+’ (apostrophe)

PRINTING A TABLE Normally, you will not want to print a table – it won’t look very pretty. A table is just a plain grid of rows and columns. Instead, you’ll want to create and print a report that contains exactly the data you want. However, sometimes you may want a quick printout of the raw data in the table. In that case, follow these steps: 1. 2.

Open the table Click the Print button on the Standard toolbar. Access prints the table.

EDITING DATA IN A TABLE Changing a cell’s content: Editing a cell’s content is not easy. You can either replace the old content completely or edit it. Which is better? It depends on how much you need to change; you make the call.

Replacing a Cell’s Content If the old content is completely wrong, it’s best to enter new data from scratch. To replace the old content in a field, follow these steps: 1.

Select the cell whose contents you want to replace. Do this by clicking it or by moving to it with the keyboard. If you are going to select the cell by clicking it, position the mouse pointer at the left edge of the field sox the mouse pointer becomes a plus sign. Then click once to select the entire document. 2. Type the new data. The new data replaces the old data. Editing a Cell’s Content If you need to make a small change to a cell’s content, there’s no reason to completely retype it. Instead, edit the content. Follow these steps to learn how: 1. 2. 3. 4.

Position the mouse pointer in the cell so the mouse pointer looks like an I-beam. Click once, and an insertion point appears in the cell Use the arrow keys to move the insertion point to the location in the cell where you want to start editing. Press Backspace to remove the character to the left of the insertion point, or press Delete to remove the character to the right of it. Then type your change/

Field Properties When a field type has been selected for a field, the bottom portion of the table design window changes to reveal the field properties. Field Properties- are settings that control various aspects of the field's behavior, such as the size of the field, any special formatting to be applied during data entry, a default value for the field, validation rules to control what can be entered into the field, and whether or not an index should be created for the field. It enables you to control the behavior of the field when data is entered into the table. Field properties fall into four broad classifications:   



Size. Field size sets the maximum size of the data that can be entered into a field. With text fields, the size restricts the number of characters you can enter. Format. Format refers to the way the data entered into the field appears. A field format controls which style of display is used. Validity rules. Validity rules are logical tests that can be applied to the information entered into a field. When an entry is made in a field, Access applies the validity rules to the entry. If the entry fails to pass the test, the entry is rejected. Default values. Field properties can be used to establish a value that is automatically inserted into a field each time a new record is added to the table. Default values save time by entering the most common entry for a field, and ensure that a field will not have a blank entry. The exact list of settings on the field’s property sheet varies with the field type because not every field property can be applied logically to each field type, as shown in table 1.1. Field Property

Caption Decimal Places Default Value Field Size Format Indexed Validation Rule and Validation Text

Can Apply To Text, Memo, Number, Date/Time, Currency, Counter, Yes/No, OLE Object Number, Currency Text, Memo, Number, Date/Time, Currency, Yes/No Text, Number Text, Memo, Number,Date/Time, Currency, Counter, Yes/No Text, Number, Currency, Date/Time, Counter Text, Memo, Number, Date/Time, Currency, Yes/No, OLE Object

The Field Size Property The Field Size Property affects only the Text and Number field types and controls the maximum size of the entry that can be made into either type of field. A. Text Field Size In a text field, the field size sets the maximum amount of characters that can be entered into the field. By default, each text field is given a size of 50 characters. The most common reason to change the field size is to restrict entry to items of a certain length. Setting the field size to 2 characters would imply that only 2 character state abbreviations should be entered into the field.

B. Number Field Size The field size property of a numeric field displays a list of five options: 1. Byte 3. Long Integer 5. Double 2. Integer 4. Single Property Setting Byte Integer Long Integer Single Double

Value Range 0 to 255 -32,768 to 32,767 -2,147,483,648 to 2,147,483,647 -3.402823E38 to 3.402823E38 -1.79769E308 to 1.79769E308

Decimals None None

Memory Used 1 byte 2 bytes

None

4 bytes

Up to 7 places

4 bytes

Up to 15 places

8 bytes

The Format Property The format property affects all the field types with the exception of OLE objects. A format is a specification that affects how the data in the field, if any, is displayed on-screen. Formats do not affect the contents of a field, only the appearance of the data on-screen. Predefined Formats A. Text and Memo Formats Text and Memo field formats can be used to control the display of data entered into the field in two ways. 

Fill Templates. Certain entries, such as phone numbers or social security numbers, have a fixed structure in which special characters are inserted at specific locations. You can use formatting to insert characters, such as the dashes. Access recognizes two symbols as placeholders for characters in a field. @ This symbol causes a space to be displayed when no character is entered into that place. & This symbol does not show a space when no character is entered for that position. ! Fill the field from left to right instead of the default, right to left.



Force Case. The following symbols cause the letters in the field to appear as all uppercase or all lowercase letters. You should enter only a single symbol to format the entire field as upper- or lower-case text. One-letter characters are not affected by this format option. < Force all characters to lowercase > Force all characters to uppercase

Format (@@@)-@@@-@@@@

Data Entered 5103331212 3331212 12

Field Shows (510)-333-1212 ( )-333-1212 ( )- -12

(&&&)-&&&-&&&& &&&-@@@-@@@@

!(@@@)-@@@-@@@@ !(&&&)-&&&-&&&&

5103331212 3331212 12 5103331212 3331212 12

(510)-333-1212 ()-333-1212 ()- - 12 510-333-1212 -333-1212 - - 12

5103331212 3331212 12 5103331212 3331212 12

(510)-333-1212 (333)-121-2 (12)- (510)-333-1212 (333)-1212(12)- -

B. Number, Currency, and Counter Formats The formatting options available for number, currency, and counter fields are more varied and complex than the formatting options available for text and memo fields. Access provides six predefined formats that can be selected from a drop list : 1. General Number- Displays the number as entered. Very large or very small numbers entered into a single or double number field may be converted to scientific notation (default format for a number field). 2. Currency- Displays comma-separated numbers preceded by a $ and rounded to two decimal places. (default for currency fields). 3. Fixed- Displays numbers rounded to two decimal places. No comma separators or dollar signs. 4. Standard- Displays numbers rounded to two decimal places with comma separators to the left or the decimal point. 5. Percent- Moves the decimal point two places to the right and shows the value rounded to two decimal places followed by a %. 6. Scientific- Forces all numbers to be displayed in scientific notation in which only a single digit appears to the left of the decimal point and the magnitude of the number is expressed as an exponent. Number Format Examples Format General Number Currency Fixed Standard Percent Scientific General Number Currency Fixed

Entered 12345.6789 12345.6789 12345.6789 12345.6789 12345.6789 12345.6789 -12345.6789 -12345.6789 -12345.6789

Displayed as 12345.6789 $12,345.68 12345.68 12,345.68 1234567.89% 1.23E+04 -12345.6789 ($12,345.68) -12345.68

C. Date/Time Formats Access provides seven predefined date formats that can be selected from a drop list in the field’s Format property box: Format General date Long date Medium date Short date Long time Medium time Short time

Example 11/30/92 11:26:48 AM* Monday, November 30, 1992 30-Nov-92 11/30/92* 11:26:48 AM* 11:26 AM 11:26

D. Yes/No Formats Yes/No type fields are used to record logical information. Logical information is a Yes/No type answer to some question.

User-Defined Formats Access enables you to create your own user-defined formatting that can be applied to all field types except OLE objects, which do not support the format property. A user-defined format is created by directly entering the formatting symbols that describe the format you want to apply in the Format box on the field’s property sheet. A. Number Formats You may find that the predefined numeric formats do not fit exactly the format you have in mind for a number or currency field. . (period) 0 # % $ , (comma) E-/E+ * Number Formats with # and 0 symbols Format 10.125 ##.### 10.125 00.000 10.125 ##.000 10.125 0.### 10.125

Indicates where the decimal separator should be displayed. The 0 is a fixed placeholder for a digit. When the value has no digit for that place, a zero is displayed. The # is a placeholder for a digit. When the value does not have a digit for that place, nothing is displayed. Displays the value as a percent. Displays a $ in front of the value. Inserts a separator between thousands Specifies scientific notation for the displayed values. Fills unused portion with a character

2500 2500 2500.000 2500 2500

1.5 1.5 01.500 1.500 1.5

The # is used to limit the display to significant figures. Places not needed are not displayed. The 0 symbol always displays a digit. If the value has a significant digit for that place, it is displayed; otherwise, a zero is displayed. The 0 is most often used to display a fixed number of decimal places to the right or left of the decimal point. Formats with % Format .10125 .5 #% 10.125% 50% #.000% 10.125% 50.000% ##.00##% 10.125% 50.00% 000.0000% 10.1250% 050.0000% The % symbol causes the value to be displayed as a percent; the decimal point is displayed two places to the right of its actual location in the number. A percent sign follows the value to indicate that it is being displayed as a percent. The % symbol cannot be used by itself but must be combined with other symbols, such as the # and 0 digit symbols. Use of the , symbol Format #,### #,###.00 000,### #, #,, $#,###

1000 1,000 1,000.00 001,000 1

100000 100,000 100,000.00 100,000 100

$1,000

$100,000

100000000 100,000,000 100,000,000.00 100,000,000 100,000 100 $100,000,000

The , (comma) is used to add comma separation between groups of numbers in thousands. Comma separators are generally used to make it easier to read large numbers.

Use of the * symbol Format **#,###.00 *X#,### *$#,### $*X#,### *#,###

1000 *********1,000.00 XXXXX1,000 $$$$$$$$1,000 $XXXXX1,000 #######,1000

100000 ******100,000.00 XXX100,000 $$$$$100,000 $XXX100,000 #####,1000000

The * symbol is used to pad all of the unfilled space in a number or in currency fields with a character. This style is often used in printing amounts of checks so that additional digits cannot be added to the value after it has been printed. The * symbol must be followed by a character that will be used as a fill character.