COMPUTER 10 by YOUG JI INTERNATIONAL SCHOOL

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

FIRST QUARTER Module 1:

What is a Database?

A database is a collection of related data. The database stores information. This information can be manipulated and viewed in many different ways. A Database Management program such as Access stores information in tables that are related to each other. It is primarily designed for two main purposes:  

To store, add, delete, and update the data in the database. To provide various ways to view the data in the database. For example, you can display data on your screen in a form or print out a report.

With the power of Access you can maintain and manipulate large amounts of data and create reports. Can you think of some examples where a database could be used?

Some examples

are:    

Store and/or query information on applicants, training or programs. Query or extract information on donors and donations made to the college and produce reports. Store a list of students, addresses for a mail merge. Personnel database with separate but related tables for employees, departments and payroll information.

Databases allow us to organize, manipulate, analyze information and create reports.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 2:

Flat Database

Many of you have probably created a flat database which consists of only one table. Below is an example of a Word table that stores information on computer books.

What problems do you see with a single flat database?    

Unnecessary duplication of data. Wastes storage space. It is more difficult to update and maintain. It can be cumbersome to find or summarize information. Greater chance for data entry errors. The main problem with a flat table is redundancy.

  

The publishers name and phone number are repeated. If their phone number changes, how many updates would you have to make? The authors‟ name and phone number is duplicated. If an authors Phone number changes how many updates would you have to make?

To avoid these problems, you need to break up the information into separate related tables. This is where the power and versatility of a relational database such as Access should be used. In this example, you would have the following tables: Publisher‟s, Author‟s, Books, and an Intermediary table Book/Author. Intermediary tables consist of many to many relationships. For example, an author can write many books and a book can have more than 1 author. So this table could have ISBN and Author ID. An ISBN can be repeated many times depending on how many authors wrote the book. An author can be listed many times for different books. Same ISBN

2 authors (ID)

2 ISBN

1 author (ID)

0-55-123456-9 0-55-123456-9

9 10

1-1111-1111-1 1-22-233700-0

4 4

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 3:

Relational Database

Access is a relational database. In a relational database data is organized in related tables. In related tables, one or more fields are linked to fields in another table. This link ensures that you can enter only those values that have corresponding entries in the other table. For example, you canâ&#x20AC;&#x;t enter an order without a customer! A relational database can have multiple tables that contain data about various entities, such as customers, orders products. An entity is any object that has a distinct set of properties. A relationship is a connection between two or more tables based on common fields.

Linking tables together through common fields.

Tables are the building blocks of a database. From the tables you can generate forms, queries, macros, or reports. Relationships must be established between the tables before we can create queries using multiple tables. Definitions of a few key terms A table is a collection of related information that is organized in columns (fields) that describe an entity (record). In the most basic sense, an entity is a person, place, thing, or idea. A Record is a collection of information about a specific entity, such as a student, applicant, program, or project. If we have pieces of information, on one subject, we have a record. For example, if we know that Jane Doe is a programmer, with a 2002 Blue Toyota Camry, license plate number 4XN746. We now have a record on Jane Doe. Each of these pieces of information by itself (blue car) is data, but the information altogether makes a record. A Field is a category of information. A formal definition of field: a column of data sharing properties and a data type. We organize this type of information into categories or fields. In our Jane Doe example we would have fields such as name, occupation, car color, year, make and model. This would give us a total of six fields. Primary key: The fields or set of fields that uniquely identify any given row. It ensures no two rows have the same value. Nulls are not allowed. Primary keys are an important concept because they provide the only way of exactly addressing a specific record in a table. In our Jane Doe example, the license plate number could be designated as the primary key.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 4:

Database Design

Step 1:

Identify the purpose of your database.

What type of information are you trying to store and retrieve from your database? Each database should be set up for a specific purpose. For example if you‟re keeping track of donations, you need to plan and design a database that can store related data such as donors and events as well. Consider the following issues:    

What types of queries do you need to perform on the database? What types of reports will need to be produced? What forms will be needed for data entry purposes? How will the data be sorted, grouped or filtered?

Tip: Look for database models that you can follow. There are many resources (online, books, etc) from which you can garner information. Microsoft Access offers templates on your computer (local templates) and featured online templates that you can download and modify it to suit your needs.

Step 2:

Determine the tables you need.

Each table should focus on one topic and should store only the data related to that topic. For example, to track donations you may have separate tables for Donors, Donations and Events. Remember our flat database example? You should separate fields into related tables for the following reasons: 

Eliminate redundant information. For example a customer address change is much easier to

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

implement if the data is stored only in the Customers table. It is only necessary to make the change once, thereby reducing the amount of data entry required and potential for data-entry errors. It also saves storage space. 

Manage data easier. Large tables can be difficult to work with. It may be cumbersome to find or summarize information.



To make future changes to the database design easier. Maintaining separate tables makes it easier to accommodate change. You can avoid having to frequently restructure the fields to accommodate new data.



Join or relate the tables to one another to view information from multiple tables in a query.

Even if you choose a blank database, Microsoft Access offers Table Templates that you can use and modify for your own purposes. Just go to the Create tab and select Table Templates. Contacts, Tasks, Issues, Events and Assets table templates are available.

Step 3: 

Breakdown the tables into fields.

The fields in a table should relate to the subject of the table. For example, it is intuitive for a user to look in the Customers table for the address of a particular customer.

  Avoid inconsistent dependencies that can occur when you add fields to a table that are not directly related to the table. For example, it does not make sense to look for the salary of the employee who calls on that customer in the customer table. The employee's salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access.  

Consider the type of data that you plan to put in each field. Some common types are Text, Integer, Currency, and Date/Time. Store information in its smallest logical part. If you combine more than one kind of information in a field, it is difficult to search or sort the data.  For example, create separate fields for first name and last name.  Don‟t create one field to store all the address information, break it down into street, city, state, zip.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE



Do not include calculated data. In most cases you do not want to store the result of a calculation in a table. Access should only perform the calculation when you want to see the result (in a form, query or report).

Note: If you have repeating (the same) information in several tables, it is a clue that you have unnecessary fields in some tables.

Step 4:

Determine the Relationships.

Joining or relating the tables to one another will allow you to view information from multiple tables at the same time. After you define relationships between the tables, you have to flexibility to bring the data from multiple tables together in a query, form or report. Decide how each table will be related to the other tables in the database. If necessary, add fields to create relationships. To set up a relationship between two tables, add one table's primary key to the other table, so that the field appears in both tables. (Open the secondary table and add the field using the same name and data type.)* The field in the second table is called the foreign key. *Exception

If the primary key is an Auto number, the foreign key (the corresponding field) must have a data type of a Long Integer.

Step 5:

Refine the Design. Create your tables, specify relationships between the tables, enter a few records and see if the database gives you the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplications of data and eliminate them.

    

Did you forget any fields? Go back and add them. The intersection of every column and record should contain one and only one value. Did you choose a good primary key for each table? Can you search for specific records? Make sure that you won't need to enter a value in a primary key field that will result in a duplicate value. Are you repeatedly entering duplicate information in one of your tables? If so you probably need to divide the table into two tables, with a one-to-many relationship. Do you have tables with many fields, a limited number of records and many empty fields in individual records? If so think about redesigning the table so it has fewer fields and more records.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 5:

Getting Started

1. Go to Start All Programs Microsoft Office Microsoft Access 2007. 2. You can create a New Blank database, choose from a Template, or open anexisting database.  

The Blank Database choice allows you to create your own database from scratch. Local Templates reside on your computer. Microsoft Office Online also offers templates from various categories, that you can download. These templates provide the design and structure of the database, so you just enter the data.

3. Open Recent Databases displays a list of currently opened databases from which you can choose, or select “More” to navigate and select a database in the Open dialog box.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Example: Create a new database from the Events template.

Navigation Pane When a database opens, the name and format of the database appears in the title bar, and the navigation Pane opens on the left side of the Access Window. Click the Expand Navigation Pane arrow (chevron).

An empty table with sample fields opens in datasheet view.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

The navigation Pane displays the objects in your database. Objects include tables, forms, queries, and reports. You can use the Navigation pane to change which objects display and in what order. For instance, you can display just tables, or all objects as shown below.

Access Database Objects The following table describes the objects you can have in an Access database.

Tables

A Table stores a collection of related information organized in columns (fields) and rows (records).

Queries

A query is used to extract specific information from your table(s). A Query takes information from the selected tables and displays a subset of data that meets yourcriteria.

Forms

A form is a graphical representation used to enter or view data into a table.

Reports

Presents data from a query or table and places it in neat, organized and readable form.

Macro

A stored set of commands that can be used to automate database tasks.

Modules

Automates and customizes database operations using visual basic. Modules are a collection declarations, statements and procedures stored together as a unit.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 6:

Tables

When you create a new blank database or create a database based on a table, Access automatically opens in Datasheet View. When you create a blank database you need to rename the default field names as shown below.

Datasheet View The Datasheet View can be used to view, enter, edit, add, delete, sort and filter data. The field names appear at the top of each column. Each row represents one record. On the far left of your table is the vertical â&#x20AC;&#x153;record selector.â&#x20AC;? An arrow located in that area shows you which record is selected. Because we used a template, the field names already appear in our table. You need to enter the data into the records.

Field

Record Selector Records

The Record Bar allows you navigate through the data. Go to first Record

Go to last Record Create new record

Current record

Go to next Record

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 7:

Forms

A form is an Access database object that allows you to view, edit, and add data to a table. The datasheet view of a table shows you a grid of fields and rows. A form typically shows just one record at a time. A form is a graphical representation used to enter data into the tables. A form is simple way to enter data all at once rather than in multiple tables. Data input from a form will enter data directly into the respective table/tables. You can build the form from a table or a query. To create a basic form, do the following: 1. In the navigation pane, select a table on which to base the form. 2. Click on the Create tab 3. In the forms group, click Form. This will create a Columnar form that includes all the fields from the selected table.

An example is shown below. This is a registration form, which can be used to view or add attendees. Once the data is entered in the form, the data is stored in the registration table.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 8:

Queries



A query is a database object that retrieves and displays selective data from one or more tables or from other queries. You can use a query to retrieve data meeting specific conditions. For example, I can display all classes a John Doe attended in June. Query results are similar to the results of a filter, but a query can be saved permanently, whereas a filter provides only a temporary view.



When you run a query it displays the results in datasheet view. The result of the query is dynamic; hence the results are called a dynaset. The dynaset looks and acts like a table; it is a “live” view of one or more tables. WARNING: If you make changes to the data in the query, the data in the table will also change!!



A query can be based on data from one or more tables. Queries usually connect two or more tables through a relationship between a common field in both tables, such as a key field (a field unique to each table).



Queries allow you to perform calculations on the data; to create data sources for forms, reports, charts and other queries; to make changes to tables and create new tables.



Select Query - This is the most common type of query; it selects information from one or more tables and returns only the records that meet the criteria.

Plan a Query 1. Determine the tables from which you will extract the fields. 2. Determine the fields that you want to see in the query result. 3. Specific the conditions that you want the data to meet. Create a query in Design View 1. In the navigation pane, select the Queries object. 2. On the Create tab, click Query Design. 3. In the show table dialog box, select the table you want to add to the query and click Add. 4. Click close to return to design view. 5. Select the fields that you want to display in the query result. Apply any sorts or criteria to the fields. 6. Click the Run button or switch to datasheet view to see the results. 7. Open the query <qryExcel Intro Attendees>.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Below is an example of a query to display all the attendees for Intro to Excel.

Running Queries When you run the query it displays the "answer" to your query. It displays the corresponding records from the table that meet the criteria you specify.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

SECOND QUARTER Module 1:

Reports

A Report places the information you have gathered from a query or table and places it in neat, organized and readable form. You would use a report to present your database information. Below is an example of a report built in access that displays a list of people that attended an Excel Introduction Class.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Module 2:

Working in Datasheet View

Datasheet Toolbar

View button - Displays the available views for the current window. Click the button if it displays the desired view, or click the arrow next to the button, and then click the desired view. Save - Saves the layout of a datasheet; design of a table, query, view, stored procedure, SQL statement, form, report, or data access page; or structure and content of a macro. Search - Allows you to search for files on a hard disk or a network drive. Print - Prints the selected form, report, datasheet, or data access page immediately without displaying the Print dialog box. Print Preview - Displays the active object, such as a table, form, or report, as it will appear when printed. You can zoom in or out of a page or see several pages at once. Check Spelling - Checks the spelling of text entries in table, query, or form Datasheet view or selected text in a text box in Form view. Cut - Removes the selected item (for example, a control or a record) and places it on the Clipboard so you can insert it elsewhere. Copy - Copies the selected item (for example, a record) and places it on the Clipboard so you can insert it elsewhere. Paste - Inserts an item from the Clipboard into an active database object. Undo - click to undo up to 20 of the most recent actions. Hyperlink - Inserts or modifies a hyperlink address or Uniform Resource Locator (URL). In datasheet and form view, the hyperlink address field must be selected. Sort Ascending - Sorts records based on the selected column(s) in ascending order (0 to 9, A to Z). Subsequent sorts replace previous sorts. To undo a sort, choose Remove Filter/Sort on the Records menu. Sort Descending - Sorts records based on the selected column(s) in descending order (9 to 0, Z to A). Subsequent sorts replace previous sorts. To undo a sort, choose Remove Filter/Sort on the Records menu.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Filter by Selection - Filters records based on selected data. To filter by selection, select a field or part of a field in a datasheet or form, and then click Filter By Selection. Filter By Form - Displays the Filter by Form window, which is a blank version of the active form or datasheet, so you can filter data. Apply Filter/Remove Filter - Applies or removes a filter in the active datasheet or form. Find - Searches for a string, such as an employee's last name in a datasheet or form. New Record - Moves to the end of the form or datasheet so you can enter a new record. Delete record - Deletes the selected record or records from the form or datasheet. Database Window - Displays the Database window, which lists all objects in the current Microsoft Access database. For example, to create, copy, or delete a database object, or to drag an object to a group or to the current window. New Object - Automatically creates a form based on the selected table, query, view, or stored procedure. Office Assistant - The Office Assistant provides Help topics and tips to help you accomplish your tasks.

Add Records 1. Click the New Record button on the toolbar or on the navigation bar. 2. Enter your data. 3. Press your Tab key or the Enter key to move to the next field. You can also use your mouse.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Save a Record A record is automatically saved by Access when you move to another record. Access automatically saves all records when you close the table. Select a Record 1. Selecting the record (row) makes it active. An active record will have the arrow in the record selector area. This means that the record is ready for an action to be taken, such as editing or deleting data. 2. To select one record, you can point to it in the record selector area with your mouse or you can choose Edit, Select Record from the Menu Bar. 3. To select all records, from the menu bar select Edit, Select All Records.

Record Selector

Edit a Record You can edit any record except for data in the AutoNumber field. The AutoNumber type produces a unique computer-generated number, which is never repeated in that field. To illustrate this point, if you delete the third record, which has an AutoNumber of 3, the third record will then be AutoNumber 4. Even if you add records, the number 3 will never appear again in that field. Delete a Record 1. Select the record you would like to delete. 2. Click the Delete Record button on the toolbar or select Edit, Delete Record from the Menu Bar or press the Delete key on the keyboard. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

3. A dialog box will display a message to confirm the action and warn you that it cannot be undone. Deletions cannot be undone. Click OK to confirm deletion.

Check Your Spelling In Datasheet View you can check the spelling in your records. ď&#x201A;ˇ

Click the Spelling button

on the toolbar

Filter by Selection Filter y Selection, filters the records via selected data. To filter by selection, select a field or part of a field in a datasheet or form, and then click Filter By Selection. Exercise 1. Sort the list of employees by last name in ascending order then descending order.

2. Filter by selection

all employees in the Department Safety & Security.

3. Remove filter

Filter by Form Filter By Form (Records/Filter menu) displays the Filter by Form window, which is a blank version of the active form or datasheet, so you can filter data. Exercise 1. Click filter by form

button.

2. Click the drop-down arrow under the fields you want to filter, and select your criteria. In this example filter Department ITS and the building St. Josephs Hall.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

3. Click the Apply Filter

button. The results are shown below.

4. Once you set up and apply a filter you can save it as a Query. 5. Click the filter by form

button.

6. In filter by form view click the Save as Query

button.

7. In the Save As Query dialog box, type in a name. Click OK.

8. Click remove filter to get back into datasheet view. Load from Query Once the query is saved, you can go back at a later date and open the query. 1. Go back into filter by form view. Click Load from query 2. In the Applicable Filter dialog box select the filter you want to view.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

3. Click OK. 4. Click Apply Filter 5. Your data is displayed. Note: Since you saved it as a query, you can also open the filter in the query objects tab.

Module 3:

Table Design View

To open a table in design view, select the table in the database window and click Design. To switch between Design and Datasheet views, click the Table View button in the top left corner of the toolbar. The button will look different depending on which view you are in at the time. Table View buttons: Switch to Datasheet view

Switch to Design view

Design view is recommended when you create a new database. This method allows you to specify the fields in the table and the properties associated with each field. The top part of this view contains three areas: Field Name: Name the field. Although you can use spaces in the field name, it is not recommended. You can use up to 64 characters, which can include letters, numbers and spaces. They cannot contain periods, exclamation points, a back quote, brackets or ASCII control characters. Names must be unique within a table. Data Type: What type of data will be stored in the field? Will it be text, a date, or a number in which you will perform calculations on? Choose the appropriate data type from the drop down list for the type of data that will be entered into this field. The choice made in this field will affect the second (bottom) half of the form. Different data types allow different options.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Description: Use this area to define the field fully. This description will appear in the bottom comment bar of Access when the field is activated.

After a description is entered, you can press [ENTER] or [Tab] to move to the next field name. The order of the field names in design view will determine the order they will appear as column headings in your table. The order does not matter to the database, but for your own comfort you may wish to place them in a logical order. 

To change the order you can click and drag the field to a new location.

Data Types The following are a list of the data types available:  

Text: A string of up to 255 characters; however, you can limit the length to a smaller number by specifying the maximum number of characters in the “field size” property on the bottom half of the form. This data type should also be used for numbers that will not be used in calculations.



Memo: Used to store longer strings of characters. Access places a limit of 64,000 characters for data of this type.



Number: Used to store numeric data. Access offers a selection of formats for the date and time. This data type is best for numeric data that will be used in calculations.



Date/Time: Used to store dates and time. Access will let you store a specific format for displaying the date and time. General Date is the default but other formats are available using the drop-down list or creating a custom format.



Currency: Used for monetary data. This form does not round numbers.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE



AutoNumber: Places a computer-generated number in the field. This is a read-only field assigned by Access. This data type is commonly used as a key field (to be discussed) as it is unique to each row in the table.



Yes/No: Used for Boolean data, when an either/or situation is present.



OLE Object: Used for “Object Linking and Embedding” from the OLE server provided in the Windows environment. An Excel spreadsheet or Word document can be an OLE object.



Hyperlink: Insert a hyperlink address.



Lookup Wizard: Allows you to define a field where the database user can select a value from another table or from a predefined list.

Field Properties The field properties are located at the bottom of the Design View form. Depending upon your choice of data type, different properties will appear. You can set field properties to define the characteristics of information in your database.

If you click on the property in the lower pane, a description at the right will appear

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Field Size The Field Size property determines the maximum number of characters or the range of values that can be stored in a field. The size is important, as the database will set aside that much space regardless of whether the space is used or not. This property can be set for Text, Number and AutoNumber fields. For Text Fields, the field size determines the number of characters you can store in a field. The default size for a text field is 50 characters. Field Size Continued For Number Fields, the field size determines the range of values you can store in a field and whether the field can contain fractional values. Number fields allow the following field sizes: No fractions, no decimals, no negative numbers, data is rounded -/+32786 no fractions, data is rounded -/+2147483648 no fractions, data is rounded -/+ 3.4x1038 up to seven decimal places

Byte

0 TO 255

Integer Long Integer Single Double field size Replication ID identifier

-/+ 1.797x10308

up to fifteen decimal places, the largest

Globally unique

no decimal places available

Format The Format property controls how data is displayed in tables and reports. There are predefined settings or you can design your own. This field does not affect how the data is stored. There are the following choices: General

Displays a number the way it was entered.

Currency

Uses a comma and the currency symbol. Negative numbers are displayed in parenthesis and the default number of decimal places is two.

Fixed

Displays at least one digit. Rounds to the set number of decimal places.

Standard

Uses a comma as a thousands separator and defaults to two decimal places.

Percent

Multiplies the number by 100 and uses a percent ( % ) symbol. Defaults to two decimal places.

Scientific

Uses exponents of 10 for standard scientific notation.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Decimal Places The Decimal Places property displays for field with numeric data types. This field defines the number of decimal places to be stored in that field. If you choose AUTO, the Format property will control this option. Caption This property defines the label that will be placed on the datasheet view of the table, queries, reports or forms when that field is used. If this field is left blank, the fieldâ&#x20AC;&#x;s name will be used. Default Value A default value automatically appears in the field when a new record is entered. This value may be an expression (a calculation), which can be created activating the Expression Builder. The Expression builder allows you to choose fields and create formulas. To activate the expression builder, just click in the default value property in the lower pane then click the expression builder . For example, if you type Now () in this area, the current date and time will appear in that field by default. Validation Rule Allows you to specify what data can be entered in the field. The Validation Rule is an expression that tests the data that is entered into the field. For example: >1000. The value entered must be greater than 1000. Validation Text This text appears in a message box when an invalid value is entered into the field. Require User Input Set this property to say yes if it is required that data is entered into this field. This way the user will not be able to leave this field blank. It is advisable that you do not set this property until you are finished testing your database. Indexed By creating an index on a field, it speeds up sorting and searching on that field. However, you only want to create Indexes on fields that will be sorted by or searched often because it can slow down data entry. Types:

Yes (Duplicates OK) - will allow duplicate values in the field. Yes (No Duplicates) - cannot have duplicate values in the field.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Design View Toolbar

View the datasheet Save the table design Search Set Primary Key Define Indexes Insert Row(s) - Creates a blank row above the selected row Delete Row(s) - Deletes selected row(s) Properties - Displays the property sheet for the selected item, such as a table field or form control. Displays the active object's property sheet if nothing is selected Build button - Displays a builder for the selected item or property. Microsoft Access enables this button only if a builder is available for a selected item (such as a field name in table Design view) or a property (such as an input mask property or property set with an expression). Displays the main database window Create new database object Office Assistant

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Primary Key

Every table should have a primary key. A primary key is a field that uniquely identifies each record in a table. Data that is entered into primary key fields are automatically indexed. Data is retrieved faster from indexed fields, which means that queries and reports run faster and we can find records in our database quickly. An example of a primary key would be a social security number, phone number or license plate number. An AutoNumber (which is computer generated) is often used as a primary key to uniquely identify a row in that table. The use of the word “ID” for identifier is usually used at the end of the key field name, like Publisher ID. If you do not set a primary key before closing the table, Access will display a message and will automatically select a field for you. (Note: If a field has “ID” in its name, that field will be selected.) To set a primary key: 1. Select the field you want to designate as the primary key. 2. Click the primary key button

on the toolbar.

A key symbol will appear by that field.

The Index property of the field will change to “Yes (No Duplicates).”

Foreign Key A foreign key is a field that corresponds to a primary key in another table. The identification of a foreign key allows you to link two tables together forming a relationship that allows you to display information from both tables using queries, forms or reports. The data type for the primary key should match the data type of the foreign key in the other table. The exception to this is when using YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

an AutoNumber. If the foreign key (the corresponding field) must have a data type of a Long Integer. Module 4: Create a Database We have examined the two views (design view and datasheet view) that exist in every table. Now it is time to create our own database and tables. Exercise - Create a new blank database. See handout or follow your instructorâ&#x20AC;&#x;s example. Example - Create a new blank database named <books> that stores information on books, authors and publishers. How can we store the flat table information into a relational database? We will start by creating the Authorâ&#x20AC;&#x;s Table.

Create the Authors Table 1. Choose the Tables tab in the database window. Click New.

2. When you choose New you have five choices summarized below. Datasheet View: Creates a table based on data you enter into the table. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Design View: Creates a table where fields and field properties are identified first (Recommended). Table Wizard: Guides you through built-in templates to create a table. Import Table: Runs a wizard that imports a table to your database from an external source such as Excel, another database, or a delimited text file. Link Table: Runs a wizard that adds a link to a table in another database. 3. Choose Design View. 4. Create a table with the field names and properties shown below. 5. Designate the primary key to AuthorID. ď&#x201A;ˇ

In the top pane, select the AuthorID field. Click the primary key button on the toolbar. Field Name

Data Type

AuthorID

AutoNumber

AuFName

Text

AuLName

Text

AuPhone

Text

6. Create an Input mask for the authorâ&#x20AC;&#x;s phone. An input mask provides a pattern to make data entry easier for the user. 7. In the top pane, select the AuPhone field. 8. In the lower pane click the build

button for the Input Mask.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Input Mask Wizard Step 1:

Select a mask that matches how you want your data to appear. 

Select Phone Number. Click Next.

Step 2:

Select the placeholder character you want. Click Next.

Step 3:

Choose to store the data with the symbols or without the symbols. 

Step 4:

Select “with the symbols in the mask”.

Click Finish.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

In the Input Mask field properties, Access inserts coding for an input mask that will produce the pattern you selected.

Back in datasheet view when data entry is performed, the input mask will appear when you begin to type a phone number into the AuPhone field, as shown below.

Saving a Table 1. Click on the Save

button or choose File  Save.

2. Give the table a meaningful name. All Access tables should begin with the three letter prefix “tbl” and then a descriptive name.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Create the Publishers Table 1. Create the Publishers table with the field names and properties shown below. 2. The two attributes publisher name and phone number together can uniquely identify the publisher. Nevertheless, for convenience, designate the primary key to PublisherID. Field Name

Data Type

PublisherID

AutoNumber

PubName

Text

PubPhone

Text

3. Create an Input mask for the field named PubPhone.

Create the Books Table 1. Create the Books table with the field names and properties shown below. 2. Designate the primary key to ISBN. This is a foreign key. It creates a link back to the primary key in the Publishers table which is an Auto Number so this needs to be Number data type

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Field Name

Data Type

ISBN

Text

Title

Text

PubID

Number

Price

Currency 32

3. For the Price field designate the data type as Currency. Change the properties for that field to currency and 2 decimal points.

4. For the PubID field, create a lookup to the Publisher‟s table. 5. Follow the directions on the next page for using the Lookup Wizard to create a lookup field. Creating a Lookup Wizard Field A Lookup Wizard field let the user select a value from a list of possible values. For the Publisher ID field, the user will be able to select the correct ID from the Publisher‟s table, rather than having to remember the correct ID or refer back to the table. This makes entering the ID‟s easier for the user and guarantees that the ID is valid. 1. For the PubID field, choose the data type Lookup Wizard from the drop-down list. 2. Choose I want the lookup column to look up the values in a table or query. Click Next.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

3. Select the table (Publishers table) that provides the values for your Lookup column.

4. Select the fields that contain the values you want included in your lookup column (PublishersID, PubName). For example, you may want the Publishers ID because that is the primary key for this table and the publisher‟s name. You don‟t need the phone number.

5. Adjust the width of your lookup column. Choose whether you want to hide the key column.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

6. Type a label name (or accept the default name given) for the lookup column and click Finish.

Module 5:

Multiple-value problems

How do we specify the author of a book? What if we have more than one author per book? Authors can write more than one book and a book can have more than one author. What a quandary? How do we represent this in out database? If we add the AuthorID to the books table as shown in below, each book that is written by two or more authors must be represented by two or more rows of repeated data. Look at the example titled Balloon below.

If we add the ISBN key to the authors table, what do we do when an author writes more than one book?

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Create the Books /Author Table This scenario represents a many-to-many relationship. Authors write many books, books are written by many authors. At first we may think we can just add more than one author‟s name or author ID into the field as shown below, but this would not be correct. To include all the author‟s names or ID in one cell it becomes more difficult to search the database for a single author or sort sort the authors in the table? Remember the rule.  

The intersection of every column and record should contain one and only one value.

Second we might think of adding more fields, or a multiple columns to accommodate each author. 

The multiple-column approach presents the problem of guessing how many author columns we will ever need, and creates a lot of wasted space (empty fields) for books with only one author.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Intermediary Tables The proper approach to implementing a many to many relationship is to create another table called a junction or intermediary table, in order to break the relationship into two one-to-many relationships.

In this case you would need to create another table to store multiple instances of the (ISBN), once for each author. Below is an example of a Book and Author table where there is more than one author per book or ISBN. 1. Create the Book/Author table with the field names and properties shown below. Field Name

Data Type

ISBN

Text

AuID

Number

2. For ISBN number 0-321-321321 there are 3 authors. The table below shows how you would enter all 3 authors for that particular ISBN.

Types of Relationships One-to-One Relationship This type of relationship is very rare. If you find you have a one-to-one relationship, you should reevaluate the database design. The tables can most likely be combined into one table. One-to-Many relationship A record in Table A has one or more related records in Table B. â&#x20AC;˘

For example a customer places many orders.

â&#x20AC;˘

A publisher publishes many books.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Many-to-Many relationship A record in Table A has one or more matching records in Table B, and a record in Table B can be related to more than one record in Table A. Example:

A many-to-many relationship is possible between an author‟s table and a books table. Each author could have several books and each book could have several authors.

A many-to-many relationship is often a symptom that the two tables are not directly related, but instead are related through some additional table.

Module 6:

Create Relationships

Creating relationships allows you to combine data together from multiple tables. 1. To set up the relationship, open the relationship window by going to Tools → Relationships or click on the relationships

button on the toolbar.

2. Below is an example of the relationships window where relationships have already been created. To make changes to the relationship, double-click the relationship link line.

3. Click the Show Table button to display the tables or on the menu bar select Relationships → Show table. 4. Double-click the tables or queries you want to add to the Relationships layout. Close the show table dialog box. 5. Create the relationship by selecting the primary key field in one table and dragging it to the matching foreign key field in another table. The primary key will be bolded in the table. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

6. The Edit Relationships box will appear. Click the Create button to create the relationship.

Referential Integrity In the edit relationships dialog box, you can choose Enforce Referential Integrity. Referential Integrity ensures that the records in related tables are consistent with one another. When you choose Enforce Referential Integrity, certain rules are implemented. 

When you add a record to a related table, a matching record must already exist in the primary table, thereby preventing the possibility of orphaned records.



If you attempt to change the value of the primary key in the primary table, Access prevents this change if matching records existing in a related table.  However, if you choose the cascade updates option, access permits the change in value to the primary key and change the appropriate foreign key values in the related table, thereby eliminating the possibility of inconsistent data.



When you delete a record in the primary table, Access prevents the deletion if matching records exist in a related table.  However, if you choose the cascade deletes option, Access deletes the record in the primary table and also deletes all records in related tables that have matching foreign key values.

If you want to enforce referential integrity between the two tables, check the box. If you choose to enforce referential integrity, you will need to tell Access how to handle Edits and Deletions in the primary table.

Select if you want changes in one table to automatically carry over to the related table,

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Select if you want deletions in one table to automatically carry over to the related table,

Note:

In order to define referential integrity between tables, the matching field from the primary table must be a primary key or have a unique index, the related fields must have the same data type and both tables must be stored in the same Access database.

Modify Table

Changing a Field Name To change a field name, go to design view and type in a new name. But, before you do this check to see if that field is referenced in any calculations, expressions or validation rules. If so, you will have to change the field name in every reference. As an alternative, you can leave the field name as is but enter the name you wish to use in the Caption property. Captions display in the status bar when the field is activated on forms and reports. Changing a Field’s Properties You can change the properties of a field at the bottom of the Design View form. If the modification affects the data already entered into the database, Access will display a message to that effect. Adding a Field To add a new field to the table, select the row where you want the new field to appear, and do one of the following:   

Select Insert, Field from the menu bar. Press the Insert key on the keyboard. Click the Insert Field button on the toolbar.

Deleting a Field To delete, select the row and do one of the following:   

Select Edit, Delete or Edit, Delete Row from the menu bar. Choose Yes on the dialog box to permanently delete the field. Press the Delete key on the keyboard. Click the Delete Field button on the toolbar.

Moving a Field You can move fields in both Datasheet and Design views. To move a field in Design View: YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

1. 2. 3. 4.

Click in the selector bar at the field you wish to move. Point at the selected field. A white arrow appears. Hold the mouse button down and drag the field to its new location. Release the mouse.

Note: You can undo this move immediately after it is performed by going to the Edit menu and selecting Undo. To move a field in Datasheet View: 1. 2. 3. 4.

Point to the field name of the column you wish to move. A black down arrow appears. Click once to select the column and release the mouse. Hold the mouse button down and drag the column to its new location. Release the mouse.

This change only appears in Datasheet View. If you choose to save the changes upon closing the table, the change will appear in Design View as well.

Module 7:

Modifying Database Objects

Renaming a Table In the database window, do one of the following:   

Double-click on the table‟s name to activate the “edit” mode. Type the new name and press Enter. Select the table and choose Edit, Rename from the Menu Bar Right-click the table‟s name and select Rename from the context menu.

CAUTION: Do not rename a table if it is already being used in any queries, forms and/or reports in the database. Exercise 1. Right click on “Books” and choose Rename. 2. Rename it <tblBooks>. 3. Click off the name, and you have renamed your table!

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Copying a Table To copy a table, do one of the following from the database window: 

 

Right-click the table‟s name and choose Copy from the context menu; then, rightclick a blank area in the window and choose Paste. A box appears asking whether you want to copy the structure, the data or both. Make your selection and click OK. Hold the left mouse button down and drag the table‟s name to a blank space below the list of tables; release the mouse. This method copies the structure and data of the table.

Exercise Copy the table <tblBooks> and name it <tblBooksStructure>. 1. Right click on <tblBooks>. 2. Choose Copy.

3. Right click in a blank area of the database window. 4. Choose Paste.

In the Paste Table As dialog box, type in the new table name tblBooksStructure.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

5. Under the Paste Options, choose Structure Only. 6. Now you have a copy of the tblBooks table structure. 7. When you open the table there will be no records since you copied the structure only. This can be a useful time-saver because you don't have to recreate the table and fields.

Deleting a Table In the database window, select the table and do one of the following:   

Choose Edit, Delete from the menu bar. Press the Delete key on the keyboard. Right-click the table and choose Delete from the context menu.

Access will warn you prompt you with a warning message before the table is actually deleted. Click Yes, to delete the table. Once you confirm the deletion, it cannot be undone.

Printing the Table There are two ways to print all the data in your table. One is to choose the Print button on the toolbar in Datasheet View. The other is to choose the table in the Database Window and click the Print button from the toolbar. To change the page setup, choose File, Page Setup in Datasheet View and make your changes. Printing Selected Records To print selected records, rather than the whole table: 1. Select the group of (adjacent) records you want to print. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

2. Select File, Print from the Menu Bar. 3. Choose Selected Records in the dialog box. 4. Choose OK.

Module 8:

Queries

What is a Query? A query is a question that you ask about data. Queries help you to obtain specific information about the data. A Query takes information from the selected tables and displays a subset of data that meets your criteria. Learning how to create queries is the key to unlocking the data in your database!!   The result of the query is temporary or dynamic; hence the results are called a dynaset. The dynaset looks and acts like a table; it is a “live” view of one or more tables. WARNING: If you make changes to the data in the query, the data in the table will also change!! 

A query can be based on data from one or more tables. Queries usually connect two or more tables through a relationship between a common field that exists in both tables, such as a key field (a field unique to each table).



Queries allow you to perform calculations on the data; to create data sources for forms, reports, charts and other queries; to make changes to tables and create new tables.



Select Query - This is the most common type of query; it selects information from one or more tables and returns only the records that meet the criteria.

Module 9:

The Simple Query Wizard

This wizard will help you build the background of the query. It assists with the selection of the tables and fields to be included. You can also create simple calculations. If you want to create a more complicated query, you will need to work in the query design window directly. 1. In the Database Window, click on the Query tab and click Create query by using wizard.

2. Choose the table and fields to include in your query.  In this example choose the tblBooks table and add all of the fields, as shown below. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Select the table from the drop down list. To select a field to be included in the query, double-click on the field name or use the arrow buttons.

3. After selecting the fields for your query, click Next. 4. If you have any numerical data in your table you will be given the option to display your data in detail or summary. 5. Choose Detail. Click Next.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

6. Give your query a meaningful name using naming conventions.

All queries should begin with the prefix “qry”.

If you choose Modify the query design, it will take you to the query design window.

7. Choose Open the query to view information. (This view will show you the results of the query in a dynaset.) 8. Click on Finish to view the results. Running Queries When you run the query it displays the "answer" to your query. It shows the corresponding records from the table that meets the criteria you specified. The results are shown below.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Switch between Views There are three different views for a query: design view, datasheet view, and SQL view. 

Design view is where you create the query.



Datasheet view is where you can view the results of the query. We saw the datasheet view when we created the query with the Simple Query Wizard.



SQL (Structured Query Language) view shows the user the actual programming language used to create the query. 

Go to the View Menu and select Design, Datasheet or SQL view. Or



Click on the arrow for View button

and select the view.

Query Design View (Query-By-Example grid) Below is the Applicant personal query in design view.

Table Pane: Displays all the tables that make up this query. Each column represents a field.

QBE Pane: shows all fields and criteria used in the query.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Field

Name of a field or expression.

Table

Name of the table that the field is from (this is automatically filled in when the field is chosen).

Sort

Is the data going to be sorted in ascending or descending order.

Show

If you want the data to appear in the query.

Criteria

Use to filter data.

Total

Select an option from the list of predefined calculations (To view this option, go to View ď&#x192; Totals)

Module 18: Creating a Query in Design View The simple query wizard took care of the simple steps in the design of the query for you. If you want to get into the thick of designing a query, you need to go into the query design view. 1. In the Database Window, click on the Query tab and click Create query in Design View.

2. The Show Table dialog box appears.

3. Select the tables or queries you want to use for the query and click Add. In our example select the <tblAuthors> table and click Add. 4. Close the dialog box. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Adding a Field There are three methods for adding fields to the QBE pane:   

Double click on the field name in the table pane. Click in the Field box in the QBE grid and choose the field name from the drop down list. Click and drag the field from the table pane into the QBE grid.

5. Add all of the fields or the Authors table to the QBE grid as shown below.

Sort • Click in the Sort field and choose either Ascending or Descending. • If you want to do multiple sorts, Access will sort the field on the left first and so on. Show •

You may want to include a particular field in your query for sorting or criteria purposes, but you may not want to show the data. In the Design view, remove the check mark in the Show row. Will not Show

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Show

Running Queries 1. Click the Run is shown below.

button or the datasheet view

button, to view the results. A snapshot

Save the Query Once you created the query you can save it for future use. You will be prompted to save upon exiting.

Type in a meaningful name for the query and click OK. Your query has been saved!

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

THIRD QUARTER

Queries

A query is a basic tool that Access provides for retrieving information from your database. It allows you to take information from single or multiple tables to obtain the data you want. Each query functions like a question that can be asked immediately or saved to be asked later. Although this question will generally remain unchanged (unless you choose to edit the query), the altering nature of your database means that the answer Access provides will generally be different each time the query is run. This section covers advanced topics, including using queries to sort and modify data.

Types of Queries You can use queries to view, change, and analyze data in different ways. In addition, you can use them as a source of records for forms, reports, and data access pages. There are several types of queries in Microsoft Access.

Select Queries The most common type of query, select queries retrieve data from one or more tables (using criteria you specify) and display the results in a datasheet. Select queries may be used to group records as well as perform calculations including sums, counts and averages.

Parameter Queries When run, a parameter query displays its own dialogue box prompting you for information, such as criteria for retrieving records or a value you wish to insert in a field.

Crosstab Queries You can use crosstab queries to calculate and restructure data for easier analysis. Use a crosstab query to calculate a sum, average, count, or other type of total for data that is grouped by two types of information.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Note: The select query only groups totals vertically by employee and category, resulting in more records and making comparisons between totals more difficult. A crosstab query displays the same information, but groups both vertically and horizontally so the datasheet is more compact and easier to analyze. Action Queries An action query uses just one operation to makes changes to, or moves many records. There are four types of action queries: Delete, Update, Append, and Make-Table Queries. Creating a Query in Design View Most of the queries described may be created using a query wizard. However, design view is useful for creating more complex queries and for revising existing queries.

labelled Query Design.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Click on the name of the table you wish to query. Click on the button labelled Add.

Repeat these steps until you have selected all tables. The tables you selected will appear at the top of the query window.

Adding Fields to Your Query Double-click on the name of the field you wish to add to your query. The name of the field will appear in the next available column.

Repeat the above steps until you have added all desired fields for your query. Locate the Results area of the Design ribbon. Click on the button labelled Run. The results of your query will appear in datasheet view.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Creating a Parameter Query Parameter queries are interactive filters that allow you to specify a different filter criterion every time you open up the query. You can design the query to prompt you for more than one piece of information; for example, you can design the query to prompt you for two dates. Access will then retrieve all records that fall between those two dates. Open up an existing query in Design view. Decide which field you wish to use in your filter. In the design grid, click in the field’s Criteria cell.  

If your parameter query requires an operator, type that operator in the Criteria cell (For a complete description of operators, please see page 10 in this guide). Complete your filter expression by inserting a parameter label in square brackets. Every time you run your query, you will be asked to provide this parameter.

Continue to add criteria in the appropriate fields to complete your query. Right-click on the query’s tab at the top and click Save. From the Design ribbon, go to the Results area and click on the Run button to test your query.

The Enter Parameter Value window will appear for each criterion that has to be specified.

Enter the numeric value you wish to use in your filter. Click on the button labelled OK. Access will run the query and retrieve your data from the database. The following chart displays the results for a filter designed to retrieve employees with a salary less than or equal to $60K.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Guide to Parameters To Enter. . .

Type. . .

Which is â&#x20AC;Ś

A Parameter Value

The Value

A value specified by the user

The Default Value

A value that is automatically entered in a field when a new record is added

A Null Value

<NULL>

A value you can enter in a field to indicate missing or unknown data

A Blank String

Leave blank

Indicates that no known value exists for a field

Tip: Parameter queries are helpful when used as the basis for forms, reports and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print out the report, Access displays a dialogue box asking for the month that you want the report to cover. Once you enter the desired month, Access will print the appropriate report.

Creating a Crosstab Query Open up an existing query in Design view. From the Design ribbon, go to the Query Type area and click on the Crosstab button.

A Crosstab row will appear in the design grid.

Crosstab Row For the field(s) whose values you want to appear as row headings: o Click on the down-facing arrow next to the box labelled Crosstab. o From the drop-down menu that appears, select Row Heading.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

o Repeat these steps for each field you desire to use as a row heading in the query. o You can also choose to make a field a Column Heading or a Value in the same manner. Note: You MUST have at least one of each (Row Heading, Column Heading, and Value) to run a Crosstab Query. Tip: You can select Column Heading for one field only, and as with Row headings, you must leave Group By in the row labeled Total for this field. Customizing Crosstab query column headings By default, column headings are sorted in alphabetic or numeric order. In certain cases, you may wish to change the order or limit the column headings in a crosstab query. For example, in column headings containing the months of the year, you can display the months chronologically rather than alphabetically. You can also limit the columns to only January through June. To edit crosstab query headings: Open up the crosstab query in Design view From the Design ribbon, go to the Show/Hide area and click on the Property Sheet button.

The Property Sheet pane will appear to the right.

In the box labeled Column Headings, type the column headings you wish to display. o Enter the column headings in the order in which you want them to be displayed. o Place a comma between each column heading. Tip: Column headings must be entered exactly as they appear in the query datasheet. For example, if a column heading in the datasheet is "USA," you must enter a column heading of "USA"â&#x20AC;&#x201D; not "US." After you click on the key labelled ENTER or move the cursor to a different location, Microsoft Access places quotation marks around each heading.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Click on the View or Run buttons to view the query's results.

Creating Action Queries Queries may also be used to change, retrieve, and display data. These action queries perform specific operations and can be an efficient tool for making modifications to bulk data. There are four types of action queries: Type Delete Update Append Make Table

Description Deletes a group of records from one or more tables Makes global changes to a group of records in one or more existing tables Adds a group of records from one or more tables to the end of another table(s) Creates a new table from all or part of the data in other tables

Before creating an action query, it is good practice to run a select query to determine how the action query will affect your records and the appropriate criteria to create the desired result. Delete Queries Create a Select Query in Design View to determine the records what will be deleted. Modify the query by adding the appropriate data source(s) and criteria. In this example, a company wishes to cut costs at the lowest payroll levels and accordingly removes employees making less than 30,000.

Click on the Datasheet View button to bring up a list of the retrieved records.

10 YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

The Select Query window will appear, displaying fields that will be altered by the action query. Verify that these records should be deleted.

Click on the Design View button to return to Query Design View.

If necessary, further modify the query so the proper fields will be deleted. From the Design ribbon, go to the Query Type area and click on the Delete button.

The “Criteria:” row will now say “Delete:”

Click on the Run button. A Microsoft Office Access window will appear, warning that fields from the table will be deleted. Click on the button labelled Yes.

The specified records will be deleted from your table. Open your table to verify that the proper records were deleted. Click on the Save button if you are satisfied with the action query results. Tip: Note that delete queries always delete entire records, not just selected fields within records.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Update Queries Create a Select Query in Design View to determine the records that will be updated. Modify the query by adding the appropriate data source(s) and criteria. In this example, a company will be raising its lowest salaries from 10,000 to 15,000.

Click on the Datasheet View button to bring up a list of the retrieved records.

If necessary, modify the query further so that the proper fields will be updated. Once satisfied, locate the Query Type area of the Design ribbon and click the Update button.

The Update Query window will appear. In the field labelled Update To, type in the value you wish to change the altered fields to. In this example, the minimum salary of 10,000 is being changed to 15,000.

A Microsoft Office Access window will appear, warning that fields from the table will be updated. Click on the button labelled Yes.

Click on the Save button if you are satisfied with the action query results.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Make-Table Queries A Make-Table Query is slightly different from the other action queries. Instead of modifying data within an existing table, a Make-Table Query creates a new table from the query results. Create a Select Query in Design View to determine the records that will be used to create a new table. Modify the query by adding the appropriate data source(s) and criteria. In this example, our query will create a new table detailing all male employees.

Click on the Datasheet View button to bring up a list of the retrieved records.

Return to Design View. If necessary, modify the query further so the proper fields will be included in the new table. Once satisfied, locate the Query Type area on the Design ribbon and click on the Make Table button. The Make Table window will appear. In the box labelled Table Name, type the name you wish to give to the new table. Click on the option button to select whether the new table will be created in the Current Database or Another Database. Click on the button labelled OK.

Click on the Run button. A Microsoft Office Access window will appear, warning that a new table will be created using the selected records. Click on the button labelled Yes.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Note that a new table has been created with the desired name. Open the new table to verify the make-table query worked correctly. Click on the Save button if you are satisfied with the make-table query results. Tip: Make-table queries are helpful for creating a table to export to other Access databases, or to a history table that contains old records.

Append Queries In this example, a company hires a new batch of employees. The new employee list is sent in a separate table, entitled â&#x20AC;&#x153;NewEmployeesâ&#x20AC;?. The company wishes to add these to their master list of employees. To do so, we create an append query with these new records. Create a Select Query in Design View to determine the records that will be appended. Modify the query by adding the appropriate data source(s) and criteria.

Click on the Datasheet View button to verify the list of retrieved records. Click on the Design View button and if necessary, modify the query further so the proper fields will be appended. Once satisfied, locate the Query Type area of the Design ribbon and click the Append button.

The Append window will appear. Click on the downward-facing arrow next to the box labelled Table Name. From the drop-down menu that appears, select the table to which you wish to add the retrieved fields. Click on the option button to select whether the new table will be created in the Current Database or in Another Database. Click on the button labelled OK. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

The “Criteria” field has now been replaced by “Append to”.

Click on the Run button. A Microsoft Office Access window will appear, warning that the retrieved fields will be appended. Click on the button labelled Yes.

Open the table to which you append the new records to verify your append query worked. Note that the retrieved fields have been added at the end of the table and that the values are appended only in matching fields. Click on the Save button if you are satisfied with the append query results. Tip: To run an action query, use the Run button rather than the View button. Using View will bring up a list of all records that will be updated, but not display their new values. Using Run will update the values.

Sorting Data Add criteria to a query Incorporate comparison operators and conditional operators to access more information. Comparison Operators Including comparison operators in an expression allows one data value to be compared against another. Operator = < <= > >= <> Between _ And _ Is Null

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Value Equals Less than Less than or equal to Greater than Greater than or equal to Not equal to Within a Range Null values

Example = “11/29/1961” < “K” <= 2500 > “K” >= 2500 <> “Illinois” Between 2 and 9 Is Null

Conditional Operators Conditional operators, also referred to as logic operators, add the ability to evaluate the truth of an expression, thus increasing flexibility and power Operator AND OR NOT

Description True if both conditions are true True if either condition is true True if the single instance is not true

Example >5 AND <>100 <5 OR >500 NOT Between 2 And 5

Adding a Calculated Field to a Query Calculated controls allow you to make a mathematical calculation or join multiple strings of text when you run your query. Numeric calculations To create a new calculated control: Click in an empty Field cell in Query Design View. Type the name of the newly-calculated result, a colon, and the formula you wish to calculate. Refer to any fields in your calculation by entering their names surrounded by square brackets. You can use the following mathematical operators: Operator * / + -

Description Multiplication Division Addition Subtraction

For example, to calculate a 5% bonus given to each employee, type: o Bonus: [Salary]* .05

Save your query. Use the View button to check the results and troubleshoot your calculation as necessary. Concatenation To create a new calculated control: Click in an empty Field cell in Query Design View. Type the name of the newly-calculated result, a colon, and the formula you wish to calculate. Refer to any fields in your calculation by entering their names surrounded by square brackets. Incorporate any text that will not change (including spaces) inside quotation marks. Use an ampersand character (&) to join any two strings of text in your expression (one fields with another, the contents of a field with text in quotations, etc.) In this example, type the following to concatenate the First Name and Last Name fields. (Note that space within the quotation marks.) o FullName:[EmployeeFirstName]&â&#x20AC;&#x153; â&#x20AC;?&[EmployeeLastName]

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Save your query. Use the View button to check the results and troubleshoot your calculation as necessary. Sort query results Access allows you to sort data retrieved by a query so it will be displayed in a logical order. Open up an existing query in Design view. Locate the field you wish to use to sort your data. Click on the down-facing arrow next to the Sort cell. From the list that appears, select the sort order you wish to apply to your query. In this example, we can sort the Salary field in descending order.

o An ascending sort order will sort data from the smallest to the largest value in the sort field. o A descending sort order will sort data from the largest to the smallest value in the sort field. Click on the Save button. Click on the View button to switch from Design view to View mode and see the results of your query.

Customizing Reports Reports are printable summaries of the information in your Access tables and queries. They may be created by using a wizard; however, Design View provides the user the ability to enhance the appearance of reports beyond the options provided through the reports wizard.

Adding Subtotals to Your Report A sales report can include a lot of numbers. That is why you will need to calculate sums at the appropriate locations in a report. Open up your report in Design View.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

To insert a subtotal, click on the field you are subtotaling (in this case, Units Ordered). From the Design ribbon, go to the Grouping & Totals area and click on the Totals button. From the resulting menu, click on Sum to insert a subtotal of the selected field. Subtotals will be inserted at each grouping (in the grouping footer) and as a total sum (in the Report Footer).

Grouping subtotal

Report subtotal

Inserting a grouping A group header is used to place information, such as a group name or group total, at the beginning of a group of records. Likewise, a group footer places such information at the end of a group of records on a report. Both may be used as a location for subtotals. To add a group header or footer on a report: From the Design ribbon, go to the Grouping & Totals area and click on the Group & Sort button. The Group, Sort, and Total pane will appear on the bottom of the screen. Click on â&#x20AC;&#x153;Add a groupâ&#x20AC;? to add a grouping.

A list of fields will pop up. Select the field you wish to use as a grouping.

Access will create a grouping by this field. Use the method described above to add subtotals to your grouping. To see how your report is affected, click on the Report View button, located in the Views area of the Design ribbon.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

The Report window will appear.

Note that Access automatically puts in the lines for grouping and report subtotals. If you wish to add labels (“Client Total”, “Grand Total”, etc.): o Open the report in Design View. o From the Design ribbon, go to the Controls area and click on the Label button. o Using the label cursor, draw a label box next to the desired subtotal.

Changing the Appearance of Reports Design Sections Detail Section: This section, by default, is included in every form you create and is the only section seen when Design View is first opened. Once your report is complete, the Detail section will include the bulk of data from your database, including group headers and footers. Header and Footer Sections: In addition to data in your Detail section, you may wish to add header and footer sections to your form. o Headers will often contain titles & logo graphics. o Footers will often contain instructions for using the form, modification dates, and the designer’s name. To summarize, there are three types of headers and footers that can appear on a report: Header/Footer Report Page Group

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Description Appear at the beginning and end of the report Appear at the top and bottom of each page Included in the Detail section

To show/hide a page/report footer and header: o From the Arrange ribbon, go to the Show/Hide area. o Use the buttons to the right to toggle on or off the desired footer and header. Turn report header & footer on/off Turn page header & footer on/off Resize a section of the report If the report materials you wish to insert do not fit comfortably into a section, you can easily resize any section of your report. Place your cursor at the bottom of the section you wish to resize so that the double-arrow cursor is displayed. Drag the cursor upward or downward to resize the section. Create a report title To give your report a title or add any other additional passages of text, you must create a label box: From the Design ribbon, go to the Controls area and click on the Label button. Draw an area with the cursor in the Report Header section by clicking and dragging. You can always adjust the size of the box after your text has been typed.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Moving Text Boxes and Labels Move a Text Box and its Label Together Click on either the label or the text box. Notice that the perimeter of the label remains silver while its text box is highlighted in orange. Click anywhere on the perimeter (notice the directional cursor) and drag.

Move a Text Box or a Label Separately Generally, Access forces you to move text boxes and their corresponding labels together. However, you may want to move a label or text box without moving its partner. Place the cursor over the upper left-hand corner of the field or label (i.e. on the brown square). The cursor will change into the directional cursor. Drag the field or label to a new location.

Tip: Moving Report Elements Between Sections You can move both text boxes and labels between sections using the open hand cursor to drag the report elements across the section boundary. To move an individual text box or label to another section (for example, you wished to move a label into the report header section): o Select the report element you wish to move and cut it from its present location. o Click in the section where you want to move this label or text box, and paste it into the section. o Drag the element from the sectionâ&#x20AC;&#x2122;s top left corner (using the pointing finger cursor) into its new position. Tip: Aligning Report Elements Although you can utilize Design Viewâ&#x20AC;&#x2122;s grid to align your report elements relative to each other, Access can also align those elements for you. Select the report elements you wish to align by simultaneously holding down the Shift key and selecting each element. Right click on one of the elements. From the menu that appears select the Align submenu. From the Align submenu, select the alignment type you prefer.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Resizing Text Boxes and Labels Sometimes, the font size or style you choose will obscure text beyond the edges of your text or label box. In this situation, you must resize the control. Select the text box or label you wish to resize. Drag one of its orange square handles to resize your text box.

Saving your Report Be sure to save your report frequently! Right-click on the tab of your report and click Save. Give your report a descriptive title. A good title will look like this: rpt_clientinfo. o This title follows a standard naming convention (which holds that each report’s name should be preceded by the “rpt_” prefix to make every report easy to identify). o This convention allows for differentiation between different types of objects with the same name (“tbl_, qry_, etc.). o The report’s name also plainly describes the data that the report will display once it is opened.

Reformatting Text Styles Changing Text Styles To change the text of either a label or a text box: Click on the control you wish to change. From the Design ribbon, go to the Font area.

Use the options of the Font area to change the text style of your control.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Tip: Resize a field or label after applying text formatting if not all of the fieldâ&#x20AC;&#x2122;s data or labelâ&#x20AC;&#x2122;s text can be displayed.

Adding and Formatting Borders Adding Borders To add a border to your field or label: Click on the control you wish to change. From the Design ribbon, go to the far right of the Controls area.

The buttons in the first column control border thickness, pattern, and color. Tip: Right-click to Add Borders By simply right clicking on a control, you may select a border thickness from the Special Effects menu. Changing Background Colors

To change the background color of a textbox or a label: Click on the control you wish to change. From the Design ribbon, go to the Font area and click on the Background Fill button.

From the resulting menu, choose the appropriate background color for your control. To change the background color of an entire section: Select the section by clicking anywhere on its grid. Use the same process as described above to change the color of the entire section. Tip: Right-click to Change the Color To easily change the color of your text, control background, or section background: Right-click on the section or control you wish to change. From the menu that appears, select a background color from the Fill/Back Color submenu or a text color from the Font/Fore Color menu.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Using images Inserting an image Select the form section where you want to insert your image by clicking on its background grid. From the Design ribbon, go to the far left of the Controls area and click on the Logo button.

The Insert Picture window will appear.

Navigate to the pictureâ&#x20AC;&#x2122;s location on your hard drive or network drive. o Select the image you wish to insert. o Click on the button labeled OK. To move the image once it has been inserted: o Click on the image to select it. o Place your cursor anywhere above the image (watch for the open hand cursor). o Click and drag your image to a new location.

Resizing an Image Proportionately Double-click on the image you wish to resize. The Property Sheet pane will appear on the right. Click on the tab labelled Format.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Click on the down-facing arrow located next to the box labelled Size Mode. From the drop-down menu that appears, select Zoom Close the Property Sheet pane. Drag one of the corners or side handles to resize the image, just like resizing a field or text box. Tip: To crop an image so that less is displayed, utilize the Clip size mode (instead of zoom) and then drag to resize the image’s box. Just as you see less of the scenery outside your window upon lowering a shade, you see less of your image as you make the image’s box smaller. Adding Borders to an image Click on the desired image. From the Design ribbon, go to the far right of the Controls area. As was done with text boxes and labels, you can use the Border buttons to modify your image.

Box Label Border Style Border Color Special Effect Border Width

Description Choose the type of border you wish to display Choose the border’s color Select an effect to add unique dimensions to the border Choose the width applied to the border

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

FOURTH QUARTER

Modules What is a Module? A module is a collection of declarations, statements, and procedures that are stored together as a unit. Modules are very similar to macros since they are objects that add functionality to the database. However, while you create macros in Access by selecting from a list of macro actions, VBA (Visual Basic for Applications) programming language is used for writing modules. Modules generally belong to two types: class modules and standard modules. Class modules contain procedures that are associated with a specific form or report it is attached to. Standard modules contain general procedures that aren't associated with specific objects. Standard modules are listed under Modules in the Navigation Pane, whereas class modules are not. Developing Programming Code The programming language available in Access is Microsoft Visual Basic for Applications, which is nearly identical to Visual Basic 6.0 (VB6). VBA code can be stored in modules and code behind forms and reports. Modules can also be classes. To manipulate data in tables and queries in VBA, two database access libraries of COM components are provided: the Data Access Objects (DAO), which is included in Access and Windows and evolved to ACE in Microsoft Access 2007 for the ACCDE database format, and ActiveX Data Objects (ADO). Beside DAO and ADO, developers can also use OLE DB and ODBC for developing native C/C++ programs for Access. For ADPs and the direct manipulation of SQL Server data, ADO is required. DAO is most appropriate for managing data in Access/Jet databases and the only way to manipulate the complex field types in ACCDB tables. Designing a Module Accessing the Visual Basic Window In order to access the VBA environment, press Alt-F11 while your Access database file is open to access the Microsoft Visual Basic window. See the example below.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Project Explorer The Project Explorer can be found in the top left portion of the Microsoft Visual Basic window. It is a hierarchical listing of the objects recognized by VBA. If the Project Explorer is not visible when you open the Microsoft Visual Basic window, you can make it visible by selecting Project Explorer under the View menu.

In the example below, there are two "Microsoft Access Class Objects" - one is a form called frmProducts and the other is a report called rptCategories_Report. In addition, there is one Module called Module1.

The above reflect all objects that you've created in your Access database. YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Code Window The Code window is found to the right of the Project Explorer. It displays the VBA code for the object currently highlighted in the Project Explorer. If the Code window is not visible when you open the Microsoft Visual Basic window, you can make it visible by selecting Code under the View menu.

In the example below, the VBA code is displayed for the module called Module1.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

Immediate Window The Immediate Window is located directly below the Code window. It is an essential element of the debugger found within the VBA environment.

If the Immediate Window is not visible when you open the Microsoft Visual Basic window, you can make it visible by selecting Immediate Window under the View menu.

The Immediate Window is helpful for several reasons. It allows you to type code and press ENTER to view the results of the code. In addition, when you are in debug mode, it lets you view the value of a variable in its current state. Objects in Project Explorer

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

You might assume that all of the Forms, Reports, and Modules that you create will automatically appear in the Project Explorer. However, this is not true. The VBA environment will only know about the objects if you tag them as "having a module". By default, all Modules will appear in the Project Explorer, but not all Forms and Reports necessarily will. A Form will only appear in the Project Explorer if the Form's "Has Module" property is set to "Yes". Refer to the example below.

In addition, a Report will only appear in the Project Explorer if the Report's "Has Module" property is set to "Yes". Refer to the example below.

It is important to note that whenever you invoke the Code Builder in a Form/Report, Access will automatically set the Form/Report‟s „Has Module‟ property to „Yes‟.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

If you want to reference one or more of your Forms or Reports in the VBA environment, you may need to manually go to the Form/Report's Properties window and set the "Has Module" property to "Yes" as in the example above.

Security Microsoft Access offers the following ways to secure the application while allowing users to remain productive:    

Database Password Workgroup Security Encryption MDE Version

Database Password

The most basic security method is a database password. Once entered, the user has full control of all the database objects. This is a relatively weak form of protection which can be easily cracked.

Workgroup Security

The next level of protection is the use of workgroup security which requires a user name and password. Individual users and groups can be specified along with their rights at the object type or individual object level. This is especially helpful when you need to specify people with read only or data entry rights but this may be challenging to specify. A separate workgroup security file exists which contains the settings that can be used to manage multiple databases. It is important to note that workgroup security is not supported in the Access 2007 ACCDB database format, although Access 2007 still supports it for MDB databases.

Encryption

Another form of protection is encryption. Access databases can be encrypted. The Access 2007 ACCDB format offers significantly advanced encryption from previous versions.

Access MDE Version YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

If the database design needs to be secured in order to prevent changes, the database can be locked/protected and the source code compiled by converting the database to an MDE file. All changes to the VBA project (modules, forms, or reports) need to be made to the original MDB and then reconverted to MDE. In Access 2007, the ACCDB database is converted to an ACCDE file. Some tools are available for unlocking and decompiling, although certain elements including original VBA comments and formatting are normally irretrievable. Macros

What is a Macro? A macro is a stored series of commands that carry out an action. They can be very useful for automating simple tasks, such as performing an action when the user clicks a command button. It is not necessary to know how to program to use macros. Macros can perform a number of the common tasks that you can also use Visual Basic code to perform and can dramatically increase your productivity when working with your database. However, using Visual Basic code rather than macros gives you much more flexibility and power. Visual Basic provides much more functionality than macros such as returning values or iterating through record sets. Developing Macros If you do not have a programming background, you can use the macro feature to automate simple tasks through a series of drop down selections that Access has provided. Macros allow you to easily chain commands together such as running queries, importing or exporting data, opening and closing forms, previewing and printing reports, etc. Macros support basic â&#x20AC;&#x17E;IF conditionâ&#x20AC;&#x; logic and the ability to call other macros. Macros can also contain sub-macros which are similar to subroutines. In Access 2007, macros are significantly enhanced with the inclusion of error handling and temporary variable support. Access 2007 also introduces embedded macros that are essentially properties of an object's event. This eliminates the need to store macros as individual objects. Macros however, are limited in their functionality by a lack of programming loops and advanced coding logic. Most professional Access developers use the VBA programming language for a richer and more powerful development environment. Designing a Macro When you open the Database Window, select Macros under Objects and click on the New button.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

The following Macro window screen should appear.

In the Action column, click the arrow to display the list of available actions and select the action you want to use. There are several dozen actions to choose from. Depending on the action selected, you may have to specify Action Arguments in the lower part of the window. For example, if you select the OpenForm Action, the following Action Arguments appear in the lower part of the window for input.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE

If you want to add more actions to the macro, move to another action row and select a new action. Note that Microsoft Access carries out the actions in the order you list them. Comments for the action are optional. To save the macro, click on the "Save" icon and enter a name for your macro. Make sure you name this macro "Autoexec". By naming it Autoexec, Access will run this macro every time the database is opened.

Note: If you want to insert an action between two existing action rows, click the selector for the action row just below the row where you want to insert the new action and then click Insert Row Insert Row on the toolbar.

YOUNG JI INTERNATIONAL SCHOOL / COLLEGE