Access Beginners Notes by Sarah Mason

Access Introduction Training Manual

Introduction to Database Using MicrosoftÂŽ Access 2007 Note: Microsoft is a registered trademark and Windows is a trademark of the Microsoft Corporation

~ Presented by Sarah Mason ~

ÂŠ Sarah Mason

Access Introduction Training Manual TABLE OF CONTENTS Chapter 1 ~ Overview of the 2007 changes................................................................................... 5 1.1 ~ Microsoft Office Fluent user interface .............................................................. 5 1.2 ~ Getting Started with Microsoft Office Access page........................................ 6 1.3 ~ Navigation Pane................................................................................................... 6 1.4 ~ Tabbed objects .................................................................................................... 7 1.5 ~ Status bar ............................................................................................................. 7 1.6 ~ Mini toolbar .......................................................................................................... 7 1.7 ~ Help window F1 .................................................................................................. 7 Chapter 2 â&#x20AC;&#x201C; Tables................................................................................................................................ 8 2.1~ Designing the database ....................................................................................... 8 2.2 ~ Creating a new database ................................................................................. 10 2.3 ~ Create a new table using the wizard ............................................................. 10 2.4 ~ Create a new table using design view ........................................................... 11 2.5 ~ Enter and edit records in datasheet view ..................................................... 13 2.6 ~ Changing the field size of Tables .................................................................... 15 2.7 ~ Adding/Deleting Fields ..................................................................................... 17 2.8 ~ Changing the layout of the table .................................................................... 20 2.9 ~ Renaming a Column ......................................................................................... 21 2.10 ~ Hiding and Revealing a Column.................................................................... 22 2.11~ Navigate through records in datasheet view ............................................... 24 2.12 ~ Finding a specific record ................................................................................ 25 2.13 ~ Sorting and Filtering ....................................................................................... 27 2.14 ~ Sort the records in a table ............................................................................ 28 2.15 ~ Filtering Empty Fields ..................................................................................... 29 Chapter 3 ~ Relationships ................................................................................................................ 36 3.1 ~ Understand the difference between flat file and relational ........................ 36 Chapter 4 ~ Forms ............................................................................................................................. 42 4.1 ~ Create a form using the wizard ...................................................................... 42 4.2 ~ Form Layout ....................................................................................................... 45 4.2 ~ Navigate through records using form view ................................................... 48 4.3 ~ Add a background colour ................................................................................. 49 4.4 ~ Add special effects ............................................................................................ 50 4.5 ~ Sorting in a form ............................................................................................... 50 4.6 ~ Filter by selection and excluding selection ................................................... 51 4.7 ~ Removing a filter ............................................................................................... 52 Chapter 5 ~ Queries ........................................................................................................................... 53 5.1 ~ Create a simple query using the wizard ........................................................ 53 5.2 ~ The Query Wizard ............................................................................................. 54 5.3 ~ Introduction to Query Design.......................................................................... 56 5.3 ~ The Query Window ............................................................................................ 57 5.4 ~ Selecting the Columns...................................................................................... 57 5.5 ~ To Add Columns ................................................................................................ 58 5.6 ~ Executing a Query ............................................................................................. 59 5.7 ~ Selecting a Column ........................................................................................... 59 ÂŠ Sarah Mason

Access Introduction Training Manual 5.8 ~ Removing a Column From a Query ................................................................ 60 5.9 ~ Replacing a Column .......................................................................................... 60 5.10 ~ Moving a Column ............................................................................................ 60 5.11 ~ Data Entry on a Query ................................................................................... 61 5.12 ~ Query Printing ................................................................................................. 61 5.13 ~ Sort records ..................................................................................................... 61 5.14 ~ Using operators and wildcard characters .................................................... 63 5.15 ~ Include a numeric condition in a query....................................................... 66 Save and close the query.5.16 ~ Include a calculated field in a field ................ 68 5.16 ~ Include a calculated field in a field .............................................................. 69 Chapter 6 ~ Reports........................................................................................................................... 75 6.1 ~ Create a report using a wizard ....................................................................... 75 6.2 ~ The Design View ................................................................................................ 81 6.3 ~ Moving Fields and Labels in Design View ...................................................... 82 6.4 ~ Formatting Text in Reports.............................................................................. 84 6.5 ~ The Print Preview .............................................................................................. 85 6.6 ~ The Page Setup of a Report ............................................................................ 86 Chapter 7 ~ Manipulating the database ........................................................................................ 89 7.1 ~ Copy a database object.................................................................................... 89 7.2 ~ Rename a database object .............................................................................. 89 7.3 ~ Delete a database object ................................................................................. 89 7.4 ~ Compact and repair a database...................................................................... 90 7.5 ~ Backup a database ............................................................................................ 91 Chapter 8 ~ Working with other applications .............................................................................. 92 8.1 ~ Import spreadsheet data into an Access table............................................. 92 7.2 ~ Import text into an Access table .................................................................... 97

ÂŠ Sarah Mason

Access Introduction Training Manual Introduction These notes are designed to provide reinforcement material for tutor led training sessions in Microsoft Access. These revision exercises assume that the necessary program has been fully and correctly installed on your computer. However, in Access, some features are not installed initially and a prompt to insert the Office CD may appear when these features are accessed. Aim To provide the knowledge and techniques necessary to be competent at an introductory level in Access 2007. Objectives After completing the exercises the user will have experience in the following areas: Explore the new 2007 environment design, create and save a database add, amend and delete records add, amend and delete fields import data into a database interrogate a database using complex criteria create and format reports present and print data

ÂŠ Sarah Mason

Access Introduction Training Manual Chapter 1 ~ Overview of the 2007 changes 1.1 ~ Microsoft Office Fluent user interface The new Office Fluent user interface includes a standard area called the Ribbon, which contains groups of commands that are organized by feature and functionality. The Ribbon replaces the layers of menus and toolbars found in earlier versions of Access.

Use the Office Fluent Ribbon to locate groups of related commands faster. For example, if you need to create a form or report, use one of the commands on the Create tab. The new design makes it easier to find the commands that you need, and you will discover features that you otherwise might not notice. Commands are placed closer to the surface, which means that you do not need to dig for them in menus or memorize their locations. Key features of the Office Fluent user interface include:

Command tabs — Tabs that display commands that are commonly used together so that you can find the commands that you need when you need them. Contextual command tabs — A command tab that appears depending on your context — that is, the object that you are working on or the task that you are performing. A contextual command tab contains the commands most likely to apply to what you are doing. Galleries — New controls that display a preview of a style or option so that you can see the results before you commit to a choice. Galleries are employed throughout the 2007 Microsoft Office system interface. Quick Access Toolbar — A single standard toolbar that appears on the Ribbon, offering instant, single-click access to the most needed commands, such as Save and Undo.

Access Introduction Training Manual 1.2 ~ Getting Started with Microsoft Office Access page This page provides quick access to a library of professionally designed database templates, in addition to databases that you recently opened and (if you are connected to the Internet) links to popular Office Online articles.

1.3 ~ Navigation Pane This pane lists and provides easy access to all of the objects in the currently open database. Use the Navigation Pane to organize your objects by object type, date created, date modified, related table (based on object dependencies), or in custom groups that you create. Need more space to work on your form design? You can easily collapse the Navigation Pane so that it takes up little space, but still remains available. The Navigation Pane replaces the Database window that was used in versions of Access earlier than Access 2007.

ÂŠ Sarah Mason

Access Introduction Training Manual 1.4 ~ Tabbed objects Your tables, queries, forms, reports, and macros are displayed as tabbed objects in the Access window.

By clicking the object tabs, you can easily switch between various objects. To show the tabs if the windows are not already defaulting to tab view:

Click the Microsoft Office Button

, and then click Access Options.

The Access Options dialog box appears. Click Current Database. In the Application Options section, check the Display Document Tabs check box. 1.5 ~ Status bar The bar at the bottom of the window that displays status information and includes buttons that let you switch between views.

1.6 ~ Mini toolbar An element, similar to a toolbar, that transparently appears above text that you selected so that you can easily apply formatting, such as bold or italic, or change the font.

1.7 ~ Help window F1 Unlike previous versions of Access, Office Access 2007 offers easy access to both Access Help and Developer Reference content from the same Help window. You can easily change the scope of your search to the Developer Reference content only, for example. Regardless of the settings that you make in the Help window, all of the Access Help and Developer Reference content is always available on Office Online. ÂŠ Sarah Mason

Access Introduction Training Manual Chapter 2 – Tables 2.1~ Designing the database Procedure: A database is simply an organised collection of information, such as a telephone directory, address book, etc. A database management system is a computer program, which allows a user to create, maintain and process database information and to access, display and manipulate that information. One of the most important tasks in setting up a new Access database is that of designing the database – that is, planning the various object that are to make up the database. This should be done before using Access to actually create those objects. In designing a database, you must first determine its purpose by answering such questions as “What information will want to retrieve from this?” and “How will this information be used?” This initial step is important since it helps you determine the types of output you will require from the database, which in turn help you determine the types of records you will need to input into the database, as well as the details (fields) those records must include. The next step in designing a database is that of determining the necessary forms and reports for inputting and outputting information, respectively. To do this, you should examine forms and reports you may currently use to record and present information. You should also draft examples of forms you wish to use to enter and view your database information and reports you wish to generate from the information. The next step and probably the most difficult step in designing a database is that of determining the necessary tables for storing the actual information. Although a database need contain only a single table, you should avoid including too much information in any one table. It is better to break up one large table into two or more smaller tables and relate those tables by common fields. An Access database is made up of various objects: Tables - All data held in a database is stored within a table. In more advanced databases, many small tables are created rather than one large table. This helps the efficiency of the database when searching for information. Data can be entered, edited or deleted, directly in a table, so no further objects need to be used to manipulate the data. When a table is opened, all the data held in the table can be viewed on one large screen using scroll bars. Queries Questions or selective views of data that can be saved, if required, and used again. They can be used to create calculations and can form the basis of a report. Forms To view only one set of data at a time, e.g. one customer at a time, a form is created. Data can be entered, edited or deleted in a form. All the data that is created or altered in a form, is automatically updated in the appropriate tables. © Sarah Mason

Access Introduction Training Manual Reports Reports are created in a similar way to forms, either from a single table or a query but will give a hard copy print out of the data. Reports can show only one set of data per page, groups of data per page, or all the data at once in a similar way to a table. Pages - As with forms, pages can be created to display data pages from the current database but in HTML format. These pages can then be used on The Internet or an Intranet. Macros A macro is a set of recorded keystrokes, which can be saved and rerun at any time. A macro is usually assigned to a button on a form to repeat certain tasks. Modules - If a developer wants to enhance a database with features that are not available from the Access menu bar, modules can be written in a programming language.

ÂŠ Sarah Mason

Access Introduction Training Manual 2.2 ~ Creating a new database With Access, you can create a new database in one of two ways: By using the Database Wizard to create in one operation the necessary tables, queries, forms and reports for a specific type of database. By creating a blank database and by adding the necessary tables, forms, reports and other database objects to the database 2.3 ~ Create a new table using the wizard

From the opening window in Access 2007, you will see the two options: Blank Database Featured online templates From the ONLINE TEMPLATES choose Events

Download the template into your user area Change the Navigation pane to view by OBJECT TYPE so you can get a feel for the scale of the template

The EVENTS database is extremely extensive containing many forms, queries and reports readymade and linked together. It is helpful however to have a basic understanding of the mechanics of a database before using the templates as they can be tricky to navigate.

ÂŠ Sarah Mason

Access Introduction Training Manual 2.4 ~ Create a new table using design view To create a new table, click on the Create tab within the Ribbon.

Within the Tables group click on the Table button.

You will see the following.

Click on the Design View button to open the table in design view. This will display the Save As dialog box. Enter a name for the table, i.e. type tbl Products.

ÂŠ Sarah Mason

Access Introduction Training Manual

Click on the OK button. In the main window enter the new fields as shown below.

Save and close the table tbl Products.

ÂŠ Sarah Mason

Access Introduction Training Manual 2.5 ~ Enter and edit records in datasheet view

Enter the following data in the table in datasheet view

Press TAB to jump the cursor between fields Press CTRL + ‘ to copy the data in the same field on the row above.

Alter the LAMB entry to BRITISH LAMB

Find all occurrences of MEAT SUPPLIERS and change the ROSIES MEATS using the REPLACE function

Access Introduction Training Manual Widen the columns if necessary by clicking and dragging the column to the right

You can double click the right edge of a column heading which will adjust the width of the column to the widest entry

Adjust the row depth by positioning the pointer between any two record selectors (record selector: A small box or bar to the left of a record that you can click to select the entire record in Datasheet view and Form view.) at the left side of the datasheet, and drag until the rows are the desired size.

ÂŠ Sarah Mason

Access Introduction Training Manual 2.6 ~ Changing the field size of Tables Procedure: Having created a table, either manually or by importing data, it is still possible to change its design. Fields can be added, deleted or have their properties amended. This may be particularly important when a table has been created as a result of data import. Exercise: Open the Membership database and open the Members table. Switch to Design View and make sure the cursor is positioned within the first field, Surname.

Notice that in the Field Properties section, the Field Size is set to 255 characters. This is the default size for all text fields created during the import process and is too large for most applications. It will need to be reduced to make the storage of the database more efficient.

Click in the Field Size property and change the value to 30. Change the Field Size for all the other Text fields to 30, except Sex, which has a fixed length of 1 (M or F). Change the Field Size of Sex to 1. Save the table and a warning box is displayed because there is a possibility that when a field is shortened, some data may be lost.

ÂŠ Sarah Mason

Access Introduction Training Manual

Click Yes to save the table with the new field sizes. Switch to Datasheet View. The appearance of the table will not have changed. The column display sizes have not changed, only the field sizes. There is a danger that data can be permanently lost when reducing field sizes. Switch back to Design View. Change the Field Size property for the First_Name field to 4 characters. Save the table and click Yes to the warning message. Switch back to Datasheet View.

The First_Name values for all the records have been truncated to 4 characters. The missing data has been lost permanently. Switch to Design View and change the Field Size property for the First_Name Field back to 30 characters. Save the table, then switch to Datasheet View. Even though the First_Name field is now 30 characters long again, only 4 characters of data are left in it, therefore care must be taken when reducing Field Sizes. Leave the database open in Datasheet view.

ÂŠ Sarah Mason

Access Introduction Training Manual 2.7 ~ Adding/Deleting Fields Procedure: New fields can be added to a table to cope with extra requirements for data, or fields can be deleted to remove unwanted information. If the table already contains records, adding a field will usually require new data to fill any gaps, and deleting a field will result in data being lost. Exercise: With the Members table of the Membership database still open switch to Design View. An extra field is needed to enter the fees payable by each member. Click on any part of the DOB row to select that field, then click the Insert Rows button,

. A new row is inserted above DOB.

In the new row, enter a Field Name of Fees and select a Field Type of Currency. Save the table and switch to Datasheet view. A new empty column has appeared between Sex and DOB. Data for all the existing records will need to be entered.

ÂŠ Sarah Mason

Access Introduction Training Manual Fields can also be added or deleted from Datasheet View. Click on any value in the First_Name column and select Datasheet | Fields & Columns|Delete.

Click Yes to delete the field. The table should now look like this.

Save and close the database.

ÂŠ Sarah Mason

Access Introduction Training Manual Further Practise – Chemistry Open the Chemistry Lab database and the Elements table. Delete the Colour field. Close the table, saving the changes if prompted. Open the Stock table. A new order of gold and silver has just arrived. You now have 10 units of gold and 30 units of silver. Amend the records accordingly. Close the table and database. Further Practise – Egypt Open the database Egypt and the Bookings table. Make sure all of the data in the table is displayed, by widening columns, if necessary. Ms Janice Laughton wishes to book a 14 night holiday for herself, two friends and their five children. She wants to leave from Newcastle Airport on 29/01/03 and stay at the Excelsior Hotel on a full board basis. She would like an excursion to see the Pyramids and has paid a £500 deposit. Add the record to the table. The Nile Princess has been replaced by a brand new cruiser, The Osiris. Replace all occurrences accordingly. Stewart Henshaw has cancelled his trip. Delete the record. Ulrika Fredricsson wants to fly from Luton instead of Heathrow. Amend her record. Insert a new field - Transport - between Excursions and Deposit. The Data Type is Text and the field length 20. Save the changes. The trips to Abu Simbel, Karnak and Luxor are by boat and the others are by bus. Enter the appropriate item (Boat or Bus) for each record. Close the table. Close the database.

Access Introduction Training Manual 2.8 ~ Changing the layout of the table Procedure: If you wish to change the order of the columns, select the column by clicking above the field name. Click and drag to the new position Exercise: Open the Membership database and the members table in Datasheet view . Select the columns you want to move. To select one column, click the field selector for that column. Click and drag to the new position.

To move a group of columns, first select them, click and hold the mouse on one of the selected columns, start dragging left or right in the desired direction until the thick vertical guiding line is positioned in the desired location, then release the mouse.

ÂŠ Sarah Mason

Access Introduction Training Manual 2.9 ~ Renaming a Column There are various ways of renaming a column: You can double-click its name on the header You can right-click the column's name and click Rename Column When any cell under the column has focus, on the ribbon, click Datasheet, in the Fields & Columns section, click the Rename button Exercise: Rename the SEX field to be GENDER

ÂŠ Sarah Mason

Access Introduction Training Manual 2.10 ~ Hiding and Revealing a Column If a table contains many columns and at one time you think you don't need all of them, you can hide some. To hide a column, you can drag the right border of its column header completely to its left border as we saw earlier to change the width of a column; when the vertical guiding line reaches the left border, release the mouse: the column would be hidden from the table:

ÂŠ Sarah Mason

Access Introduction Training Manual Exercise Open the PRODUCTS database and then open the products table To hide one or a group of columns, you can right-click and click Hide Column. You can also right-click any column and click Unhide Column. This would open the Unhide Columns dialog box.

To hide a column, clear its check box. To reveal a column or a group of columns previously hidden, right-click any column header on the table and click Unhide Column. In the Unhide Columns dialog box, put a check mark on each column you want to show.

ÂŠ Sarah Mason

Access Introduction Training Manual 2.11~ Navigate through records in datasheet view Procedure The lower left side of the table is made of four buttons used to navigate the table, one button used to create a new record, and a text box. Each button plays a specific role: Button

Name

Role

First Record

Allows moving to the first record of the table

Previous Record

Allows you to move one record back (if there is one) from the current record

Current Record

Displays the number representing the current record out of the total number of records

Next Record

Allows moving you one record ahead

Last Record

Allows moving you to the last record of the table

New (Blank) Record Used to enter a new record on the table

ÂŠ Sarah Mason

Access Introduction Training Manual 2.12 ~ Finding a specific record Finding Data Sometimes there will be some regular problems in your database. For example during data entry, some information could be missing, mistyped, or entered in the wrong field. These problems would be easy to locate on a small table or form. If the database grows, values could become difficult to locate. Fortunately, Microsoft Access provides many tools you can use to find values and take actions. To assist you with finding a value in a table or a form, the Home tab of the Ribbon is equipped with a section named Find:

To start looking for a value, you can click the Find button or you can press Ctrl + F. This would open the Find and Replace dialog box with the Find tab selected:

The Find and Replace dialog box is modeless, meaning you can access the table or form in the background while the dialog box is present. To specify the value to look for, type it in the Find What combo box. If you had previously used the dialog box, the Find What combo box keeps track of the previous searches and stores them in the control. This would allow you to perform a search on a value previously used.

ÂŠ Sarah Mason

Access Introduction Training Manual The Look In combo box allows you to select the column where the value should be located. By default, when the Find and Replace dialog box displays, it selects the column or field that had focus and puts its name in the Look In combo box. You can look into that column but, if you want to search the whole table, select its name in the Look In combo box. The Match combo box allows you to specify how close the match should be. The options are Any Part of Field, Whole Field (the default), and Start of Field. The Search combo box allows you to specify the direction to follow. The options are Up, Down, and All (the default): After specifying the options, you can click Find Next. After using the Find and Replace dialog box, to dismiss it, you can click Cancel or press Esc.

Exercise: Find all occurrences of CASH & CARRY

ÂŠ Sarah Mason

Access Introduction Training Manual 2.13 ~ Sorting and Filtering Data analysis can be performed on tables, queries, and/or forms. To analyze data, display the table in Datasheet View. To assist you with data analysis, when a table is displaying in Datasheet View, the Ribbon is equipped with a section titled Sort & Filter in the Home tab:

Also, when a table or a query displays in Datasheet View, each column header displays a down pointing arrow button on its right side:

ÂŠ Sarah Mason

Access Introduction Training Manual 2.14 ~ Sort the records in a table Sorting records consists of rearranging them in alphabetical, incremental, chronological, or Boolean order. Sorting can be performed on fields that have numbers, strings, date, time, or Boolean values. Fields can also not display any value but must be sorted also. To sort records, you must specify the field that would be used as the reference. To do this, on the table or query, click a field under the column of your choice. To arrange the list in alphabetical, incremental, chronological, or Boolean order, on the table or query, click the column header or a field under the column. Then, in the Sort & Filter section of the Ribbon, click the Ascending button Descending button. You can also right-click to sort the list.

. Or

Removing the Sorting In most cases, you should "unsort" a list before continuing. To put the list back in the sequence it previously had: In the Sort & Filter section of the Ribbon, click the Clear All Sorts button Right-click the query or form and click Clear All Sorts Exercise: Sort the PRICE column into ascending order

ÂŠ Sarah Mason

Access Introduction Training Manual 2.15 ~ Filtering Empty Fields So far, we were just selecting the necessary fields for our data analysis. In some circumstances, you may want to set a limit on the number of records to display or make available to the user. To do this, you must create a rule and ask Microsoft Access to apply it to a set of records. The rule works like a funnel that decides what to let through and what to retain. The rule is also called a criterion. For example, you can set a criterion that asks a query to consider the list of all students in a school but to restrict the list only to female students. A filter is a criterion or a set of criteria that must be applied to a set of records to create a list of records that abide by a common rule. Filters can be used to isolate records on a table, a query, a form, or a report. There are differences on the way each type of value handles it. To filter records that display on a data sheet, first decide what column to use. Then: To get a list of records where the fields are empty, you can: Right-click the column and click Equals Blank Click any value under the column. In the Sort & Filter section of the Ribbon, click Selection and click Equals Blank After clicking, the table or query would display only the records that are empty. To get a list of records where the fields are not empty, you can: Right-click the column and click Does Not Equal Blank Click any value under the column. In the Sort & Filter section of the Ribbon, click Selection and click Does Not Equal Blank After clicking, the table or query would display only the records that are not empty for that particular column.

ÂŠ Sarah Mason

Access Introduction Training Manual Removing a Filter When a table, a query, or a form is filtered, in the Sort & Filter section of the Ribbon, the Toggle Filter button is highlighted

. Also, the bottom section of the table,

query, or form displays a Filtered button . If you perform another filter on the list, only the selected records would be considered. This means you must decide whether you want the new filtering to apply to all records or only to the new ones. If you want to use all records of the list, you must first remove the previous filter. To dismiss the previous filtering operation: In the Sort & Filter section of the Ribbon, if the window is large enough to show the Toggle Filter button, then click it. If the width used by Microsoft Access is not large enough, then the Sort & Filter section would be equipped with the Remove Filter button . Right-click the table, query, or form and click Clear Filter From ... Click the down-pointing button on the right side of the column name and click the Clear Filter From option. In the bottom section of the table, query, or form, click the Filtered button

ÂŠ Sarah Mason

Access Introduction Training Manual Exercise: Using the PRODUCTS table, show all products that need re-ordering.

Perform a second filter on this data to show all products that need re-ordering over ÂŁ100

ÂŠ Sarah Mason

Access Introduction Training Manual

Click on TOGGLE FILTER to show all records again Exercise 2 Select an occurrence of the DAIRY FARM and choose SELECTION from the SORT & FILTER section

Select ‘equals Dairy Farm’ option

Remove the filter

Access Introduction Training Manual Further Practise Exercise 1 COLLEAGUES Create a new database to contain staff records for a small company. Name the database Colleagues and the table Staff Records. Create the following fields in Design View, leave the descriptions blank.

FIELD NAME

DATA TYPE

FIELD SIZE/FORMAT

AutoNumber

Surname

Text

First Name

Text

Position

Text

Start Date

Date/Time

Short Date

NI Number

Text

Salary

Currency

Date of Birth

Date/Time

Short Date

Details for members of staff are shown below. Enter records into the database only for those staff with a start date after 1989. The order in which the records are entered is not important as the AutoNumber field will be completed automatically.

ÂŠ Sarah Mason

Access Introduction Training Manual

Change the width of the columns to fit the data entered. Close the table, saving if prompted.

ÂŠ Sarah Mason

Access Introduction Training Manual Further Practise Exercise 2 STOCK Create a new database named Stock. Create the following fields in Design View (add relevant descriptions if you wish): FIELD NAME

DATA TYPE

FIELD SIZE/FORMAT

Ref

Number

Long Integer

Item

Text

Bought by

Text

Supplier

Text

Price

Currency

Last Ordered

Date/Time

Short Date

Stock

Number

Long Integer

Save the table as Stationery. Enter the records below, using the following supplier codes to replace the supplier names: JS - Jones & Sons SC - Saracen PU - Pencils r Us

Reduce the width of the Ref column. Widen the Item column to display all the text. Close the table, saving the design. Close the database.

ÂŠ Sarah Mason

Access Introduction Training Manual Chapter 3 ~ Relationships 3.1 ~ Understand the difference between flat file and relational First we are going to create a simple flat file database and then analyse its limitations. We can then restructure the database to establish how the problems can be overcome. Exercise: Open a new blank database called Sports Activities.

Step 1: Design the table as shown below:

Step 2: Test the table with some sample data. The problems with this table are instantly apparent: You can enter the same name for different students There is no space for those students taking a third activity The database needs to be restricting entries so that data-entry error is minimized If we wanted to use the data to write to the students in a mail merge, they would always be known as for example John Smith instead of Dear John or Dear Mr Smith ÂŠ Sarah Mason

Access Introduction Training Manual Step 3: Analyse the data. In this case, we can see a glaring problem in the first field. We have two John Smiths, and there's no way to tell them apart. We need to find a way to identify each student uniquely. Step 4: Modify the design. We can identify each student uniquely by giving each one a unique ID, a new field that we add, called Student ID. We scrap the Student field and substitute an ID field.

While it's easy for the computer to keep track of ID codes, it's not so useful for humans. So we're going to introduce a second table that lists each ID and the student it belongs to. Using a database program, we can create both table structures and then link them by the common field, ID. We've now turned our initial flat-file design into a relational database: a database containing multiple tables linked together by key fields. If you were using a database program that can't handle relational databases, you'd basically be stuck with our first design and all its attendant problems. With a relational database program, you can create as many tables as your data structure requires.

ÂŠ Sarah Mason

Access Introduction Training Manual Create a second table listing ID and student name as shown:

This table has separated out the name fields to allow different queries such as “How many Women play rugby” and to allow a mail merge. We can see that there are two John Smiths in the table but the date of birth will identify the correct ‘John’. This STUDENT ID needs to be a Unique identifier for the table or PRIMARY KEY. You can tell the field to become a PRIMARY KEY if it hasn’t already by switching into DESIGN VIEW

You can see the relationship that you have created between the two tables by clicking on the DATABASE tab and selecting RELATIONSHIPS from the Relationships section Add both the tables from the dialog box Click and drag STUDENT ID From the COURSE table into STUDENT ID from the STUDENT table. An EDIT RELATIONSHIPS dialog box will appear explaining that this relationship is a 1:1. The means that STUDENT ID field will appear only once in the courses table and once in the STUDENT table and allow no duplicates.

Access Introduction Training Manual Step 6: Analyse the data. There's still a lot wrong with the Courses table: Wasted space. Some students don't take a second activity, and so we're wasting space when we store the data. It doesn't seem much of a bother in this sample, but what if we're dealing with thousands of records? Addition anomalies. What if student 7 wants to do a third activity? College rules allow it, but there's no space in this structure for another activity. We can't add another record for Student 7 as that would violate the unique key field ID, and it would also make it difficult to see all his information at once. Redundant data entry. If the swimming fees go up to ÂŁ30, we have to go through every record containing swimming and modify the cost. Querying difficulties. It's difficult to find all people doing swimming: we have to search through Activity 1 and Activity 2 to make sure we catch them all. Redundant information. If 50 students take swimming, we have to type in both the activity and its cost each time. Inconsistent data. It is possible for data input error to occur with the prices being typed in each time. Step 7: Modify the design. We can fix the first four problems by creating a separate record for each activity a student takes, instead of one record for all the activities a student takes. First we eliminate the Activity 2 and Price 2 fields. Then we need to adjust the table structure so we can enter multiple records for each student. To do that, we redefine the key so that it consists of two fields, ID and Activity. As each student can only take an activity once, this combination gives us a unique key for each record.

ÂŠ Sarah Mason

Access Introduction Training Manual Create a third table to show just activities as shown below:

Enter the data into the table COURSES

Step 8: Analyse the results. No redundant or repeated information. You need only list each activity once in the Activity table. No inconsistent data. There's only one place where you can enter the price of each activity, so there's no chance of creating inconsistent data. Also, if there's a fee rise, all you need to do is update the cost in one place. No insertion anomalies. You can add a new activity to the Activities table without a student signing up for it. No deletion anomalies.

ÂŠ Sarah Mason

Access Introduction Training Manual In order to view the data, you now have to create a query to show for example all John Smiths activities: Here you can see a query showing all three linked tables in the DESIGN VIEW of a query. This screen shot shows the DATASHEET view showing the actual data pieced together from the three tables

A summary of the design process Although your ultimate design will depend on the complexity of your data, each time you design a database, make sure you do the following: Break composite fields down into constituent parts. Example: Name becomes lastname and firstname. Create a key field which uniquely identifies each record. Eliminate repeating groups of fields. Example: If your table contains fields Location 1, Location 2, Location 3 containing similar data, it's a warning sign that you need separate tables.

ÂŠ Sarah Mason

Access Introduction Training Manual Chapter 4 ~ Forms Data entry of a database is mainly performed on forms as they provide a friendlier display of information than tables. They usually show one record at a time and may comprise of text boxes, drop down lists, combo boxes, images. 4.1 ~ Create a form using the wizard There are various ways you can create a form. To automatically generate a form, in the Navigation Pane, select the table. On the ribbon, click Create. In the Forms section, click Form. Using the Products database, create a form using the wizard. To launch the Form Wizard, on the Ribbon, click Create. In the Forms section, click More Forms, and click Form Wizard. On the first page, select the table or query that holds the desired fields:

ÂŠ Sarah Mason

Access Introduction Training Manual On the 2nd page of the wizard, select the layout of the form:

On the 3rd page, select a design:

ÂŠ Sarah Mason

Access Introduction Training Manual On the 4th page, name the form:

ÂŠ Sarah Mason

Access Introduction Training Manual 4.2 ~ Form Layout There are various ways you can make a form appear: Columnar, Datasheet, Tabular, Justified: A columnar form is used to display data one record at a time:

A Datasheet form looks and behaves like a table, displaying all possible records at the same time instead of one record at a time

To create a tabular form, use the Form Wizard and select its option. Otherwise, you can design a form from scratch. Then, in the Format or the All tabs of the Properties window, set its Default View to Continuous Form

ÂŠ Sarah Mason

Access Introduction Training Manual A Justified form provides a consistent look with borders added to labels

A split form is made of two sections:

ÂŠ Sarah Mason

Access Introduction Training Manual The Sections of a Form A form can display one to three different sections when in Design View. To add the sections, you can: Right-click the middle of the form and click Form Header/Footer On the ribbon, you can click Arrange and, in the Show/Hide section, click the Form Header/Footer button A form can also be equipped with two other sections. To get them, you can: Right-click the middle of the form and click Page Header/Footer In the Show/Hide section of the Arrange tab of the ribbon, you can click the Page Header/Footer button A form is equipped with special horizontal lines used to visually separate sections of a form. To equip a form with dividing lines, add a header and a footer sections.

ÂŠ Sarah Mason

Access Introduction Training Manual 4.2 ~ Navigate through records using form view In the bottom section, a form is equipped with navigation buttons used to perform a specific role each: Button

ÂŠ Sarah Mason

Name

Role

First Record

Allows moving to the first record of the table

Previous Record

Allows you to move one record back (if there is one) from the current record

Current Record

Displays the number representing the current record out of the total number of records

Next Record

Allows moving you one record ahead

Last Record

Allows moving you to the last record of the table

New (Blank) Record

Used to enter a new record on the table

Access Introduction Training Manual 4.3 ~ Add a background colour In all forms we have created so far, unless using the Form Wizard or occupying it with a picture, the body of the form was painted with a white colour. If you do not enjoy white forms, you can set the background to a colour of your choice. Unlike the picture, the form does not control its background colour. This aspect is left to each section to manage. Before specifying the colour of a form, first click or select the intended section. To change the background colour of a section: On the ribbon, click Home and, in the Font section, select a colour from the Fill/Back Colour button Right-click a section, position the mouse on Fill/Back Colour and click the desired colour

On the ribbon, click Design and, in the Font section, select a colour from the Fill/Back Colour button Access the Properties window of the section. In the Format or the All tab, click the Back Colour field. If you click the arrow of the property, you can select a familiar colour from the list. Otherwise, you can click the browse button. This would display a list of colours similar to that of the Font/Fore Colour window Unlike the Fill/Back Colour (and the Font Colour) of the Font sections of the Home and the Design categories of the ribbon, when you right-click a section of a form and position the mouse on Fill/Back Colour, the colours that display do not show their tool tip, which would indicate their names. Because the layout of colours is the same as the Fill/Back Colour and the Font Colour windows of the Font sections of the Home and the Design categories of the ribbon, we will use their names.

ÂŠ Sarah Mason

Access Introduction Training Manual 4.4 ~ Add special effects Microsoft Access provides some special visual effects used to raise or sink, etc a section of a form or report, a label or a field. These effects can be controlled by using the Special Effect field in the Design tab and the Controls section.

4.5 ~ Sorting in a form Data analysis consists of examining the values stored in an object such as a form. To assist you with data analysis, when a form is opened is Form View; the Ribbon is equipped with a section titled Sort & Filter in the Home tab:

You can sort the records of a form in Form View, in Layout View, in Tabular View, in Justified View, or in Split View To sort records on a form, you must specify the field that would be used as the reference. To do this: Right-click the control (or its accompanying label) that holds the values and click Sort A to Z Click the control or its label. Then, in the Sort & Filter section of the Ribbon, click the Ascending button

ÂŠ Sarah Mason

Access Introduction Training Manual To sort the records in reverse alphabetical, incremental, or chronological: Right-click the control (or its accompanying label) that holds the values and click Sort Z to A Click the control or its label. Then, in the Sort & Filter section of the Ribbon, click the Descending button Removing the Sorting In most cases, you should "unsort" a list before continuing. To put the list back in the sequence it previously had: In the Sort & Filter section of the Ribbon, click the Clear All Sorts button Right-click the query or form and click Clear All Sorts 4.6 ~ Filter by selection and excluding selection The filter by selection tool works in a similar method to the filter in the tables section. Highlight the field in which you wish to filter

Ensure the form is showing in Datasheet view and choose the Selection option from the sort and filter section

ÂŠ Sarah Mason

Access Introduction Training Manual The data is now filtered.

On the same selection menu, there are four options you can choose from:

4.7 ~ Removing a filter Click on the Filter funnel and choose Clear the filter:

ÂŠ Sarah Mason

Access Introduction Training Manual Chapter 5 ~ Queries 5.1 ~ Create a simple query using the wizard A Query is used in a database to obtain specific data from the database tables and to display only the data requested. Queries are similar in design and application to filters, in that they can select which records to display based on criteria but, where filters display all fields in a record, a query can also select which fields from the table to display. Queries can be designed so that the criteria are very narrow, i.e. the query, when applied to a table or group of tables, will display only very specific results. One of the most useful features of a Query is that once it has been created, it can be saved and used again at any time. Queries can be thought of as selective views of the data contained in tables, and in many situations queries can be used in exactly the same manner as tables. A report for example may be based on either a table or a query. A common scenario is to create a query to extract the required data from a table, then present the information by creating a report based on the query. Simple queries can be created using a wizard or manually in Design View, but Design View must be used to create selection criteria and to make use of more advanced features. Many different queries can be created for one table. Query results are returned in the form of a table, so if the results are to be printed, the column widths must be checked to ensure that all of the data can be viewed.

ÂŠ Sarah Mason

Access Introduction Training Manual 5.2 ~ The Query Wizard The simplest way to create a query is by using the Query Wizard. It presents a list of tables and queries you can select from the current database. Open the database called Chemistry Lab To use the Query Wizard, on the Ribbon, you can click the Create tab and, in the Other section, click Query Wizard. This would display the New Query dialog box:

Choose Simple Query Wizard and click OK. Choose the Elements table from where the data can be found. Choose the fields that you require the query to display.

On the next page, spcify DETAIL

ÂŠ Sarah Mason

Access Introduction Training Manual On the last dialog box, specify the name for the query qry Elements

The query should be displayed as shown below:

ÂŠ Sarah Mason

Access Introduction Training Manual 5.3 ~ Introduction to Query Design Staying with the Chemistry Lab database, click in the Other section of the Create tab of the Ribbon, and choose Query Design This would display the Show Table dialog box

Choose the Stock table, add then close. If the Show Tables dialog box is closed or for any reason you want to display it: In the Query Setup section of the Design tab of the Ribbon, you can click the Show Table button You can right-click anywhere on the query window and click Show Table... When a query is displaying in Design View, the Design tab of the Ribbon displays the buttons used for a query:

ÂŠ Sarah Mason

Access Introduction Training Manual 5.3 ~ The Query Window The Query window allows you to design and manage various aspects of a query. You can right-click the title bar of the Query window to access a menu:

One of the operations you can perform on the Query window consists of resizing its top and bottom sections by dragging the splitter bar up or down:

5.4 ~ Selecting the Columns To create the fields for a query, you use the table(s) or query (queries) displayed in the upper section of the window. To select one field from the list, just click it To select many fields on the same range, you can click one of them, press and hold Shift. Then click one field on the other end of the desired range To select fields at random, click one of the desired fields, press and hold Ctrl; then click each one of the desired fields To select all fields, you can click the * line on the list of fields

ÂŠ Sarah Mason

Access Introduction Training Manual 5.5 ~ To Add Columns To make a field participate in a query, you have various options: Once you have made your selection on the list in the top part of the query window, you can drag it and drop it in the bottom section of the query window

You can also select more than one field and drag them:

Instead of dragging a field or all fields, you can either double-click a field to add it to the query, or double-click the line with * to add all fields to the query In the bottom part of the query window, click an empty Field box to show a combo box. Then click the arrow of that combo box and select an item from the list:

ÂŠ Sarah Mason

Access Introduction Training Manual 5.6 ~ Executing a Query To execute a query: If the query is currently closed, from the Navigation Pane: You can double-click it You can right-click it and click Open If the query is already opened and it is in Design View, on the Ribbon:

You can click the Run button

You can click the View button and click Datasheet View

or you can click the arrow of the View button

5.7 ~ Selecting a Column Some operations require that you select a column from the bottom section of the query window: To select a field in the lower section of the view, click the tiny bar of the column header:

The whole column will be selected To select a range of columns, click the column header of one at one end, press and hold Shift, then click the column header at the other end

ÂŠ Sarah Mason

Access Introduction Training Manual 5.8 ~ Removing a Column From a Query As seen above, a query is built by selecting columns from the originating list and adding them. If you do not need a column anymore on a query, which happens regularly during data analysis, you can either delete it or replace it with another column: To delete a column: Once it is selected, you can press Delete Right-click the column header and click Cut To delete a group of columns, select them and press Delete 5.9 ~ Replacing a Column To replace a column, click the arrow on the combo box that displays its name and select a different field from the list:

5.10 ~ Moving a Column Columns on a query are positioned incrementally as they are added to it. If you do not like the arrangement, you can move them and apply any sequence of your choice. Before moving a column or a group of columns, you must first select it. Then: To move a field, click its column header once. Click it again and hold your mouse down, and drag in the direction on your choice

To move a group of columns, first select the group and then proceed as if it were one column

ÂŠ Sarah Mason

Access Introduction Training Manual 5.11 ~ Data Entry on a Query You can perform data entry on a query. To do this, display it in Datasheet View, click the desired cells and type the information as necessary. 5.12 ~ Query Printing You can print the record of a query. To do this: Right-click the query from the Navigation Pane and click Print Open the query or select it in the Navigation Pane, then use the Office Button to access the Print menu 5.13 ~ Sort records You can put the records into either Ascending order or Descending by selecting the SORT row in Design View and choosing the required option. If you wish to sort by two or more fields, the query will perform a Primary Sort on the field furthermost left and then a secondary sort on fields to the right.

ÂŠ Sarah Mason

Access Introduction Training Manual Exercise: Create a standard query Open the Wineshop database, create a new query in design view using the data from the SALES table

Show the DATE, Product Ref and Quantity fields in the query. Search for all Chianti

Do not show the Chianti field in the results. Remove the tick from the check box on the SHOW row.

Close the query, click Yes at the prompt to display the Save As dialog box. Enter qry Chianti Sales as the Query Name.

ÂŠ Sarah Mason

Access Introduction Training Manual 5.14 ~ Using operators and wildcard characters Open the Chemistry database and create a simple query using the data from the stock table. If you need to search for one specific item, type the keyword into the Criteria row under the correct column heading

If you need to search for items in a particular category for example all Substances that begin with the letter C, you add a C* into the criteria row. The * is referred to as a wildcard character. The * could also be placed at the start of the search criteria which would search for for example all people who live in the Bristol area if the data showed: Clevedon, Bristol; Portishead, Bristol; Nailsea,Bristol. *Bristol would find all these towns.

You could also use an Operator to search for all Substances that begin with C or A eg Like “C” OR like “A”

Access Introduction Training Manual Exercise: Using wildcards Open the Properties database. Create a new query in Design View based on the Commercial table. Place the Town/City, Address, Type of Premises, Price and Comment fields into the query grid.

We need to search for properties where landscaping is a feature mentioned in the Comment field. The text may appear in a variety of forms however, e.g. landscaped, landscaping, and may be in the middle of other text. In the criteria row for the Comment field, type *landscap*. This is enough of the word to find all required occurrences because the wildcard characters, (*), denote that any amount of text may occur before or after the selected characters. Press <Enter> and note that Access changes the criteria to Like “*landscap*”, which is the code it uses to perform a wildcard search. Run the query. Records are found with a variety of relevant Comment fields.

Close the query and save a qry landscaped and close the Properties database.

Access Introduction Training Manual Exercise: Multiple selection criteria Using Design View, create a new query based on the Sales table in the Wineshop database. You are required to display records for all sales to the Darlington area that have not yet paid. Drag or double click the fields Surname, First Name, Address, City, Paid and Date, into the query grid, in that order. Note: Remember, a query will display fields in the order they appear in the grid. To change the order in the grid select the whole column and drag it to the required position. In the Criteria field for City, enter Darlington and in the Criteria field for Paid enter No. Make sure both entries are on the same horizontal row.

Run the query. Because both criteria were entered on the same row the query only selects records where the City is Darlington AND the Paid field is No. Note the order of the columns in the query results. There are 3 records. Switch back to Design View. Delete the No value from the Criteria row in the Paid column and type it into the cell beneath, the or row (this is still a Criteria field).

Run the query. Because both criteria are now on different rows the query selects records where either the City is Darlington OR the Paid field is No. There should be more records. 15 records result.

Close the query and save as qry Darlington data , then close the Wineshop database. ÂŠ Sarah Mason

Access Introduction Training Manual 5.15 ~ Include a numeric condition in a query When numerical values are used in tables, queries can be performed based on ranges of values, e.g. age less than 25. Other options are

less than

greater than

greater than or equal to

less than or equal to

not equal to

Exercise: Use a range of values in a query Open the Properties database and create a new query in Design View based on the Commercial table. Place the Town/City, Type of Premises, Price, Unit Area, Lift and Parking Spaces fields into the query grid.

Position the cursor in the criteria field for Price and type <50000 then run the query. All properties with a Price of less than £50,000 are displayed (there are 4 properties). Note: £ signs do not need to be added when using range of values queries. Only the number is entered into the criteria field. Check that all data is fully displayed in the query. Enlarge the column width for Parking Spaces so that the whole header can be seen. Switch back to Design View for the query. Change the criteria for Price to <=50000 (less than or equal to £50000) and run the query. There are ten records selected this time because now prices of exactly £50,000 are included. © Sarah Mason

Access Introduction Training Manual Click

, to save the query. Save as qry 50000.

Return to Design View and remove the criteria from the Price field, and in the criteria for Parking Spaces type >= 50 (greater than or equal to 50). Run the query. All properties that have 50 or more parking spaces are displayed. In Design View, remove the parking spaces criteria and in the criteria for Price type <=75000 (less than or equal to ÂŁ75000), in Unit Area type >200 and in the criteria for Lift type Yes. All criteria should be on the same row.

Run the query. Two premises match all 3 criteria. When creating one query from another Save As is used to save the new query, otherwise the first query is overwritten and lost. Select Windows Button | Save As|Save Object As.

The Save As dialog box is displayed.

ÂŠ Sarah Mason

Access Introduction Training Manual

Change the name for the new query to Selective and leave the As box as Query. Click OK. Close the query. The Properties Database Window should now have two queries available, Cheap and Selective. Click Tables in the Objects Bar and open the Commercial table. Change the price of the first record (Portrack Park) from £50000 to £60000 then close the table. Click Queries in the Objects Bar and double click the query 50000. The saved query 50000 runs, but now shows a different number of records, nine, because the data in the table has changed. Save and close the query.

Access Introduction Training Manual 5.16 ~ Include a calculated field in a field Using the Chemistry database this query demonstrates how to calculate in a query. Queries can perform calculations very efficiently. By typing Cost:

(this defines the name of the field)

[Units in Stock]

(this refers to an existing field)

*10

(this will multiply by 10)

By right clicking on the field and choosing Properties, you can specify the format of the outcome. In this case Currency.

Exercise: Create a calculated query Open the Wineshop database. Create a new query in Design View based on the Sales table. Place the Date, Product Ref, Price, Quantity and Paid fields into the query grid.

ÂŠ Sarah Mason

Access Introduction Training Manual Click anywhere in the Paid column and select Insert | Columns to place a new blank column between Quantity and Paid.

In the Field row of this new column enter the following expression: Sales Value: [Price]*[Quantity]

Change the properties to display the result in currency format. Save the query as Total Price. Close the query and the Wineshop database.

ÂŠ Sarah Mason

Access Introduction Training Manual Exercise: Using Current date in queries Open the Staff database and create a new query in Design View based on the Staff List table. Place the Surname, Position and Start Date fields into the query grid. In the criteria field for the Start Date field, add today’s date. Click out of the criteria field. The date changes to #dd/mm/yy#. The # sign indicates the format used to perform queries based on date.

Position the cursor in front of the left # and type <. Position the cursor after the last # and type –3650. Run the query.

Note: The query uses current date minus (–) 3650 days as a selection criteria, so the result displays any individuals who started their employment more than approximately 10 years ago. (10 x 365) Run the query Close the query and save as shown:

Access Introduction Training Manual Exercise: Using AND in a query Open the Wineshop database and create a new query in Design View based on the Sales table. Place the Date, Product Ref, Quantity, Surname, First Name and Address fields into the query grid. In the Quantity field, type in the criteria >=10 and <=20. Run the query. All sales of between 10 and 20 bottles are displayed. Return to Design View and remove the criteria for the Quantity field. Range selection works equally well on dates. In the Date field, type in the criteria >=01/07/99 and <=31/07/99 and press <Enter>. Access puts # symbols around the date fields to identify them. Run the query to display all sales made during the month of July in 1999. Combinations of And criteria can be applied. Return to Design View. Add the original criteria for the Quantity field (>=10 and <=20) without removing the Date criteria and run the query. The display now shows all sales of between 10 and 20 bottles made during the month of July in 1999. There are 2 records.

Close the query saving as qry July Sales. Close the Wineshop database.

ÂŠ Sarah Mason

Access Introduction Training Manual Exercise: Using OR in a query Open the Properties database. Create a new query in Design View based on the Commercial table. Place the Town/City, Type of Premises and Price fields into the query grid. In the criteria field for Town/City, type in Sunderland Or Newcastle and run the query. All properties, in either Newcastle or Sunderland are listed.

Return to Design View and delete Or “Newcastle” from the criteria field. Type Newcastle in the cell underneath Sunderland, (on the or line). This is an alternative method of running an Or query.

Run the query. The results should be identical. If separate rows are used to enter Or conditions, care must be taken when combining these with other criteria. Return to Design View, maximise the query design window and type Durham in the cell underneath Newcastle. There are now three Criteria rows with entries. Type in <80000 in each of these three rows for the Price column.

Access Introduction Training Manual Run the query. All properties in Sunderland, Newcastle and Durham costing less than £80000 are displayed. Note: Each separate Criteria row across the Query Grid represents a separate selection definition. The price criteria had to be entered on each row so that it was applied to properties from Sunderland, Newcastle or Durham. Return to Design View and remove all data from the query grid. Place the Town/City, Type of Premises, Price, Parking Spaces and Lift into the query grid. In the Town/City criteria type in Sunderland. In the or: criteria field for Type of Premises type Office Premises. In the or: criteria field for Parking Spaces type >40 (the same line as “Office Premises”) and in the or: criteria field for lift type Yes. Note: Selecting Criteria on the same line forces the query to only select those properties, which satisfy ALL of the criteria. Placing them on separate lines allows for any property encompassing any one of the criteria to be displayed.

Run the query. This query has two lines of criteria. The first selects all properties in Sunderland. The second has three criteria and all three must be satisfied to produce a selection. So the query will display ALL Sunderland properties, together with any office premises which have more than 40 parking spaces and have a lift, no matter what their location. Note: Combining criteria in the query grid enables quite complex selections to be defined. It is a good idea to always run queries and check that the records included do match the intended selection conditions, before relying on the query to present information. Close the query saving as qry OR example. Close the Properties database.

Access Introduction Training Manual Chapter 6 ~ Reports 6.1 ~ Create a report using a wizard A report provides an object used to print a database's records. Although you can print tables or forms, reports are customized to be printer friendly. They can perform and display calculations. Once again, Microsoft Access provides wizards to quickly create reports. Microsoft Access can help you quickly create a report using one of the pre-designed layouts. This is done using the Report Wizard. The Report Wizard is available from the Reports section of the Create category of the ribbon. Data on fields of forms and reports fall in three main categories: fields that directly originate from a table or a query, fields created as a combination of existing fields, and fields independent of any other fields. The techniques used to create these fields are different but a field created using one technique can be changed into another category

ÂŠ Sarah Mason

Access Introduction Training Manual Exercise: Create a report using AutoReport An AutoReport can be generated directly from a single table or query in the database. However, AutoReport applies only limited (if any) formatting levels to the report. If further formatting is required to improve the quality of the printed report, Design View must be used. Using the Household Plants database, select the Plants Index table, then in the Reports section, click Report.

View the report in Print Preview and note the formatting which has been applied with AutoReport.

Save the report as AutoReport1, then close the report.

ÂŠ Sarah Mason

Access Introduction Training Manual

Exercise: Create a report using the Wizard

Using the Household Plants database, click Report Wizard in the Reports section of the Create tab.

Choose Plant Index from the drop down list if not already selected.

This step allows you to choose which fields will appear on the report. From the Available Fields area, select Common Name and then click Selected Fields on the right.

to move it to

Do the same for Height (cm) and Position.

ÂŠ Sarah Mason

Access Introduction Training Manual Do you want to include any grouping levels? Choose Position. This will sort the data into small groups of the position to place the plant.

Click Next. Sort the data into ascending order of Common Name.

ÂŠ Sarah Mason

Access Introduction Training Manual

Click Stepped layout in Portrait orientation. Make sure the Adjust the field width check box is checked.

Click Next, then select a style from the menu and click Next.

ÂŠ Sarah Mason

Access Introduction Training Manual

Add the title rpt Plant Index and click Finish to complete and preview the report.

Use the Magnifying Glass to zoom in and out of the report to see the data. The layout is not too good but this can be changed. Close the report, saving if prompted. Close the Household Plants database.

ÂŠ Sarah Mason

Access Introduction Training Manual 6.2 ~ The Design View One of the ways you can display a report is called the Design View. From the Navigation Pane, you can right-click it and click Design View If the report is already opened, in the Views section of the ribbon, you can click the arrow button under View and click Design View If the report is already opened, you can right-click its tab or its title bar and click Design View As done for a form, in the Design View of a report, you can add, position, format, configure, and manipulate the necessary controls. The Design View is equipped with one or more sections. The primary sections are the Page Header, the Detail, and the Page Footer:

As mentioned when studying form and report design, when a report is in Design View, the Ribbon is equipped with a Controls section in its Design tab. You can use those controls to populate your report. You can also select objects from the Field List and add them to the report.

ÂŠ Sarah Mason

Access Introduction Training Manual 6.3 ~ Moving Fields and Labels in Design View Preview the rpt Plant Report from the database Household Plants. This was created earlier using a report wizard. The Position heading is not fully displayed.

Click the Design View button,

The Position field (in the Position Header) and Position label (in the Page Header) needs to be moved to the right. The two fields are automatically linked ie if you resize one the other will move automatically. If you move the cursor over the right hand side it will change shape to a resize cursor.

Hold down the SHIFT key and it will only resize the Position fields. ÂŠ Sarah Mason

Access Introduction Training Manual Click Preview to check on the progress. Change back to Design View. Change the heading to Report of plants grouped by position.

Preview the result and Save the report using the same name and close it. Close the database.

ÂŠ Sarah Mason

Access Introduction Training Manual 6.4 ~ Formatting Text in Reports To make reports more interesting and eye catching, they can be formatted in various ways. The simplest is to change the font, its size and its attributes. Exercise: The Plant Report is still on screen. While this report is fine, it can be improved. Select the Field headings in the Page Header section.

The label boxes now are highlighted in orange. Use the drop down font list to change the font of the title to Arial Black.

Similarly, use the drop down Font Size list to change the size to 22pt. If all of the title is not visible in the text box, use the TO FIT option from the SIZE tab.

ÂŠ Sarah Mason

Access Introduction Training Manual 6.5 ~ The Print Preview To have an idea of what a report would look like on a printed piece of paper, you can display it in what is referred to as Print Preview. To do this: If you create a report using the Report Wizard, it would automatically display in Print Preview In the Navigation Pane, you can right-click a report and click Print Preview If the report is already opened, you can right-click its tab or its title bar and click Print Preview When a report appears in Print Preview, the Ribbon is made of only one tab. To appear realistic, a report in print preview appears as a piece of paper with margins. Its body is filled with the data that would be printed. Here is an example:

The right side and the bottom-right side display a scroll bar each. After using the Print Preview, to close it, in the Close Preview section of the ribbon, you can click the Close Print Preview button. This would display the report in the view it previously had.

ÂŠ Sarah Mason

Access Introduction Training Manual 6.6 ~ The Page Setup of a Report

Introduction Page setup consists of customizing some of the behind-the-scenes aspects of a piece of paper resulting from printing. These include the size of a report, the orientation, and others. To assist you with this, when the report is displayed in Print Preview, the Ribbon is equipped with a section labelled Page Layout:

The Page Layout section is equipped with a button labelled Page Setup. When clicked, this button would open the Page Setup dialog box that provides some of the options of the Page Layout section and more. The Size of a Report Like a form, a report has a size, which is the combination of its width and its height. When it comes to the height, each section has and controls its own vertical measure. As done for a form, to specify the height of a section: You can drag up or down the bottom border of the bar of the next section

ÂŠ Sarah Mason

Access Introduction Training Manual You can access the Properties window of that section and change the value of the Height property As seen for a form, the height that a report displays in Design View is the total height of its sections. When it comes to the width of a report, all sections use the same measure. The width that a report shows in Design View is the common width of its sections. Therefore, to specify the width of a report: You can drag left or right the right border of any section

You can access the Properties window of the form and change the value of the Width property To change both the height and the width of the report: You can drag left, up, right, or down the bottom-right corner of the lowest section

You can access the Properties window of the report then change the values of both the Height and the Width fields

ÂŠ Sarah Mason

Access Introduction Training Manual The Page Size of a Report

As seen previously, a report is meant to print on a piece of paper whose size is 21cm x 29.7cm. If you want, you can change that size. To do this, while a report is displaying in Design View, you can click the Page Setup tab of the Ribbon and click the Size button:

ÂŠ Sarah Mason

Access Introduction Training Manual Chapter 7 ~ Manipulating the database 7.1 ~ Copy a database object To copy a database, in the database window, select more to view the OPEN window. Select the database to copy Right click on the database and choose COPY from the shortcut menu.

Right click into ‘space’ and choose the paste option from the shortcut menu A copy of the database appears in the list with ‘–copy’ at the end.

7.2 ~ Rename a database object To rename a database, stay in the OPEN dialog box. Select the object that is to be renamed. Select the rename option on the shortcut menu or press F2

Type in the new name and press enter. 7.3 ~ Delete a database object To delete a database object In the database window select the object that is to be deleted Select the delete option on the shortcut menu or press DEL. In the prompt box that appear, click on the Yes button to confirm the operation © Sarah Mason

Access Introduction Training Manual 7.4 ~ Compact and repair a database If you delete objects and/or data from an Access database, the database file may become fragmented on the disk on which it is stored. This can result in slower program response when performing operation on that database. Also, if an Access database is damaged in some way, you may receive unpredictable output when working in that database. To deal with the above problems, Access provides a utility that both compacts a database (defragments the database file) and repairs any damage to the database in one operation. To compact and repair a database (that is open) If the database is shared make sure that no other user has it open Choose the Start Button, Manage and then Compact and repair.

ÂŠ Sarah Mason

Access Introduction Training Manual 7.5 ~ Backup a database To backup a database: Open the database and choose the start button. On the manage section choose Backup Database.

Optionally enter a new name for the backup file and/or a new location for storing the file. (The default filename I the current filename with the current date appended: the default storage location is the current database folder.

ÂŠ Sarah Mason

Access Introduction Training Manual Chapter 8 ~ Working with other applications 8.1 ~ Import spreadsheet data into an Access table Procedure: Earlier exercises have demonstrated how to type in data to a database table. If large quantities of data are involved this may be a tedious process. It is often the case however that the data already exists in some other location, in another database or in an Excel spreadsheet for example, and fortunately it is possible to Import data from many such external sources. This allows much greater flexibility for data entry. Most systems however (including Excel) have the ability to export data in a universally accepted simple text format, with each field of data separated from the next by a ‘delimiter’, often a comma. If a comma is used, the file is known as a ‘Comma Separated Variable’ type with a .csv file extension. All such delimited files can then be easily imported to Access tables. The database must exist before importing can take place but the table itself can be created by the import process. Exercise: Create a new database called BAKERS Click on the External Data tab and within the Import group click on the Excel button. This will open the Get External Data – Excel File dialog box, as illustrated.

Click on the Browse button. Select the folder containing your sample files. Select your Excel file, in this case a file called sick. Click on the Open button.

Access Introduction Training Manual

Click on the OK button to move to the next dialog box, as illustrated here.

You will see the following. Ensure the check box is ticked â&#x20AC;&#x2DC;First row contains column headings. Click NEXT.

ÂŠ Sarah Mason

Access Introduction Training Manual You will see the following. In this case do not make any changes. Access is offering to add a primary key field which would be sensible. Click NEXT.

Click on the Next button. Type in the name Absences.

ÂŠ Sarah Mason

Access Introduction Training Manual Click on the Finish button. You will see the following dialog box.

Click on the Close button without making any changes. You will see your Absences table displayed within the All Tables list, as illustrated.

Open up your Absences table and it will look like this.

ÂŠ Sarah Mason

Access Introduction Training Manual

Further Practise Exercise Project Create a new database called Project Import the Excel file called Project Add a primary key field Save as tbl Project

ÂŠ Sarah Mason

Access Introduction Training Manual 7.2 ~ Import text into an Access table Procedure Data can be imported from the delimited text files where each field is separated by a character (e.g. comma, or tab), or fixed-width text files where each field has a fixed width. This source file must have the same type of data in each field and the same fields in every row. Exercise: Create a new database called Bikestyle

Click on the External Data tab and within the Import group click on the Text File button.

This will open the Get External Data - Text File dialog box. Click on the Browse button. Change to the folder containing your sample files. ÂŠ Sarah Mason

Access Introduction Training Manual Select your text file, in this case a file called salary. Click on the Open button. Within the dialog box displayed select whether to import data into a new table, append in a table, or link the data source.

In this example select the first option to import data into a new table. Click on the OK button. In this dialog box you can select the delimited file option or fixed width file option. In this case the select Delimited file option.

ÂŠ Sarah Mason

Access Introduction Training Manual Click on the Next button. You will see the following dialog box. This text file is comma separated, so make sure that Comma is selected, as illustrated.

Click on the Next button. You will see the dialog box illustrated below. In this case accept the defaults.

ÂŠ Sarah Mason

Access Introduction Training Manual Click on the Next button and you will see the following. Accept the default values, as illustrated.

Click on the Next button and you will see the following. Accept the default value, as illustrated.

ÂŠ Sarah Mason

100

Access Introduction Training Manual Click on the Finish button. You will see the following.

Click on the Close button without making any changes to this dialog box. You will see the new table displayed at the bottom of the All Tables list, as illustrated.

If you open the table, it will look like this.

Close the table before continuing.

ÂŠ Sarah Mason

101

Access Introduction Training Manual Further Practise Exercise Membership Create a blank database and save it as Membership. Import the CSV format file from your user area. Once complete, open the table to view the data (there are 220 records).

Close the table, saving if prompted, then close the database.

ÂŠ Sarah Mason

102

Access Introduction Training Manual Adding/Deleting Fields ............................ 17

Modules ........................................................ 9

Backup a database ................................. 91

Navigate ..................................................... 24

Command tabs ...........................................5 Compact and repair a database ......... 90 Contextual command tabs .......................5 Copy Database ........................................ 89

Pages ............................................................ 9 Primary Key ................................................. 38

Delete Database ...................................... 89 Designing ......................................................8 Enter Records ............................................ 13 Field size ..................................................... 15 Filter by selection ...................................... 51 Filtering ....................................................... 27 Finding Data .............................................. 25 flat file ......................................................... 36 Form Background ..................................... 49 Form Layout ............................................... 45 Form special effects ................................. 50 Form Wizard ............................................... 42 Forms .............................................................8 Forms Sorting ............................................. 50 Galleries ........................................................5

Queries .......................................................... 8 Query calculated field ............................. 69 Query dates ............................................... 71 Query Design ............................................. 56 Query numeric condition ......................... 66 Query Operators........................................ 63 Query Sort ................................................... 61 Query wildcard .......................................... 63 Query Wizard ............................................. 53 Quick Access Toolbar ................................. 5 Relationships............................................... 36 Rename Database ................................... 89 Renaming a Column................................. 21 REPLACE ...................................................... 13 Report ~ AutoReport ................................. 76 Report ~ Page Setup ................................ 86 Report Design View ................................... 81 Report Wizard.......................................75, 77 Reports .......................................................... 9 Ribbon ........................................................... 5

Hiding a column ....................................... 22 Sorting ......................................................... 27 Import spreadsheet data ........................ 92 Import text ................................................. 97 Macros ..........................................................9

ÂŠ Sarah Mason

Table Design............................................... 11 table Wizard ............................................... 10 Tables ............................................................ 8

103