Ms Access Lab Manual

Section 5: Navigating your way through Microsoft Access 2007

NAVIGATING YOUR WAY THRU

MICROSOFT ACCESS (2007)

ACCESS - Information Technology (INT1001)

2

CHAPTER 5: DATABASE APPLICATION USING MICROSOFT ACCESS 2007 Microsoft Access is used to create and manage databases. A database in its simplest form is a collection of useful information. Examples of databases are the telephone directory, school records, employment records etc. Keeping track of all this information is a slow and tedious process, however, computerized database management systems (DBMS) such as Microsoft Access are designed to efficiently manage these collections of data.

STARTING ACCESS 2007; ______________________________________________________________________________ 1. 2. 3. 4. 5.

Click on Start Move the cursor to Programs Move the cursor to Office Move the cursor to Microsoft Office 2007 Click on the application Access 2007

CREATING A NEW DATABASE To create a new database, choose Blank Database and specify a new file name for the database, then press Create. The name of this database is UTECH Note: Be sure to use a descriptive name for the new database.

In the example we have students a university’s records of its students. We will call the database “ Database name : UTECH students Tables : students Faculty module

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

3

Figure 5.1 Naming the blank database.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

4

Additionally there are predefined templates for commonly used databases which are presented at the top left or via icons in the centre of the window as shown above.

Figure 5.2 Opening an existing database by browsing to the directory the database was saved to.

CREATING A TABLE The database consists of a collection of tables. Once the database is created you need to create tables to work with that database.

To create a table, you must describe the structure of the table to access by describing the fields within the table; For each field you must indicate the following: A. FIELD NAME - each field in the table must have a unique name B. DATA TYPE - indicates to Access the type of data the field will contain C. DESCRIPTION - Allows you to enter a detailed description of the field. D. FIELD SIZE -

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

5

E. PRIMARY KEY -

NOTE* Every database has its own set of tables

The tables are used to hold the data organized in a tabular manner. Each row has details (fields) relating to the same data item, for example the name, age and address of a student at a university. Each column will contain the same detail (field) item for different records. For example , the surname name for every student record will be found in one column.

Creating the table via the Datasheet View

The primary key field (right click to rename)

Other new fields added as necessary (right click to rename).

Figure 5.3 Creating a table and adding new fields. The key field is a unique field as an index.

Having created the database, a window presenting the datasheet view of a new table to be created is displayed. New fields may be added here as necessary using the appropriate descriptive name.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

6

An ID field (primary key), a field is presented, that unambiguously identifies a record and generally ensures that no duplicate rows exist. The other fields necessary may also be added here by clicking on the “Add New Field” tab. Individual data items may then be added under the respective column labels. On closing this view (selecting “X” at top right of the table window) you will be required to name the table. Creating the table via the Design View

Figure 5.4 Giving the table a meaningfull name.

Select “View” the ribbon menu and select “Design View” from the drop down menu which appears. You are required to give the table its name “Students” before proceeding further. In this view the name of the fields, type of data e.g. text , numbers, date, etc. and a brief description of the field are manually typed in.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

7

To open an existing database choose from the list presented top right or select More to display additional database choices.

Figure 5.5 Using the design view to set the characteristics of each field.

The active design view window opens with the ID field as the active tab requiring an entry for its name “Student ID” and data. Selecting the “Data Type” allows for modification of the field which we will change from auto number to text to accommodate UTech style identification numbers. The properties of each field are modified after selecting this field in the top window and modifying the content by changes in the lower left corner of the design view window.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

8

Figure 5.7 inserting field names of the field.

The fields for the students table include: Students ID number Last Name First Name Date of Birth Address 1 Telephone number Faculty Module Filling out the description for each field aids in clarity and removes ambiguity about the intended use of each field. To enter the data, open the datasheet view from the menu ribbon and type the information under the appropriate field.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

9

fblm

Figure 5.6 Putting data in the table using the datasheet view.

CREATING AND RUNNING QUERIES

ACCESS allows you the capability to answer questions about information stored in your database. Creating a query using the wizard Open a saved table filled with data, “Students”,. Go to the "Create" tab on the Access screen, then click Select "Query Wizard" and OK..

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

10

Figure. 5.7 Creating the query using the query wizard

STEP 1

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

11

Step 2

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

12

Step 3

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

13

Step 4

Figure 5.8 Selecting fields from necessary tables in the database.

1. Pick the "Tables/Queries" option under the query wizard. Choose the table that contains the data for your query. 2. Select the "Available Fields" tab to create your query. Add the fields that you want into your "Selected Fields" list. Choose fields such as “Student Id”, "Last Name," "First Name”, “Email”, "Telephone #”, “Address1”, “Address2” then Hit "Next" when you've finished choosing your fields. 3. Give your query list a name (“Student Contact Information” in this example), then select "Finish" to complete the project. Access shows all of the contact records in row-andcolumn view. The fields that you've specified in Step 2 will be displayed as shown below.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

14

Figure 5.9 The final product the students query. The query automatically saves in Access so that you can reopen it later. CREATING A QUERY VIA THE DESIGN VIEW Alternatively the query may be made in the “Design View” by selecting the “Design View” tab instead.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

15

Figure 5.10 Choosing tables from which the query will be done.

Queries may involve several tables or other queries, these are chosen from the show table window (above). For the multi-table, multi-query query to be successfully built it is necessary that each table or query used contain at least one field in common as the basis for a relationship between them. This is achieved mainly by inclusion of primary and foreign keys which may be a good idea for further research. Once the tables and queries are chosen then Access 2007 automatically detect the relationship between them (indicated by a line joining them as shown above).

Figure 5.11 Creating the necessary relationships via unique key fields among the tables of the database.

The relationship may be manually created by selecting the “Relationship” tab from the

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

16

“Database Tools” menu tab. Clicking on a field in one table, holding and dragging to the required field in another table and releasing and selecting “Create” establishes the relationship.

Figure 5.12 Manually creating the relationships among the tables of the database.

Upon completion of the relationships then the construction of the query may be completed by using the lower section of the “Design View” window.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

17

Figure 5.7 13

First select the table then choose fields with corresponding tables in each query column, checking the show box determines if the data contained in that field will be shown in the results of the query. Field values are evaluated based on the criteria, for example, “> 49” in the criteria section of the “Final Exam Grade” exam score to indicate grade score of 50 or more.

Figure 5.7 14 Using the design view to set the constraints of the query.

NB. Remember to create a relationship by joining tables for queries and reports involving more than one table. You may also need to install a printer before Microsoft Access allows the creation of a report.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

18

CALCULATED FIELDS (COMPLEX QUERIES) A more complex query uses a calculated field which is typically a field which is displayed ( as a result of a calculation) that is not a part of the original structure of any of the tables in the database. As an example, suppose we wished to show those who have passed the Information Technology course (IT1001). This is achieved by summing the weighted scores for each component to give a final course score of which a score of 50 or more would be successful. Using a weighting of 20% for the “Assignment”, 30% for the “In course Test” and 50% for the Final Exam. Using the design view type the heading/name for the new calculated field followed by a colon ”:” Follow this by the expression to be used in the calculation involving the field names which are themselves enclosed in square brackets (if the field name consists of more than one word). Selecting the “Shift + F2 key” allows easier editing of the formula which would be as shown below. Final It Score: [In Course Test]*0.3+[Assignment]*0.2+[Final Exam]*0.5 In this case it was not necessary to display the “Course ID” hence the Show box remained unchecked as in figure 5.17 below. For the “Course ID” field the criteria is that the course id be that of Information Technology hence ” =IT10011” in the criteria section. Finally check the show box of the calculated field to ensure that the newly calculated value is displayed (see figure 5.17 below).

Figure 5.15 Using the design view to set the constraints of the query for the calculated field.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

19

Figure 5.16 Click the run icon to display the query results.

Selecting “Run” on the menu will display the results of the query as shown in figure 5.19 below.

Figure 5.17 The “Final IT Score” Query showing those students who have successfully completed the IT course.

Exercise 5.4 If the cost to build each unit is approximately 40% of its selling price then use a calculated field to show the cost of building each unit.

CREATING AND MODIFYING FORMS Access forms provide a quick method of entering new data and/or modifying existing data in a table. The Access 2007 forms tools include: The Form command which makes a basic form, showing a single record at a time.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

20

The Split Form command creates a form showing one record on top, and includes the datasheet view of entire source table on the bottom. The Multiple Items command creates a form that shows all the records at once, which looks very similar to the source table in datasheet view. The Form Wizard is hidden under the More Forms command. It walks through the process of creating more customized forms.

Figure 5.18

CREATING A FORM USING THE FORM COMMAND To create a form using this command: Begin by highlighting the table you wish to use as a source table. With the source table highlighted, select the Form (see figure 5.20) command from the Forms command group in the Create tab on the Menu Ribbon.

The newly created form is opened in “Layout View” and has the same name as the source table by default. You can give the form a new name by saving the form as in figure 5.21 below.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

21

Figure 5.19 A newly created form fro the Students table.

This basic form may then be modified to improve its readability/presentation by clicking on the “Form Layout Tools” and using the available icons. For example in the design view field alignment is aided by selecting to display grid lines. A form title, page numbers, auto generated date and time may also be inserted. Fields can be rearranged by dragging and dropping them to their desired location. To completely remove a field, right-click on it and choose the Delete menu item. The finished form is displayed in figure 5.22 below.

Figure 5.20 Modified form with title, date and time, as well as, wider column for labels.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

22

CREATING A FORM USING THE FORM WIZARD Select “Create” from the menu ribbon then “More Forms” .

Figure 5.21 Choosing fields while creating the form via the form wizard.

Using the directional arrows select fields needed from each table. In our example all the fields of the “Students” table are used. Selecting “Next” gives another window to choose the layout of the form as in figure5.23 (Columnar in this case).

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

23

Figure 5.22 Selecting layout via the form wizard.

Similarly the next window offers a style in this case “Office”.

Figure 5.23 Choosing an appropriate style in this case office.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

24

At this stage selecting “Finish” (figure 5.26) will yield a form similar to that which was produced before.

Figure 5.24 Naming the form and exiting the wizard.

Choosing “Finish” displays the form in “Form View” as seen below in figure 5.27.

Figure 5.25 The newly created Students form via the form wizard.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

25

On the other hand choosing the “Modify the form’s design” allows for immediate modification of the form which is now displayed in “Design View” (figure 5.28 below). The modification is facilitated by selecting the tabs and menu items avaible form the “Form Design Tools” menu tab.

Figure 5.26 The Students form in design view.

For example “Property Tabs” under the “Database Tools” gives a means of modifying each field with respect to colour (form, fields), font (size, colour, texture), hidden or visible fields, places of decimal etc.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

26

Figure 5.27 Property sheet to modify elements of the form.

HIDING FIELDS ON A FORM There will be times when a field will not be needed on a form. Access 2007 allows you to hide fields by setting the Visible field property in Design View (figure 5.30). To Hide a Field on a Form Hiding a field makes it invisible on the form when it is viewed in Form View. To hide a field: In Design View, open the Property Sheet. Change the Visible property setting to No, as seen below:

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

27

Figure 5.28 Using the property sheet to make Student ID invisible on the form.

Visible Property Setting

REPORTS A report consists of information that is pulled from tables or queries, as well as information that is stored with the report design, such as labels, headings, and graphics. The tables or queries that provide the underlying data are also known as the report's record source. If the fields that you want to include all exist in a single table, use that table as the record source. If the fields are contained in more than one table, you need to use one or more queries as the record source. Those queries may already exist in your database, or you may need to create new queries specifically to fit the needs of your report. Reports are created in a manner similar to forms. Creating Report via the “Report” tab Like Forms reports are initiated by selecting the “Create” menu and selecting an icon from the “Reports” section. Selecting the “Report” icon generates a report for the active table or query as shown below in figure 5.31 for the “Students” table.

Figure 5.29 With an active table or query open, select the Report icon to automatically create the corresponding report.

Note a count of the number of records and page of the report is displayed on the report itself (figure 5.32). The window on the right allows for the addition of fields from other tables and queries.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

28

Figure 5.30 An easily crested report.

Switch to the “Design View” (figure 5.33 below) to make modifications (in a similar manner as the forms) such as the layout of fields deletions, report background, types of font etc.

Figure 5.31 The design view may be used to make modifications as

necessary.

Modifications can be done on individual fields one at a the field, right clicking and selecting the required attribute left).

time by selecting (figure 5.34 below

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes Figure 5.325 Modifying via the property sheet.

ACCESS - Information Technology (INT1001)

29

Figure 5.334 Modifying via right mouse click.

Alternatively modifications can be done via the properties sheet (figure 5.35 above right).

CREATING THE REPORT USING THE REPORT WIZARD Selecting the “Report Wizard” from the “Create” menu yields a window which allows the choosing of optional fields from the tables and queries within the database. Using the directional arrows (single arrow for individual fields and double arrows for all fields) select fields needed from each table. In our example all the fields of the “Students” table are used hence the double arrows will be used (figure 5.36).

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

30

Figure 5.34 Creating the Report via the report wizard.

The next option is to select grouping levels (and the fields to be shown) in this case “Faculty” was chosen (figure 5.37).

Figure 5.35 Selecting grouping levels for the report.

The next option is to sort the records within fields by ascending or descending order as shown in figure 5.38 below.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

31

Figure 5.36 Selecting the option of sorting the records in ascending or descending order.

The layout of the report is selected next (figure 5.39).

Figure 5.37 Selecting the layout of the report.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

32

Then the style in this case “Office” see Figure 5.40

Figure 5.38 Choosing the style of the report.

Selecting finish displays the finished report .

Figure 5.39 The report may be given a name here.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

33

Figure 5.40 The report as produced by the report wizard.

Modifying the layout by clicking and dragging etc. (as previously described in modifying forms above) via the “Design View” will yield a better looking more readable report as shown in figure 5.43 below.

Figure 5.41 The modified report with a widenned column for faculty.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

34

Exercise 5.6 Create a report to show the owners of houses in the scheme and their occupations. GROUPING AND SORTING

With the report open see figure 5.44, select the Group & Sort command from the Grouping & Totals command group on the Format tab in the Ribbon.

Figure 5.42 Selecting the menu icon option for segmenting the report into groups or sorting.

This opens a Group, Sort, and Total dialog box in the lower portion of the window which can be utilized for selecting how information should be grouped or sorted on the report as illustrated in figure 5.45 below.

Figure 5.43 Use these buttons to select the field to be put into groups ot to be sorted.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

35

For this example a main group of sex will be chosen (figure 5.46).

Figure 5.44 Choosing to group the report field by sex.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

36

Selecting a sub-group using address2 results in the report having a second level of grouping by addresses as shown belowin figure 5.47.

Figure 5.45

Totals and Sub-Totals Similarly, choosing the “Totals” menu icon will yield a choice of function for totals, count, maximum, etc. to be considered for the report. In the example below a simple average of the final IT scores is shown.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

37

. Figure 5.46

To show calculations for example sub-totals and totals first the report fields have to be divided into groups. In the example, a report of all grades was used and the average function was selected for the final exam grouping by faculty (figure 5.48). An average was produced for the group, that is for the faculty as well as an overall average for all the faculties, i.e. the entire student body (figure 5.49).

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

38

Figure 5.47

Exercise 5.7 Use a report to show the total cost to build each section for each contractor as well as the total cost to build the entire scheme.

EXERCISE 5.1 The Lions Club at St. Hilda’s high school raises money by selling merchandise imprinted with the school logo to Alumni. The Lion’s Club purchases products from vendors that deal in school specialty items. The database consists of two tables. The Item table contains information on items available for sale. The vendor table contains information on the vendors.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

39

Data for ITEM table Item ID

Description

BC02

Baseball Cap

CM12

On Hand

Cost

Selling Price

Vendor Code

15

$100.00

$150.00

AL

Coffee Mug

20

$37.50

$50.00

GG

DM05

Doormat

5

$140.00

$170.00

TM

OR01

Ornament

25

$30.00

$40.00

GG

PL05

Pillow

8

$130.00

$150.00

TM

PN21

Pennant

22

$56.00

$70.00

TM

PP20

Pen Set

12

$160.00

$200.00

GG

SC11

Scarf

17

$80.00

$120.00

AL

TT12

Tie

10

$89.00

$120.00

AL

WA34

WASTEBASKET

3

$140.00

$150.00

GG

DATA FOR vendor Table VENDOR NAME CODE

ADDRESS CITY

STATE

CODE

TELEPHONE NUMBER

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001)

40

AL

Alum Logo Inc.

136 Red Hills

Aurora

WI

53595

608-555-9753

GG

GG Gifts

52 Constant

Brisbane

NW

88061

505-555-8765

TM

Trinkets ‘n More

87 Hope

Kentwood VA

20147

804-555-1234

TASKS: 1. Create a database to store the objects related to the Lion’s Club. 2. Create two tables Item and Vendor tables. 3. Create a FORM to enter the Items and enter the data from there. 4. Display all the fields for those items that have a vendor code of TM or have a selling price less than $100.00. 5. Display the items with a cost greater than $100.00 and where the number on hand is less than 5. 6. List the Item ID and description for all items that have a selling price of $150.00 or less. 7. Display the Item Id, Description, Cost, Vendor code along with the Name and Telephone Number of each vendor. 8. List the items Id, Description and Selling Price where Description begins with the letters, Pe. 9. Display the item Id, Description and Cost Price showing the Description in descending order. 10. Create a report of the Vendor Table. Exercise 5.1 Organize the fields for a housing schemes as indicated below into tables. Give the scheme an appropriate name of your choice. Use this scheme name as the name of your database which you should save in your “My Documents” Folder. Exercise 5.1 through to Exercise 5.7 all refer to the housing scheme first encountered here in Exercise 5.1. Each exercise either builds upon or uses information from the preceding exercise. House table to include information on:

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes

ACCESS - Information Technology (INT1001) 1. 2. 3. 4. 5.

41

the type of house i.e. town house, apartment or detached. the number of bedrooms, selling price, lot number. section number.

Contractor table to include information on: 1. contractors name, 2. address, 3. phone number, 4. section number. Owner table to include information on: 1. owners name, 2. lot number, 3. occupation , 4. telephone number. 5. Gross salary Please Note that, Field names: may be up to 64 characters in length may contain letters, digits and spaces may NOT contain periods, exclamation points square brackets, etc may not be repeated in the same table

Exercise 5.2 Create and enter data for each table, at least 10 rows for each table.

Exercise 5.3 Create and run a Query to show a list of owners of 3 bedroom houses costing more than $15, 000, 000. Exercise 5.5 Create a form and use it enter information relating to a change in ownership of a house and a change in occupation of another owner.

Information Technology (INT1001) Prepared by Denvil Allen & Caroline Parkes