Access Advanced notes 2007 by Sarah Mason

Advanced Access Training Manual

Advanced 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

Advanced Access 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 ~ Covering the basics using 2007 .................................................................. 8 Chapter 3 ~ Tables .............................................................................................................. 12 3.1 ~ Templates............................................................................................................12 3.2 ~ Adding Fields ......................................................................................................14 3.3 ~ Creating Tables ..................................................................................................15 3.4 ~ Field Templates ..................................................................................................16 3.5 ~ Create a lookup field .........................................................................................17 3.6 ~ Validating Field entries .....................................................................................18 3.7 ~ Creating a Custom Input Mask ........................................................................22 3.8 ~ Adding images to tables ...................................................................................30 Chapter 4 ~ Relationships .................................................................................................. 32 4.1 ~ Create & modify a one to one relationship ....................................................32 4.2 ~ Creating a Many-to-Many Relationship between Tables .............................34 4.3 ~ Apply inner, outer & self joins .........................................................................41 Chapter 5 ~ Forms ............................................................................................................... 48 5.1 ~ Creating a Master-Detail Form ........................................................................48 5.2 ~ Adding a picture to a form ...............................................................................52 5.3 ~ Displaying the Current Date on a Form .........................................................58 5.4 ~ Modifying Form Properties ...............................................................................60 5.5 ~ Create bound, unbound or calculated controls .............................................63 5.6 ~ Create and edit a combo box, list box, check box, option groups ............66 5.7 ~ Set sequential order of controls on a form ...................................................72 5.8 ~ Insert data field to appear within form headers/ footers on the 1st page/all pages ...............................................................................................................75 5.9 ~ Conditional Formatting .....................................................................................76 5.10 ~ Creating an Advanced Filter ..........................................................................77 Chapter 6 ~ Queries ............................................................................................................ 78 6.1 ~ Including Multiple Conditions in a Query .......................................................78 6.3 ~ Creating an Action Query .................................................................................83 6.4 ~ Create a Make Table query ..............................................................................84 6.5 ~ Create an Update query ...................................................................................86 6.6 ~ Create an Append query ...................................................................................88 ÂŠ Sarah Mason

Advanced Access Training Manual 6.7 ~ Create a Delete query .......................................................................................90 6.8 ~ Create a Crosstab query ...................................................................................91 6.9 ~ Group information in a query using the MAX, MIN and AVG function ......93 6.10 ~ Group information in a query using the SUM function ..............................94 6.11 ~ Grouping information in a query using the COUNT function....................95 6.12 ~ Create a logical (condition based) expression within a query .................96 6.13 ~ Show lowest range of values in a query......................................................98 6.14 ~ Finding the highest range of values within a query ..................................99 6.15 ~ Refining queries using NULL values ...........................................................100 6.16 ~ Finding NOT values .......................................................................................101 Chapter 7 ~ Reports .......................................................................................................... 102 7.1 ~ Create a grouped report using design view ................................................103 7.2 ~ Creating Master-Detail Report .......................................................................106 7.3 ~ Modifying Report Properties ...........................................................................108 7.4 ~ Calculated Fields ..............................................................................................109 7.5 ~ Calculate percentage calculations .................................................................112 7.6 ~ Using formulae in a report: Average ............................................................113 7.7 ~ Adding a Cover Sheet to a Report ................................................................115 7.8 ~ Create running summaries .............................................................................116 7.9 ~ Add a line number for each record in a report or group ...........................117 Chapter 8 ~ Macros ........................................................................................................... 118 8.1 ~ What is a Macro? .............................................................................................118 8.2 ~ Creating a Macro ..............................................................................................118 8.3 ~ Creating a Command Button .........................................................................121 8.4 ~ Add an image to a button or other control .................................................122 8.5 ~ Writing an Event procedure ...........................................................................123 Chapter 9 ~ Advanced Procedures................................................................................. 125 9.1 ~ Setting Database Startup Options ................................................................125 9.2 ~ Making a Backup Copy ....................................................................................127 9.3 ~ Opening Databases Exclusively .....................................................................128 9.4 ~ Encrypting Databases and Adding Passwords ............................................129 9.5 ~ Splitting Databases and Linked Tables ........................................................130 9.6 ~ ACCDE Format: Security for Programmable Objects.................................131 9.7 ~ Creating a Switchboard ..................................................................................132 Chapter 10 Working with other applications ................................................................ 134 10.1 ~ Exporting Access data to an XML Document/Importing an XML Document into Access ................................................................................................134 Exercise: To import an XML document into Access:.............................................136

ÂŠ Sarah Mason

Advanced Access 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 advanced level. Objectives After completing the exercises the user will have experience in the following areas: Explore the new 2007 environment Tables – use the validation rules, custom input masks and advanced filters Forms – create sub form, add images, display dates, modify form properties, create bound and unbound controls, create and edit combo boxes etc, create logical expressions, set sequential order Queries – create parameter queries, action queries, group information, use functions, logical expressions, refine queries with NOT and NULL Reports – create master details report, modify properties, calculate percentages, use formulae, running sums, force page breaks Relationships – create one to one, joins, auto delete Macros – create & run, command button, event procedure Working with other applications – import/export access data as an xml file

Advanced Access 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.

Advanced Access 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

Advanced Access 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

Advanced Access Training Manual Chapter 2 ~ Covering the basics using 2007 Procedure Moving around the 2007 Access environment is quite different to the previous version so the following exercise covers basic database skills including making and editing tables, queries, forms and reports. Filtering the table by selection, Grouping reports Using the Navigation pane Exercise

Open a new blank database called sparrow From the EXTERNAL DATA tab choose IMPORT EXCEL

ÂŠ Sarah Mason

Advanced Access Training Manual Locate the file called Sparrow Company 2007.xls

Import the source data into a new table in the current database Follow the import wizard through and ensure the primary key is the REC NO field.

Switch to DESIGN VIEW and change the money fields to CURRENCY

ÂŠ Sarah Mason

Advanced Access Training Manual Sort the county into alphabetical order Filter the list to show only customers who live in AVON

Using the FILTER BY SELECTION find all companies who have HU facilities

Filter the ADULT FEE to show only those customers who charge less than £1.00 Move the FAMILY FEE to appear before the ADULT FEE Change the FONT to ARIAL Create a QUERY to show all customers whose names begin with S

Advanced Access Training Manual Create a report to display the information from the query. Group the information by COUNTY Bring the customers table to the front and create a form from this data.

Explore the NAVIGATION bar to ensure familiarity.

ÂŠ Sarah Mason

Advanced Access Training Manual Chapter 3 ~ Tables 3.1 ~ Templates Office Access 2007 includes a suite of professionally designed database templates for tracking contacts, tasks, events, students, and assets, among other types of data. Use the Getting Started with Microsoft Office Access page, which appears each time that you start Office Access 2007, and quickly get started creating your database by opening one of the templates.

Each template is a complete tracking application that contains predefined tables, forms, reports, queries, macros, and relationships. Exercise Download the Contacts Template

ÂŠ Sarah Mason

Advanced Access Training Manual Add your name to the contact list that appears:

Explore the different views of the table

Close the CONTACT LIST From the NAVIGATION PANE, retrieve it again by choosing CONTACT LIST

Explore the other methods of viewing the table data Contact details Contact list Contact address book Contact Phone list

ÂŠ Sarah Mason

Advanced Access Training Manual 3.2 ~ Adding Fields Procedure The existing fields in the TABLE TEMPLATE can be removed and added to very simply. The fields will SNAP TO the existing fields and copy the properties with data type, length. Exercise In DESIGN VIEW of the contacts list table, delete the unused fields by highlighting the fields and pressing DELETE

In the DESIGN tab, click on ADD EXISTING FIELDS and drag back onto the page the ADDRESS field You will find that the field will SNAP TO the existing fields and copy their formatting.

ÂŠ Sarah Mason

Advanced Access Training Manual 3.3 ~ Creating Tables Procedure: Click Table on the Create tab and start entering data. Office Access 2007 automatically determines the best data type for each field. The Add New Field column shows you where to add a new field — and if you need to change the data type or display format of a new or existing field, it is easy to do so by using the commands on the Ribbon, which is part of the Microsoft Office Fluent user interface. You can also paste data from Microsoft Office Excel tables into a new datasheet — Office Access 2007 creates all of the fields and recognizes the data types automatically. Exercise: From the CREATE tab, click on table Type in your name in the data section. Double click where it says FIELD1 and rename the field to FORENAME and SURNAME

Create another field called Mem No and change the DATA TYPE and FORMATTING to NUMBER

Advanced Access Training Manual 3.4 ~ Field Templates Procedure To save time designing your fields, use the new field templates, which are predefined fields, each with a name, data type, length, and preset properties. You can drag the fields that you need directly from the Field Templates pane to a datasheet. Field templates are based on XML Schema Definition (.xsd) files so that you can set up your own standard definitions for shared use in your department or workgroup. Exercise: In the DATASHEET tab, choose NEW FIELD and from the CONTACTS section choose ADDRESS. Click and drag this field onto your page.

ÂŠ Sarah Mason

Advanced Access Training Manual 3.5 ~ Create a lookup field Procedure Suppose you need to add an existing field from another table to your table. Just drag the field from the Field List pane onto the datasheet. Access automatically creates any needed relationships or prompts you throughout the process. Exercise: From the DATASHEET tab, click on ADD EXISTING FIELDS, a field list will appear with existing fields already set up in other tables. Choose email address and drag onto the datasheet. The wizard is completed for you, click on NEXT and then FINISH. A drop down list now appears in your table.

ÂŠ Sarah Mason

Advanced Access Training Manual 3.6 ~ Validating Field entries Procedure You can simplify data entry, as well as prevent data entry errors, by setting the following field properties when designing a table: ValidationRule This property defines the entries that are allowed in a field. Setting this property can ensure that only valid data is entered into a table. ValidationText This property designates the error message that is displayed when an invalid entry is made in a field. Exercise: Open the BinderInsurance.mdb database Select the SALARY table Choose DESIGN VIEW Ensure the DEPARTMENT field is selected In the FIELD PROPERTIES section, enter the VALIDATION RULE “Personnel” or “Catering” or “Training” In the VALIDATION TEXT section enter Only enter Personnel, Catering or Training Switch back to DATASHEET VIEW. When prompted to save, click on YES

Advanced Access Training Manual Another message will appear as shown below:

This is asking to check all the existing data with the new rules. As this table contains some data that is not in the validation rule, click on NO.

Enter the following NEW RECORDS No 21 22

Surname Jones Humphries

First John Ben

DOB 19/06/66 16/04/55

Department Catering Production

Age 42 53

Basic 900 1100

Note: You are unable to enter the department Production so enter Personnel instead. If you wish to add extra departments, return to the DESIGN VIEW and add to the existing list.

ÂŠ Sarah Mason

Advanced Access Training Manual FURTHER PRACTISE Open the database called SWIMMING CLUB.mdb Open the table called MEMBER and switch to DESIGN VIEW Add a VALIDATION RULE to the CATEGORY field that will allow the following to be entered: Recreational, International, County, Novice, District Add a VALIDATION TEXT to state the entries that are allowed. Enter the following NEW RECORDS, save and close. ID

First Name 111 Brian

Last Name Britain

112 Sandy

Severell

Address

DOB

Tues

Thurs

Category

Coach

Fees

Yes

Age Grp E

123 Old Street BS21 7YT 12 High Street BS1 6YT

23/11/1994

Novice

Louise

12/06/1994

Yes

County

Mary

You can also use the Validation Rule property to required specific values, and the Validation Text property to alert your users to any mistakes. For example, entering a rule such as >100 and <1000 in the Validation Rule property forces users to enter values between 100 and 1000. Exercise: Open the BINDER INSURANCE.mdb database Switch into the DESIGN VIEW of the SALARY table Add a VALIDATION RULE that will only allow a basic salary of greater than £800 and less than £3000 Add an appropriate VALIDATION TEXT Test the new rule by altering one of the figures

Advanced Access Training Manual Exercise:

Open the SWIMMING CLUB.mdb database Switch into the DESIGN VIEW of the MEMBER table Add a VALIDATION RULE that will only allow members in the club who are younger than 18 on the 1st January.

Hint: In the Date of Birth field add the rule that will not accept members who were born before 1/1/1990

ÂŠ Sarah Mason

Advanced Access Training Manual 3.7 ~ Creating a Custom Input Mask Procedure: An input mask is a field template that controls where data is to be entered as well as what kind of data and the number of characters that can be entered into a field on a datasheet (as well as into a text or combo box on a form). It defines the format of that data. This template consists of a series of literal characters (for example, brackets, full stops and/or hyphens), which separate input areas that are to be filled in by the user. You define an input mask by setting the InputMask property of a field. An input mask definition consists of literal characters (as mentioned above) and special characters that determine the actual data that can be entered into the input area. The definition can include up to three sections, separated by semicolons, as described below. Section

Description

First

Contains the actual input mask (see the following table for the characters that can appear in this section)

Second

Determines whether or not literal characters are stored with the input values 0=store literal characters with input values 1 or blank = do not store literal characters with input values

Third

Contains the placeholder character – the character that represents the spaces (in the input area) in which values are to be entered. If this section is left blank, an underscore (_) is used by default. (To specify an actual blank character enter “ “ in this section.

Advanced Access Training Manual The following table describes the characters that can appear in the first section of an input mask definition. Character

Description

Represents a digit (0 – 9) – an entry is required; plus and minus signs are not allowed. Represents a digit or a space – an entry is optional’ plus and minus signs are not allowed Represents a digit or a space – an entry is optional; plus and minus signs are allowed Represents a letter (A – Z) – an entry is required

Represents a letter – an entry is optional

Represents a letter or a digit – an entry is required

Represents a letter or a digit – an entry is optional

Represents any character or a space – an entry is required

Represents any character or a space – an entry is optional

.,:;-/

Represents a decimal placeholder, and a thousands, date and time separator – the characters used depend on regional settings in windows Control Panel Converts all characters that follow to lowercase

Converts all characters that follow to uppercase

Displays the input mask right-to-left; characters typed, however, fill the field left-to-right. Displays the character that follows as a literal character

9 #

You can also set the InputMask property to Password. In this case, any character entered into the associated field is stored as that character but appears as an asterisk (*). In addition to entering an input mask definition manually, you can use the Input Mask Wizard to create and enter the definition for you. This wizard, however, can be used only with Text and Date/Time fields and is limited to defining masks for common types of entries (for example, telephone numbers and post codes.)

Advanced Access Training Manual Exercise Open the exercise Courses and open the table Course in design view Insert a new row below the site row called POSTCODE Select TEXT as the DATA TYPE

In the General properties area, click on the INPUT MASK row and then the BUILD area and choose POSTAL CODE from the INPUT MASK WIZARD. Follow the wizard through to the finish. Test out the mask by typing in the postcodes as follows

Site

Postcode

Congresbury

BS49 5EY

Churchill

BS25 5PQ

Devon

TN22 9AW

Cheddar

BS27 3NF

Remember the keyboard shortcut CTRL + ‘ will copy the data from the same field in the previous record.

Advanced Access Training Manual Further Practise Continuing to use the COURSES database, switch into DESIGN VIEW and set up an INPUT MASK that will display the COURSE CODE as follows:

ABD 202 0199 NB

The first three characters are Capitals Letters Entry required

There will always be the same number of letters

Spaces are required in three places in the course code

Two numbers are required on the end

Set up a telephone field that will display the phone numbers as follows: (01275) 846377

But will also allow a Bristol phone number

(0117) 9500 233

In order to save space within the database, it has been decided to only allow the SITE field to display as two characters. Amend the INPUT mask to allow only two capital letters in this field.

ÂŠ Sarah Mason

SITE

CODE

Congresbury

Devon

Churchill

Cheddar

Advanced Access Training Manual Exercise: Add an input mask to a query Create a query to show only the COURSE CODE, SITE, POSTCODE, DATE, MAX NO, and ENROLLED

In the Navigation Pane, switch to Design View on the shortcut menu. In the query design grid, place the pointer in the next blank columns and create a calculated field to work out AVAILABLE PLACES Available Places:[MAX NO]-[ENROLLED] Press F4 to open the property sheet for the field. Under Field Properties, on the General tab, click the Input Mask property box,. Click the Input Mask field and type your input mask definition. ##” PLACES” RUN the query to check the data

Advanced Access Training Manual Exercise: Add an input mask to a control on a form or report

Create a report from the query grouping the data by SITE Switch to DESIGN VIEW and create a CALCULATED CONTROL to work out total revenue per course. To do this click on the AB text box tool

Draw the new text box in the DETAIL section

Type in the formula to work out the NUMBER OF ENROLLED * £55 PER COURSE

Right click on the CALCULATED CONTROL box and choose PROPERTIES The property sheet for the control appears. On the All tab, click the Input Mask property box, and then do one of the following: Click the Input Mask field and type your input mask definition to read £350.00 total course revenue

Advanced Access Training Manual Answers: Course Code in table answer

Telephone Number Answer

Site code answer

ÂŠ Sarah Mason

Advanced Access Training Manual Query Input Mask answer

Report Input Mask Answer

ÂŠ Sarah Mason

Advanced Access Training Manual 3.8 ~ Adding images to tables Access provides two options for adding images to tables: You can embed the images directly or you can add links to the images. Both processes follow these broad steps: Add an OLE Object field to an existing table In the AUCTION Database window, select the table, and then click Design In the first blank row in Design view, under Field Name, type Image. Click the next field (the field in the Data Type column), click the arrow that appears, and then click OLE Object in the list. Save the table. Switch to Datasheet view Right-click the first field in the Image column of the table, and then click Insert Object on the shortcut menu. Select Create from File, and then click Browse. Browse to the course folder and find one of the four .bmp files. Select the first image (image fozzie) and then click OK. To link to an image, click Link, and then click OK again to complete the process. To embed the image in the table, leave the Link check box blank and click OK. The words "Bitmap Image" appear in the first field of the Image column in the table. Note If "Package" appears in the OLE Object field, you're trying to link to or embed an unsupported graphics file, such as a GIF or a JPEG. If you need to display those types of files, you can reinstall Microsoft Photo Editor.

ÂŠ Sarah Mason

Advanced Access Training Manual The picture once clicked on will open in the package that created itâ&#x20AC;Ś..

ÂŠ Sarah Mason

Advanced Access Training Manual Chapter 4 ~ Relationships A relational database can best be described as a set of data tables, each modelling a single entity and have certain “key” fields in common with other tables. These key fields establish the relational link between the data tables. Each table in a relational database must have a unique identifier (primary key). The first type of relationships is a one-to-many which was discussed in the Intermediate course. The other two are detailed below: 4.1 ~ Create & modify a one to one relationship In this relationship, a record in the primary (parent) table has a single corresponding record in the related (child) table. For example, you may have a table containing employee addresses and phone numbers, while another table has employee salary information. There should be one record in the primary table related to EXACTLY one record in the related table. The type of join is the least used because the data from the two tables could easily be combined into one bigger table. A good reason for using it could be security. In the above example, the company may want to keep the salary information confidential and only certain personnel can be given access to it. Exercise: Open the database name Auction From the DATABASE TOOLS menu choose RELATIONSHIPS

Check the SALES and the SALARY tables are showing, if not choose ADD TABLE and drag them onto the window. Click and drag SALES PERSONID from the SALES table to the SALARY table Tick the check box to ENFORCE REFERENTIAL INTEGRITY Check the RELATIONSHIP TYPE states One-To-One Click on OK

Advanced Access Training Manual

NOTE: Referential Integrity means that an entry cannot be made in one table unless there is a corresponding entry in the related table. Add a new salesperson to the SALES table and the related data in the SALARY table Name

Payscale

Commission

Date of joining

Nicola Newhouse

LU998765R

35000

25/06/08

Simon Brewer

NE454332W

30000

25/06/08

Note: Since the relationship was defined, the SALES table now shows a + symbol to the left of the record. If this is selected the related SALARY table can now been seen and the NI number and the SALARY data can be added at the same time as the new employee.

ÂŠ Sarah Mason

Advanced Access Training Manual 4.2 ~ Creating a Many-to-Many Relationship between Tables (Acknowledgement to databasedev.co.uk for this exercise)

In Access, a many-to-many relationship between two tables is one in which a record in the first table can have any number of matching records in the second table, and a record in the second table can have any number of matching records in the first table. To define such a relationship, you create one-to-many relations between each of the above tables and a third (junction) table. The primary key of the junction table should be a combination of the primary keys of the other two tables. Exercise In order to create a many to many relationship between the data in the ALBUMS database you first need to create the junction table to show the following information: Album

Artist

Heathen Chemistry

U2 Oasis

Reloaded Volume 4

U2 Sterophonics

The Joshua Tree

Now thatâ&#x20AC;&#x2122;s what I call music!

Kylie Minogue Moby Stereophonics

Fever

Kylie Minogue

The Best Bands

Oasis Moby

The Eminem Show

Eminem

The Marshall Mathers LP

Eminem

Forget about Dre

Eminem Dr Dre

Open the ALBUMS database

ÂŠ Sarah Mason

Advanced Access Training Manual Create a NEW TABLE in DESIGN VIEW

Create the first field called ALBUM ID with the DATA TYPE ….NUMBER. Use the LOOKUP WIZARD to find the information from the ALBUM TABLE STEP 1:I want the lookup column to look up the column in a table or query, NEXT

STEP 2: Choose ALBUM TABLE, NEXT

Advanced Access Training Manual STEP 3: Choose ALBUM ID and ALBUMNAME, Next

Choose ASCENDING order of ALBUM NAME, NEXT

Advanced Access Training Manual Choose to SHOW KEY COLUMN and adjust column widths, NEXT

Choose to store the ALBUM ID in the database, NEXT

ÂŠ Sarah Mason

Advanced Access Training Manual Accept the Default name of the this lookup as ALBUM ID and FINISH

Save the table as LINK TABLE Enter a second FIELD NAME of ARTIST ID and use another lookup table to find the values as before. Enter the values as shown at the beginning of the exercise. Make BOTH fields a primary key. (Highlight both rows and click on the KEY ) This makes a COMPOSITE KEY table. NOTE: A composite field is a group of fields that uniquely identify a record. While duplicate values are allowed within any of the files of a composite key, there cannot be duplicated values across all the fields that make up the composite key. Once completed have a look at the RELATIONSHIPS window and ENFORCE REFERENTIAL INTEGRITY for the joins.

ÂŠ Sarah Mason

Advanced Access Training Manual Now create a query in DESIGN VIEW

Run the query

ÂŠ Sarah Mason

Advanced Access Training Manual Further Practise

Open the database PITTS GARAGE Create a LINK TABLE to join the OWNER table with the CAR table. Use a lookup wizard to enter the data.

Create a query to display who owns which car.

ÂŠ Sarah Mason

Advanced Access Training Manual 4.3 ~ Apply inner, outer & self joins You can change the type of join so that Microsoft Access selects all the records from one table or query whether or not it has matching records in the other table or query. Join properties can be changes by selecting and then double-clicking on the join line. The Venn diagram below shows the three different variations will show different results.

List of courses Option 2 outer join to the left

list of bookings Option 1 Inner join

Option 3 outer join to the right

The most common join is the INNER JOIN where both tables have matching data. This is the option that the JOIN PROPERTIES dialog box defaults to if nothing else is selected.

Exercise:

Open the EMPLOYEE DATA database and then open the JOIN EXAMPLE query and practise with the different combinations

ÂŠ Sarah Mason

Advanced Access Training Manual Option 1 This is referred to as an INNER JOIN. By choosing this option you are answering the question: Show all courses listed that have students enrolled on them.

Note: The student BROWN 116 only appears twice in this list and actually he appears three times in the right hand table (Training Details) but on one occasion does not have a course against his name

There is also a PUBLISHER course that does not appear from the left hand table. This is because no-one is enrolled on it.

ÂŠ Sarah Mason

Advanced Access Training Manual Creating Outer Joins An outer join is one where all the records in one table are included in the dynaset regardless of whether there are matching records in the other table. For example, suppose you're dealing with Northwind's Customers and Orders tables, which are related on the common CustomerID field. An inner join between these tables shows only those customers who have placed orders. By contrast, an outer join on the Customers table displays all the records from that table, even customers who have never placed an order. There are two types of outer joins: Left outer join—This join displays all the records from the "left" table. For example, in tables with a one-to-many relationship, the left outer join displays all the records from the "one" table. Right outer join—This join displays all the records from the "right" table. For example, in tables with a one-to-many relationship, the left outer join displays all the records from the "many" table.

Advanced Access Training Manual Option 2

This is referred to as an OUTER LEFT JOIN. By choosing this option you are answering the question: Show every course that has been arranged but may not have any students.

NOTE: You can see here that all courses are showing including the PUBLISHER course which doesnâ&#x20AC;&#x2122;t have any students.

ÂŠ Sarah Mason

Advanced Access Training Manual Option 3

This is referred to as an OUTER RIGHT JOIN. By choosing this option you are answering the question: Show every single student that has been enrolled but only show the courses that have enrolments.

NOTE: You can see here that student BROWN 116 appears 3 times including the entry where he has enrolled on a course that doesnâ&#x20AC;&#x2122;t exist. The PUBLISHER course does not show as no-one is enrolled on it.

ÂŠ Sarah Mason

Advanced Access Training Manual Creating Self Joins Database tables are sometimes self-referential, which means they contain a field with data that points to another field in the same table. A good example is the Northwind Employees table, which includes an EmployeeID field, the primary key that contains the employee identification numbers. Employees also contains the ReportsTo field, which contains the identification number of the person each employee reports to. In other words, each value in the ReportsTo field will have a corresponding value in the EmployeeID field. If you want to know, for example, which employees have people reporting to them, you need to create a self-join—a table joined to itself—on the Employees table. Creating a self-join involves the following steps: Start a new query and add the table (Employees in this case) twice. Create a temporary join by clicking and dragging the field that contains the data (EmployeeID) to the field that contains the subset of the data (ReportsTo). Add the fields you want to use for the query to the design grid and then set up your criteria, sorting, and other query elements. For a self-join to work properly, you need to tell Access to return only unique values in the query. To do this, click an empty spot inside the query design window and then choose View, Properties (or press Alt+Enter). In the Query Properties window, click Yes in the Unique Values list and then close the window.

Advanced Access Training Manual 4.4 ~ Apply automatic deletion of related records For relationships in which referential integrity is enforced, you can specify whether you Access to automatically “cascade” delete and update operations for related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. If you select the CASCADE DELETE RELATED RECORDS, any time you delete records in the primary table, Access automatically deletes related records in the related table. Exercise Open the database auction Open the relationships window Edit the relationship between the CUSTOMERS and the ITEMS tables Tick the check box to CASCADE DELETE RELATED RECORDS

Save and close the relationship window. Open the CUSTOMER table and delete record number 1 ANDERSON Note the message about deleting related data Open the ITEMS database and note the DELETED message for all items bought by ANDERSON.

Advanced Access Training Manual Chapter 5 ~ Forms 5.1 ~ Creating a Master-Detail Form Procedure A master-detail form is actually two forms – a main form, which displays information from one table, and a subform, which displays information from a second related table. (This format, in fact, is often referred to as a mainform/subform.) You can create a master-detail form by using either the Autoform option in the New Object list or the form wizard. (To use the Autoform option, the relevant tables must be related.) From design view, you can also create such a form “from scratch” by using the Subform/Subreport button in the Toolbox. (This method is demonstrated in the following exercise.) Exercise Open the database AUCTION Use the FORM WIZARD to create a simple form from the CUSTOMERS table. Use all the fields

Advanced Access Training Manual Switch into DESIGN VIEW and click the SUBFORM/SUBREPORT icon

A wizard is opened, click on USE EXISTING TABLES AND QUERIES, NEXT

Choose TABLE: ITEMS. Jump over the ITEMS ID, ITEM NAME, SALE PRICE, DATE SOLD. NEXT

Advanced Access Training Manual Choose SHOW ITEMS FOR EACH RECORD IN CUSTOMERS USING CUSTOMER ID, NEXT

Name the report as given

Adjust the controls and view the data. Note how if you move through the main data of customers, you can view in the sub-form the items that they have bought.

ÂŠ Sarah Mason

Advanced Access Training Manual Further practice

Create a form and subform for the AUCTION database. Create the main form to show the sales people and the sub form to show the items that they have sold.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.2 ~ Adding a picture to a form Procedure: Access provides a special container, called an image control, in which you can display a picture. To add a picture to a form: With the previous form displayed in design view, click on the Image button in the toolbox.

Create an image control by dragging the mouse pointer. In the Insert Picture dialog box, which is displayed when you release the mouse button, specify the picture filename. Click on the OK button. After you add the image to the form, click the SizeMode property and select one of the following values. Setting

Description

Clip

Displays the picture at actual size. If the picture is larger than your form window, Access cuts off the image.

Stretch

Sizes the picture to fit the form window. This setting may distort the image.

Zoom

Maintains the correct proportionality of the image regardless of how you size the form. This setting won't clip the picture or distort its proportions.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.3 ~ Add a picture as a watermark When you add a background image or watermark, the other controls on your form sit on top of the image. For an example of a background image on a form, start the Northwind Traders sample database and open the Customers form. Open the CUSTOMERS form in the AUCTION database that you want to change in Design view. In the Database window, select the form or report to which you want to add a background image, and then click Design report in Design view.

. Access opens the form or

Double-click the form selector or the report selector sheet for the form or report.

to open the property

On the Format tab, in the Picture property box, click the Build button use the Insert Picture dialog box to locate your image.

and

In the PictureType property box, specify whether you want to embed the image or link to the image.

After you add the image to the form, click the SizeMode property and select one of the following values:

ÂŠ Sarah Mason

Advanced Access Training Manual Setting

Description

Clip

Displays the picture at actual size. If the picture is larger than your form window, Access cuts off the image.

Stretch

Sizes the picture to fit the form window. This setting may distort the image.

Zoom

Maintains the correct proportionality of the image regardless of how you size the from. This setting won't clip the picture or distort its proportions.

If you want to change the alignment of the picture, click one of the settings in the PictureAlignment property box. Note If you want to centre the background picture on a form, and you want the background picture to resize when you size the form or report window, click the Centre setting. If you don't want the background picture to resize when you size the window, click the Form Centre setting.

If you want to tile (repeat) the picture across the background of the form or report, set the PictureTiling property to Yes, and set the SizeMode property to Clip. Tiling starts at the position that is specified for the PictureAlignment property. Note The background picture will not tile if you set the SizeMode property to Zoom or Stretch.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.4 ~ Display bound images (images that change with each database record or report page) Procedure Access provides a number of ways to display bound images â&#x20AC;&#x201D; images that change as you move through the records in a database or the pages in a report. You can: Embed your images in a database table and display them by using the bound object frame. This method consumes the most space, but if your database needs to travel, it ensures that your images are always available. Store links to your images in a database table and use the bound object frame to display them. This requires less space, but if your images move or become corrupt, the links break. In addition, these first two methods require you to store your image data in an OLE Object field. That limits you to using .bmp or .dib files, unless you want to install additional software. However, these first two methods are easier to implement because you can use the tools and screens that Access provides. Note You can use VBA code to programmatically store GIF and JPEG files in an OLE Object field, and then display those files in the image control. Use VBA code to programmatically set properties for the image control. This method uses a minimal amount of space because you only store image file names and path information in a text field in one of your tables. It also supports more types of graphics files because you use the image control instead of the bound object frame. However, keep in mind that implementing this solution requires some programming experience. Exercise: Open the CUSTOMERS table in the AUCTION database and check that the image field has been correctly added (as described on page 30) Save any changes and close the table. Open the CUSTOMERS FORM and switch into DESIGN VIEW Choose the BOUND OBJECT FRAME Click and drag the frame onto the main sheet

ÂŠ Sarah Mason

Advanced Access Training Manual Right click on the BOUND OBJECT FRAME and choose PROPERTIES From the DATA menu on the PROPERTY SHEET, click on CONTROL SOURCE and then click BUILD … In the EXPRESSION BUILDER, select the TABLES, CUSTOMERS, IMAGES, OK

Save the form and check in DATASHEET view that the images change with each record

Advanced Access Training Manual 5.5 ~ Make images read-only Procedure: To prevent users from editing your images, you can: Lock the bound or unbound object frame. This prevents users from starting the graphics program used to create an image. Convert an unbound object frame to an image control, which also prevents users from starting the graphics program used to create an image. Exercise : Lock a bound or unbound object frame In the Database window, select the CUSTOMERS form from the last exercise, and then click Design

Right click on the object frame and then click Properties on the shortcut menu. Set the Locked property to Yes and the Enabled property to No.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.3 ~ Displaying the Current Date on a Form Procedure: You can also display the current date on a form by inserting a text box control and by entering the Now function into the control, as demonstrated in the next exercise. Exercise: Using the SALES form, switch into DESIGN VIEW

From the DESIGN tab, in the CONTROL section, select the DATE TIME option. This automatic control will add the current DATE to the FORM HEADER section using the expression =DATE() or TIME = Time() Selecting the PAGE NUMBERS section will insert the automatic numbering in the PAGE HEADER section.

NOTE: Remember anything that appears in this section will only appear when printed not when viewed on screen.

ÂŠ Sarah Mason

Advanced Access Training Manual Exercise

An alternative method of incorporating the date on a form is to place it manually and use the expression =Date() or =Now() Increase the height of the FORM FOOTER by dragging the bottom of the section downward. Click on the AB text box label and click and drag to draw the box inside the footer section. Click in the TEXT BOX CONTROL and type =Now() Click in the LABEL CONTROL (to the left of the text box). Press Delete Right click the text box control and choose PROPERTIES. In the PROPERTY LIST, click on the FORMAT box. Then expand the option list and click on SHORT DATE. Close the PROPERTIES box and SAVE the form.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.4 ~ Modifying Form Properties Procedure: The properties of a form can be accessed by selecting the top left corner of the form (the black box). The Properties window is organized into various tabs FORMAT, DATA, EVENT, OTHER, ALL. Some of the main property changes are discussed below: The Format Tab

The Caption is what appears on the title bar. Default View permits us to specify the initial format of the form, it is the view with which we visualize the data on opening the form. In this property we can choose between the following values: Single form: shows us just one record per screen according to the design defined. Continuous forms: shows various records (as many as will fit on the actual screen), it generates a copy of the detail section for each record that fits in the window. Datasheet: shows us the records organized into rows and columns as in datasheet view which we already know. The Allow... property indicates which views are available once the form has been opened, those that have their property as Yes will be seen in the views shown in the list of the

button and in the View menu.

The ScrollBars: permits us to define which scroll bars are to be seen in the form when the form is bigger than our computer screen. It can be Vertical Only, Horizontal Only, Both, or Neither (no scrollbar is displayed). Record Selectors: to specify whether the record selectors should appear or not (the record selector is a small box placed to the left of a record in which we can click to select the entire record in Datasheet or Forms view, and appears with a pencil when we are editing the record).

ÂŠ Sarah Mason

Advanced Access Training Manual Navigation Buttons: if the property is established as Yes, the Navigation bar appears in the form view. Dividing lines: if this property is established as Yes, a line separating each record will appear in the form view. This property is usually used when the default view is Continuous forms. The Data Tab Record source: indicates from which table or query to extract the data to be seen in the form. Filter: allows us to put a condition to filter those source records that we want to appear in the form. The filter functions when we click on the apply filter button on the toolbar or the Apply filter/Sort in the Records menu. Order by: indicates the field for which we want to extract the sorted records. If we want to put various ordination fields we need to separate them with. Allow filters: if this property is established as No, the buttons on the toolbar remain deactivated as well as the filter options in the Records menu. Allow Edits: if this property is established as No, no changes can be made to the data appearing in the form, it can only be viewed. Allow deletions: if this property is established as No, records cannot be deleted. Allow additions: if this property is established as No, new records cannot be added. Data entry: if this property is established as Yes, when you open the form the already existing records will not appear but rather a record in blank to start adding new records; eg it can be used when someone needs to introduce data into the form, but not to have access to the existing records.

ÂŠ Sarah Mason

Advanced Access Training Manual The Other Tab

Pop Up: is a pop up form that remains above the other windows. A pop up form can be modal or non modal. Modal: When a form is opened as a modal form, the user needs to close the form in order to be able to click outside of it. Dialogue boxes and messages are normally modals. Cycle: allows us to specify what will happen when we are in the last field of a row and we press the TAB key. If we select the All records value, we go to the next record. If we select the Current Record value, we stay in the record and return to the first field of the record. If we select Current Page, we return to the first field of the page. Exercise

Open the PERSONNEL database. Create a new form using the Personnel table. Choose FIRST NAME, LAST NAME DOB and THURS Change the PROPERTIES of the form as follows Add the CAPTION Blueband Recruitment Thursday Order by Date of Birth (DOB) Filter on load to display Thurs data [Thurs]=”Yes” The default view is continuous form with a dividing line.

Advanced Access Training Manual 5.5 ~ Create bound, unbound or calculated controls Procedure: Three basic type of controls exist Bound Controls for which the source of data is a field in a table or query. After you enter a value into a bound control, that value is updated to the bound tableâ&#x20AC;&#x2122;s current record. Most controls that allow information to be input can be bound controls. Unbound controls that do not have a source of data. These controls retain any values you enter but do not update any field in the table. You can use these controls to display text, or you can use them as graphics or special effects on the form itself. Calculated controls for which the source is an expression instead of a field in a table or a query. These controls are based one expression or calculations. Calculated controls do not update any table fields, so they are also types of unbound controls. A calculated control can take a purchase price and multiply by the local tax to get a final total for example. Exercise Keep the PERSONNEL form open and add ADDRESS field as a BOUND control Choose the AB text box option from the CONTROLS section Click and drag the boxes onto the DESIGN VIEW Right click on the currently UNBOUND control and choose PROPERTIES From the DATA menu, click on CONTROL SOURCE and choose ADDRESS.

ÂŠ Sarah Mason

Advanced Access Training Manual Exercise

Create an UNBOUND label to show your name as the designer on the form in the footer area Click on the LABEL option Click in the FOOTER section Create the box and add the text

Exercise Create a calculated control in the FORM Click on the TEXT BOX AB icon in the CONTROLS section and draw a box in the DETAIL section Change the label to read New Fees Due Change the Control to read =[Fees]*1.1. This will increase the current fees by 10% to inform the personnel of the annual price increase. In the PROPERTIES for this control, change the format to CURRENCY Exercise Create a logical expression in the form by using the AB text box again. This will show all the THURSDAY people who are INTERNATIONAL

ÂŠ Sarah Mason

Advanced Access Training Manual You could also use the EXPRESSION BUILDER to help you create a formula like this Right click on the CONTROL box of the International control and choose BUILD EVENT

Choose EXPRESSION builder, OK Create the expression using the tools available.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.6 ~ Create and edit a combo box, list box, check box, option groups Procedure: Combo boxes initially display a single choice with an arrow next to it that shows all the options available when selected. Combo boxes also enable users to enter information that is not in the list. List box controls display a list of data similar to a pull-down menu, but the list box always stays open. You can select any of the options by moving the cursor to the desired tem and pressing Enter or clicking the item. The value of the item selected then is passed back to the bound field. List boxes have no limit on how many fields or records can be displayed. Check Boxes are used for fields that have Yes/No data types. Also used are radio buttons or option buttons. Option Groups contain several toggle buttons, options buttons or check boxes. These controls work together instead of separately when they are combined inside an option group. Instead of being used with Yes/No data types only, they can be used to select one option from a selection. Only one option in the group can be selected at one time. Try not to exceed four option button, if you need more options it is best to use a list box. Option groups are usually bound to one field or expression; each button has a different value that it passes back to the bound field if it is selected.

ÂŠ Sarah Mason

Advanced Access Training Manual Exercise: Create a combo box

Choose the COMBO box option from the CONTROLS section

Draw the box in the DESIGN VIEW of the FORM. A wizard to guide you setting this up will appear. Choose “I will type in the values that I want”

Type in Yes No Store the value in the field Tues

Amend the label for the combo box to read Tuesday Group

Advanced Access Training Manual Exercise: Create a list box Choose the LIST BOX option A wizard will appear, choose “I will type in the values that I want”

Type in the values shown here

Choose “Store that value in the field CATEGORY

Choose a name for the list and FINISH

Advanced Access Training Manual Exercise: Create Check boxes and Radio Buttons

In DESIGN VIEW experiment with the adding check boxes and OPTION buttons (radio) Enter the label AGE GROUP and the options M A B C D E

Clearly an OPTION GROUP would be more suitable for this data so that only one option could be selected. Exercise: Create a option group Click the Option Group tool on the toolbox and draw the area where the group will be placed on the form with the mouse. The option group wizard dialog box will appear. On the first window, enter labels for the options and click the tab key to enter additional labels. Click Next > when finished typing labels.

ÂŠ Sarah Mason

Advanced Access Training Manual On the next window, select a default value if there is any and click Next >.

Select values for the options and click Next

ÂŠ Sarah Mason

Advanced Access Training Manual Choose what should be done with the value and click Next >.

Choose the type and style of the option group and click Next >.

Type the caption for the option group and click Finish.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.7 ~ Set sequential order of controls on a form Procedure: When there is more than one control on your form, you can set the sequential tab order for them. For example in your form you want to move your cursor focus from start to bottom when you press the tab key on the keyboard.

Exercise: Open the Personnel Form in Design View. You will see the following.

The form is too long at the moment and it would be better if the list box and grouped option fields were moved over the right hand side.

ÂŠ Sarah Mason

Advanced Access Training Manual To view and set the tab order, click on the first control, i.e. click on FirstName.

Within the Tools group click on the Property Sheet button.

In the Tab Index field under the Other tab in the Property Sheet window, you will see that the wizard has preset the value to 0.

Change the value from 0 to 5 Click on the Surname text box on the form and you will see that the wizard has preset the value to 1 in the Tab Index field and so on. Change the value from 1 to 6 Click on the View button and select the Form view. Try pressing the Tab key and you will see that you move form field to field in a different order.

ÂŠ Sarah Mason

Advanced Access Training Manual Now, right click on the FIRSTNAME field and choose TAB. This presents a dialogue box thatâ&#x20AC;&#x2122;s achieves the same but is slightly more efficient. It does rely on you naming the fields appropriately though!

Close and save your changes.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.8 ~ Insert data field to appear within form headers/ footers on the 1st page/all pages Procedure: The form header/footer display in different places to the page header/footer: The form header prints on the first page only above everything else. The form footer prints on the last page only, below the last record, but not necessarily at the bottom of the page. The page header will print on each page above the record(s). On the first page it is below the form header. The page footer prints on each page at the bottom of the page. Exercise Switch to the DESIGN VIEW of the Personnel form Add STRODE SWIMMING CLUB and the DATE to appear on the first page only as a FORM HEADER Add “Designed by Sarah Mason” to appear on the last page only as a FORM FOOTER Add MEMBERS FORM OF THE STRODE SWIMMING CLUB to display on every page as a PAGE HEADER Add the PAGE NUMBERS to display on every page as a PAGE FOOTER

Advanced Access Training Manual 5.9 ~ Conditional Formatting Procedure: Conditional formatting enables you to change the formatting of a control based upon conditions that you have defined. Microsoft Access allows you to set up to three conditions. You can use conditional formatting in Microsoft Access forms and reports. This could flag up either information that has been incorrectly entered or information that is important. Exercise: Select the International field that contains the IF statement. Select the CONDITIONAL FORMATTING icon Make the changes shown below:

Check the data.

ÂŠ Sarah Mason

Advanced Access Training Manual 5.10 ~ Creating an Advanced Filter Procedure: To create a simple filter, you can use the Filter by Selection and Filter by Form methods. To create a more complex filter, you can use the Sort & Filter, Advanced Filter/Sort command, as demonstrated in the next exercise. Exercise: Open the Personnel form in Datasheet view. On the SORT & FILTER section, point to ADVANCED, then Advanced Filter/Sort. Add the fields that you need to specify the values or the other criteria that the filter will use to find records to the design grid. In this example search for all members with a SURNAME beginning with W and who have a DOB greater than 01/01/1990 To specify a sort order, click in the Sort cell for a field, click the arrow, and then select a sort order. Microsoft Access first sorts the leftmost field in the design grid, and then it sorts the next field to the right, and so on. On the Sort & Filter section, click Toggle Filter to view the filter's results.

ÂŠ Sarah Mason

Advanced Access Training Manual Chapter 6 ~ Queries 6.1 ~ Including Multiple Conditions in a Query Procedure: When you include two or more conditions in a query, records are selected on the basis of how those conditions have been entered. When multiple conditions in the same Criteria row, as in the illustration below, a record is selected only when all conditions are satisfied. The criteria, in this case, are called AND conditions.

When multiple conditions appear in different Criteria rows, as in the illustration below, a record is selected if any condition is satisfied. The criteria, in this case, are called OR conditions.

You can combine AND and OR conditions, as in the illustration below.

ÂŠ Sarah Mason

Advanced Access Training Manual Exercise: Open the Database CARS Click on the Create tab and within the Other group click on the Query Design button. Select a table from the Tables tab in the Show Tables dialog box and click on the Add button, i.e. select the CARS table. Click on the Close button. The selected table will be shown in the query grid. Select the desired fields from the Fields drop down menu, i.e. select TYPE, MAKE, MODEL, ENGINE, DATE IN, AUTOMATIC, PRICE Show all cars that are 4x4 petrol vehicles under £10000 Also show all petrol, automatic cars, under £10000 that came into the garage after July 08.

Advanced Access Training Manual 6.2 ~ Creating a Parameter Query Procedure A parameter query is one that prompts the user for selection criteria when it is run. A prompt displayed by a parameter query appears in a box that is defined when the query is first set up. To create a parameter query: With the Query window displayed in Design view, add the necessary fields to the Field row. Enter an operator, along with the desired prompt(s) that is/are to appear when the query is run, into the Criteria row for the appropriate field. (See the examples below.) Repeat the previous two steps, if necessary. The following are examples of parameter query criteria: Between [Enter the first date:] And [Enter the last date:] Entering the above into the Criteria row for a Date field would display two prompt boxes – the first prompting the user for the first date in a date range and the second prompting the user for the last date in the range. The query would then display those records in which the field contained an entry within the specified date range. Like [Enter the first character(s) you wish to find:] & “*” Entering the above into the Criteria row for a Text field would display a single prompt box, prompting the user for one or more characters. The query would then display those records in which the field contained an entry beginning with the specified character(s). Exercise: Creating a parameter query Click on the Create tab and within the Other group click on the Query Design button. Select the required tables from the Tables tab in the Show Tables dialog box and click on the Add button, i.e. add the Cars table. Click on the Close button. The selected table will be shown in the query grid.

Advanced Access Training Manual Select the required fields from the Fields drop down menu, i.e. select Type, Make, Model, Reg, Date In. In the Criteria field of Date In type: Between [Start Date] and [End Date]

Click on the Run button within the Results group in order to execute this query.

Enter the start date and end date when prompted. The resulting query will display cars that arrived in the showroom in June and July this year. Close the query results window and save the query as QryParameter.

ÂŠ Sarah Mason

Advanced Access Training Manual Using wildcards as parameters

A parameter query is a query that prompts users for an input. This is useful for creating generic queries where users can select input options. Click on the Create tab and within the Other group click on the Query Design button. Select the required tables from the Tables tab in the Show Tables dialog box and click on the Add button, i.e. add the Cars table. Click on the Close button. The selected table will be shown in the query grid. Select the desired fields from the Fields drop down menu, i.e. select Make, Model, and any others. Add a wildcard prompt in the Criteria field, i.e. enter Like [Enter the Wildcard Search Criteria] & "*"

Click on the Run button within the Results group on the top menu in order to execute this query. Enter your parameters in the Enter Parameter Value dialog box, i.e. type m

Click on the OK button. Close the Query Design window and save the query QryWildcard.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.3 ~ Creating an Action Query Procedure Action queries are queries that change multiple records in one operation. The following types of action queries are available: Make-Table: This creates a new table from data in one or more tables Update: This type makes global changes to a group of records in one or more tables. Append: This type adds a group of records to the end of one or more tables. Delete: This type deletes a group of records from one of more tables. To create an action query: With the query window displayed in the Design view, look in the Query Type section on the Application window toolbar, and select the type of query you wish to create.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.4 ~ Create a Make Table query Procedure: A make table query retrieves data from one or more tables, and then loads the result set into a new table. That new table can reside in the database that you have open, or you can create it in another database. Typically, you create make table queries when you need to copy or archive data. Loading the data into a separate table and using that table as a data source can reduce workload and provide a convenient data archive. As you proceed, remember that the data in your new table is strictly a snapshot; it has no relationship or connection to its source table or tables. Exercise: Open the database called Tourist Attractions Click on the Create tab and within the Other group click on the Query Design button. Select the Tourist Attractions table from the Tables tab in the Show Tables dialog box and click on the Add button. Click on the Close button. The selected table will be shown in the query grid. Click on the Design tab and within the Query Type group click on the Make Table button. This will open the Make-Table dialog box. Select a table from the Table Name drop down box OR type a new table name. For instance enter Bucks Attractions.

ÂŠ Sarah Mason

Advanced Access Training Manual Click on the OK button. Select the desired table fields from the Field drop down menu, as illustrated below.

Click on the Run button within the Results group on the top menu in order to execute this update query.

Click on Yes to create a table. This will create a new table and include data from the fields selected. Close the Query Design window and save the query as QryMakeTable.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.5 ~ Create an Update query Procedure: You use update queries to add, change, or delete the data in one or more existing records. There are certain restrictions on the type of field that can be updated however, the following cannot be updated: Calculated fields Fields from a totals query or a crosstab query AutoNumber fields Fields in unique-values queries and unique-records queries Fields in a union query Fields that are primary keys Exercise: Click on the Create tab and within the Other group click on the Query Design button. Select the Tourist Attraction table from within the Tables tab in the Show Tables dialog box and click on the Add button. Click on the Close button. The selected table will be shown in the query grid. Click on the Design tab and within the Query Type group click on the Update button.

Select the name and Address fields.

ÂŠ Sarah Mason

Advanced Access Training Manual Click inside the Update To section and in the COUNTY field type Bristol

Click on the Run button within the Results group on the top menu in order to execute this update query. Click on the Yes button to apply changes.

It will update all 4 records in the County field of the Tourist Attraction table and change AVON to BRISTOL. Close the Query window, and click on Yes to save the Query. Close the table. NOTE: Once an update is made to the records, there is no option to Undo or reverse the changes. So be careful when making updates through an update query.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.6 ~ Create an Append query Procedure: An append query adds a set of records (rows) from one or more source tables (or queries) to one or more destination tables. Typically, the source and destination tables reside in the same database, but they don't have to. For example, suppose that you acquire some new customers and a database that contains a table of information about those customers. To avoid entering that new data manually, you can append it to the appropriate table in your database. You can also use append queries to: Append fields that are based on criteria. For example, you might want to append only the names and addresses of customers who have outstanding orders. Append records when some of the fields in one table don't exist in the other table. For example, suppose that your Customers table has 11 fields, and the fields in the Clients table in another database match 9 of your 11 fields. You can use an append query to add only the data in the matching fields and ignore the others. Remember that you cannot use append queries to change the data in individual fields in existing records. To do that type of task, you use an update query â&#x20AC;&#x201D; you can only use append queries to add rows of data. Exercise: Click on the Create tab and within the Other group click on the Query Design button. Select the Append Table from the Tables tab in the Show Tables dialog box and click on the Add button. Click on the Close button. The selected table will be shown in the query grid. Click on the Design tab and within the Query Type group click on the Append button. Select the TOURIST ATTRACTION table from the Table Name drop down box

ÂŠ Sarah Mason

Advanced Access Training Manual Click on the OK button. Select all the fields from the Fields drop down menu.

In the Append To field, select a field from the drop down menu to append the data, such as CustomerID and CompanyName. The data will be appended in this selected field.

Click on the Run button within the Results group on the top menu in order to execute this query. Save and close the query. Double click on the query to action the append. Click on the Yes button to append the data.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.7 ~ Create a Delete query Procedure: A delete query can be used to delete records from a table or group of tables. Exercise: Click on the Create tab and within the Other group click on the Query Design button. Select the Tourist Attraction table from the Tables tab in the Show Tables dialog box and click on the Add button. Click on the Close button. The selected table will be shown in the query grid. Click on the Design tab and within the Query Type group click on the Delete button. Select the COUNTY field from the Field drop down menu, Select Where in the Delete field drop down menu. Delete query requires a ‘where’ condition, otherwise it will delete all the records of the selected field. Type a criteria inside Criteria field, i.e. type fife Click on the Run button within the Results group on the top menu in order to execute this update query. Click on the Yes button to apply changes. It will delete all records in the Customer table where the COUNTY is fife. Close the Query window, and click Yes to save the Query. Enter any name of this query in the Save As dialog box i.e. type QryDelete, and click OK.

Advanced Access Training Manual 6.8 ~ Create a Crosstab query Procedure: A crosstab query presents summary information in a compact format that is similar to a spreadsheet. These types of queries can present a large amount of summary data in a format that is usually simpler to analyse than viewing the information in a database form. Each attribute (field) in a table typically contains a category of data. A crosstab query summarizes the data from one or more of these fields that are separated into groups based on one or more fields. Exercise: Click on the Create tab and within the Other group click on the Query Wizard button.

In the New Query dialog box select Crosstab Query Wizard and click on the OK button.

In the Crosstab Query Wizard dialog box, which appears next, select the Tourist Attraction table, click on the Next button.

ÂŠ Sarah Mason

Advanced Access Training Manual In the next dialog box that asks for a row heading, select a field as a row heading, i.e. select the County and click on the > button.

Click on the Next button. In the next dialog box that asks for a Column heading, select a field as a Column heading, i.e. select TYPE and click on the > button.

Click on the Next button. In the next dialog box that asks for a field value for calculating, select a field i.e. select the Adult Price field and select the AVG Function. Click on the > button.

Click on the Next button. In the next dialog box it asks you to give this crosstab query a name, enter a name i.e. enter “Tourist Attractions Crosstab”.

Click on the Finish button. The query result will be displayed.



Close the Tourist Attraction_Crosstab query.

Advanced Access Training Manual 6.9 ~ Group information in a query using the MAX, MIN and AVG function Procedure: These functions are used in conjunction with the GROUP BY clause. The MAX function will return the maximum value in a set of numeric values in a select query. The MIN will return the minimum value and the AVG will return the average amount. Exercise: Using the TOURIST ATTRACTION database, click on the Create tab and within the Other group click on the Query Design button. Select the Freemason UK Holiday from the Tables tab in the Show Tables dialog box and click on the Add button. This table has been created to calculate how much each attraction would cost for 2 adults, 6 children and 2 senior citizens. Click on the Close button. The selected table will be shown in the query grid. Click on the Design tab and within the Show/Hide group click on the Totals button. Select the COUNTY and TOTAL fields from the Fields drop down menu, In the Total field select Group By from the drop down menu for the COUNTY field. In the Total field select Max from the drop down menu for the desired field. This would calculate the maximum spend for each county. Click on the Run button within the Results group on the top menu in order to execute this query. Experiment with using MIN and AVG with this data Close the Query Design window and save the query as QryMaxMinAvg

ÂŠ Sarah Mason

Advanced Access Training Manual 6.10 ~ Group information in a query using the SUM function Procedure: This function will add up a range of values that are grouped into certain criteria. Exercise: Open the Binder Insurance database. Click on the Create tab and within the Other group click on the Query Design button. Select the Salary tables from the Tables tab in the Show Tables dialog box and click on the Add button. Click on the Close button. The selected table will be shown in the query grid. Click on the Design tab and within the Show/Hide group click on the Totals button. Select the Department and Basic fields from the Fields drop down menu. In the Total field select Group By from the drop down menu for the desired field, i.e. select Group By for the Department. In the Total field select SUM from the drop down menu for the desired field, i.e. select SUM for the Basic field. Click on the Run button within the Results group on the top menu in order to execute this query. Close the Query Design window and save the query as QrySum.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.11 ~ Grouping information in a query using the COUNT function Procedure: The COUNT function will total the records that are specified by the GROUP BY field. In this exercise, it calculates how many customers are in each town. Exercise:

Open the Garden database. Click on the Create tab and within the Other group click on the Query Design button. Select a table from the Tables tab in the Show Tables dialog box and click on the Add button, i.e. select the GARDFILE table. Click on the Close button. The selected table will be shown in the query grid. Click on the Design tab and within the Show/Hide group click on the Totals button.

Select the desired fields from the Fields drop down menu, i.e. select ID and TOWN. In the Total field select count from the drop down menu for ID In the Total field select Group By from the drop down menu for TOWN. Click on the Run button within the Results group on the top menu in order to execute this query. Close the Query Design window and save the query as QryCount.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.12 ~ Create a logical (condition based) expression within a query Procedure: The Iif conditional expression returns one of its two parameters based on the evaluation of an expression. It contains the following parts: Order Status: IIf([Quantity]>3,"Large Order","Small Order")

Name of field

expression

Return this value if the expression is true

Return this value if the expression is false

Exercise: Open the Garden database. Click on the Create tab and within the Other group click on the Query Design button. Select the GARDFILE and the PRICES table from the Show Tables dialog box Click on the Close button. The selected tables will be shown in the query grid. Select the desired fields from the Fields drop down menu. i.e. select ID, TITLE, SURNAME, NO, ADDRESS, TOWN, LAWN, HEDGES. In the next column enter your logical expression, i.e. enter: Lawn Price: IIf([Lawn]="Y",[Price per Lawn],0) In the next column enter another logical expression to enter the Hedges price. In the next column enter a calculated field to total the amount owed by the customer Total: [Lawn Price]+[Hedge Price]

Click on the Run button within the Results group on the top menu in order to execute this query.

ÂŠ Sarah Mason

Advanced Access Training Manual

Close the Query Design window and save the query as QryLogical. Exercise: Create a new query to show the total that customers will pay. Some customers are given a 5% discount

ÂŠ Sarah Mason

Advanced Access Training Manual 6.13 ~ Show lowest range of values in a query Procedure: This is a quick method of displaying the lowest numbers in the list, in this particular exercise the customers owing the bottom 25% of all the money outstanding. Exercise: Click on the Create tab and within the Other group click on the Query Design button. From the Queries tab in the Show Tables dialog box, select the QRY Price to Pay query and click on the Add button, Click on the Close button. The selected query will be shown in the query grid. Select the desired fields from the Fields drop down menu, i.e. select the ID, TITLE, SURNAME, PRICE TO PAY fields. In the Sort field under SURNAME column, select the field to be sorted by ascending order Click on the Top Values button within the Query Setup group on the top menu. NOTE: You will NOT see a button called Top Values. Click on the control illustrated below (which is actually called the Top Values control).

Enter the25% into the Top Values box. It will look like this. Click on the Run button within the Results group in order to execute this query. Close the Query Design window and save the query as Qr Bottom 25% Val ÂŠ Sarah Mason

Advanced Access Training Manual 6.14 ~ Finding the highest range of values within a query Procedure: This is the opposite of the previous exercise and will find the top 25% of monies outstanding. Exercise: Click on the Create tab and within the Other group click on the Query Design button. From the Queries tab in the Show Tables dialog box, select the QRY Price to Pay query and click on the Add button, Click on the Close button. The selected query will be shown in the query grid. Select the desired fields from the Fields drop down menu, i.e. select the ID, TITLE, SURNAME, PRICE TO PAY fields. In the Sort field under SURNAME column, select the field to be sorted by descending order Click on the Top Values button within the Query Setup group on the top menu.

Click on the Run button on the top menu in order to execute this query. Close the query results window and save the query as Qry Top 25% Val.

ÂŠ Sarah Mason

Advanced Access Training Manual 6.15 ~ Refining queries using NULL values Procedure: By adding IS NULL to the criteria in DESIGN VIEW, it will display those records that do not have any data. In the following example, all customers who have not submitted their postcodes. Exercise: Click on the Create tab and within the Other group click on the Query Design button. Select the GARDFILE table from the Tables tab in the Show Tables dialog box and click on the Add button. Click on the Close button. The selected query will be shown in the query grid. Select the desired fields from the Fields drop down menu, i.e. select the ID, TITLE, SURNAME, ADDRESS, TOWN, POSTCODE fields. Type the expression Is Null in the criteria field for POSTCODE. Click on the Run button within the Results group in order to execute this query.

It will display the customers who do not have a postcode. Close the query results window and save the query as QryNull.

ÂŠ Sarah Mason

100

Advanced Access Training Manual 6.16 ~ Finding NOT values Procedure: A Not value means that a value is not the same as the specified value eg not Bristol Exercise:

Click on the Create tab and within the Other group click on the Query Design button. Select the GARDFILE table from the Tables tab in the Show Tables dialog box and click on the Add button. Click on the Close button. The selected table will be shown in the query grid. Select the required fields, Type the expression Not “Value” in the criteria field for Name, i.e. type Not "Newton” or “Crawley”

Click on the Run button within the Results group in order to execute this query. It will display details for all customers except for those who live in Newton or Crawley. Close the query results window and save the query as QryNotVal.

101

Advanced Access Training Manual Chapter 7 ~ Reports About the report sections In Access, the design of a report is divided into sections. To create useful reports, you need to understand how each section works. For instance, the section in which you choose to place a calculated control determines how Access calculates the results. The following list shows a summary of the section types and their uses. Report Header Printed once at the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo, or a title and date. The report header prints before the page header. When you place a calculated control in the report header, the value is calculated for the entire report. For example, placing a control that uses the Sum aggregate function in the report header calculates the sum for the entire report. Page Header Printed at the top of every page. Use a page header, for example, to repeat the report title on every page. Group Header Printed at the beginning of each new group of records. Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group. Detail Printed once for every row in the record source. The Detail section is where you place the controls that make up the main body of the report. Group Footer Printed at the end of each group of records. Use a group footer to print summary information for a group. Page Footer Printed at the end of every page. Use a page footer to print page numbers or per-page information. Report Footer Printed once at the end of the report. Use the report footer to print report totals or other summary information for the entire report. NOTE: In Design view, the report footer appears below the page footer. However, when the report is printed or previewed, the report footer appears above the page footer, just after the last group footer or detail line on the final page.

ÂŠ Sarah Mason

102

Advanced Access Training Manual 7.1 ~ Create a grouped report using design view Procedure: As well as being created by a wizard, grouped reports can be created in design view. Exercise: Open the database Country and click on the Create tab and within the Report group click on the Report Design button. Within the Tools group click on the Property Sheet button if the Property Sheet window is not displayed. In the Record Source field of the Property Sheet under the Data tab, select a table, i.e. select Country Within the Controls group, click on the Text Box button. Click on the Report Detail section to place the selected Text Box. In the Property Sheet under the Data tab, enter a Control Source for this Text box, i.e. type: =[Capital] & "-" & [Country]

ÂŠ Sarah Mason

103

Advanced Access Training Manual At the bottom of the report in DESIGN VIEW, select ADD A GROUP

In the section saying GROUP ON SELECT FIELD, choose REGION from the list

This will automatically insert a Group Header called REGION HEADER

From the TOOLS section select the ADD EXISTING FIELDS option From the Field List, drag the field Region into the REGION HEADER area. This will group all of the countries by Region. Format the Region field so that it is size 20 and Bold, enlarge the field so that it will fit the text – the biggest entry is Central America. Click the Region label and press <Delete>. There will be no need to display the label. The following fields are also to be displayed in this report – Population, Currency. Drag them on to the Detail area. Preview the report. Some of the Regions may be detached from their data.

104

Advanced Access Training Manual Switch back to Design View. Open the Sorting and Grouping section, Select MORE. From the Group Properties area, drop down the Keep Together option and select Keep whole group together on one page. This will allow the whole group to be displayed together, if possible. Check this in Preview before switching back to Design View.

Some of the groups are so large that they have to be split over two or more pages. To display the Region Header on every page where the group is split, right click on the Region Header divide. From the shortcut menu, select Properties. The dialog box is displayed. Select the Format tab and in the Repeat Section box, select Yes. Close the Group Header properties dialog box. Preview the report. Each Region is displayed, as far as possible, completely on a page. Where this is not possible, e.g. for Asia, the Group Header is repeated. Save the report as Region and leave it open for the next exercise.

ÂŠ Sarah Mason

105

Advanced Access Training Manual 7.2 ~ Creating Master-Detail Report Procedure: A master-detail report is actually two reports – a main report, which displays information from one table, and a subreport, which displays information from a second related table. You can create a master-detail report by using the Report Wizard, or by creating a report “from scratch” (in Design view) and by using the Subform/Subreport button in the Toolbox. Exercise: Staying with the COUNTRY database and keeping the Rpt Grouping report open from the previous exercise, switch into DESIGN VIEW Select the SUBREPORT icon from the CONTROLS section. Click onto the report design and draw the box which will open the wizard. Step 1 choose Use existing tables and queries

Step 2: Choose the Bookings table and the SURNAME, FIRST NAME and HOTEL/SHIP

106

Advanced Access Training Manual Step 3: Choose from list: Show booking from each record in Country using Country ID

Step 4: give the sub report a suitable name.

Check the detail in the EGYPT section. Find the MIDDLE EAST section and right click on the GROUP HEADER. Choose “Equals Middle East”. This will filter out all other Regions.

107

Advanced Access Training Manual 7.3 ~ Modifying Report Properties Procedure: Earlier in this section, you modified the properties of various report controls. You can also modify a number of properties of the report itself. Exercise: Click on the report selector Choose PROPERTIES. The settings are very similar to those discussed in the FORMS section, therefore in this exercise just change the width of the paper.

ÂŠ Sarah Mason

108

Advanced Access Training Manual 7.4 ~ Calculated Fields Procedure: Calculated fields can be created on reports to obtain any mathematical/statistical information that may be required. Some calculations are performed on one field only, e.g. count, sum, average, or simply a field multiplied by a number. On other occasions the result may be one field multiplied/divided by another. On every occasion the field name is contained in square brackets [Field Name]. Exercise: The Region report should still be on screen, make sure Design View is displayed. Click the Sorting and Grouping section and set the Group Footer property for Region “With a footer section”.

From the toolbox click the Text Box button Footer.

and draw a box in the Region

Click in the Unbound box and enter the following calculation: =Count([Country]) This will count how many countries appear in each group. Change the label (Text#:) to Number of Countries in this Region. Move the label so that it can be viewed. Draw a line at the bottom of the Region Footer. Switch to Print Preview to view the calculation.

109

Advanced Access Training Manual Return to Design View and create another Text Box in the Region Footer. Resize the footer if necessary. Enter the following calculation in the unbound box: =Sum([Area]) This will calculate the total area of the region. Change the label to Total Area for this Region. Draw another text box in the Region Footer. Enter the following calculation: =Sum([Population]) This will total the population for each region. Change the label to Total Population for this Region. Realign the labels and fields so that all of the data can be read.

Switch to Print Preview and scroll through the pages to view the calculations.

Select all the calculated fields in the Region Footer and click the Copy button.

ÂŠ Sarah Mason

110

Advanced Access Training Manual Click in the Report Footer, then click the Paste button, to paste the fields into it. Change the field labels in the Report Footer to Total Number of Countries and Total Area and Total Population.

Preview the report. At the end of the report are the totals for the entire report.

ÂŠ Sarah Mason

111

Advanced Access Training Manual 7.5 ~ Calculate percentage calculations Procedure:

Using the AB text boxes, you can calculate percentages easily by the following formula VAT =[cost]*0.175 Exercise: Staying with the COUNTRY database and the REPORT, click into DESIGN VIEW. Add a calculation to work out the population of each country in 2040 if there is a global increase of 1.5% Add an AB text label into the DESIGN area as shown below.

ÂŠ Sarah Mason

112

Advanced Access Training Manual 7.6 ~ Using formulae in a report: Average Exercise:

Open the Houses database. Create a report in design view that shows all properties that are for sale where the asking price is over ÂŁ25,000. Display only the Town, Price, Address, Occupied and Status fields. Group the report by Town. Sort the groups by Price in ascending order. Display the Average price for each town. Save the report as Over 25. Close the report and the database. Exercise Use the Country database. Find all of the countries in Europe or America and view only the countries with a population over 30 million.

Use the results to produce a report grouped by Region, sorted in descending order of Population.

ÂŠ Sarah Mason

113

Advanced Access Training Manual Display the total and average population for each region. Insert the description Total Population for this Region alongside the total figure, and, insert the description Average Population for this Region alongside the average figure. Display the total and average figures as whole numbers. Give the report the title:

Europe and America Populations. Save as Rprt 2 and leave open for the next exercise.

ÂŠ Sarah Mason

114

Advanced Access Training Manual 7.7 ~ Adding a Cover Sheet to a Report Procedure: An Access report can optionally include a cover sheet created in Microsoft Word or another word processing program. Exercise: To add a cover sheet to a report: In design view, show the REPORT HEADER and double click the heading bar to show the PROPERTY SHEET In the FORCE NEW PAGE section, choose AFTER SECTION.

Choose the Insert, Object command. In the Microsoft Access dialog box, which is subsequently displayed, specify the name of the file that contains the cover sheet. Click on the OK button. Resize accordingly to move into place.

ÂŠ Sarah Mason

115

Advanced Access Training Manual 7.8 ~ Create running summaries Procedure A running sum is a total that is accumulated from record to record across a group, or even across the entire report. Exercise Using the COUNTRY database, create a report in DESIGN VIEW, showing FIRST NAME and SURNAME, DEPOSIT. Group the report by EXCURSION. Right-click on the DEPOSIT. Click on the Copy command from the pop-up menu. Right-click again and select Paste from the pop-up menu to paste the copied Deposit field into the report DETAIL section. Drag the pasted field and align it next to the first ProductPrice field. In the Property Sheet under the Data tab, change the Running Sum field from No to Over Group for this copied field. Place a label for this copied field next to the DEPOSIT label and give it the title DEPOSIT Running Price.

Click on the View button to preview the report. Keep the report open ready for the next exercise.

ÂŠ Sarah Mason

116

Advanced Access Training Manual 7.9 ~ Add a line number for each record in a report or group Procedure: You can number the items in your report by using a calculated control and setting its Running Sum property. Exercise: In the Navigation Pane, right-click the report and then click Design View on the shortcut menu.

On the Design tab, in the Controls group, click Text Box. In the Detail section of the report, drag the pointer to create the text box, making sure that it's wide enough to accommodate the largest item number. Select the text box. If the property sheet is not already displayed, press F4 to display it. Click the All tab. In the Name property box, type a name, such as txtItemNumber. Click the Data tab. In the Running Sum property box, select Over Group. In the Control Source property box, type =1. Click the Format tab. In the Format property box, type #. (a pound sign followed by a full stop). This formats the line number with a full stop following the number. Save the report and switch to Report view to see the results.

ÂŠ Sarah Mason

117

Advanced Access Training Manual Chapter 8 ~ Macros 8.1 ~ What is a Macro? A macro is a small program, which automatically performs a series of operations when run, saving you time it would take to manually perform the same operations. Macros can be used to automate such tasks as: Opening, printing and then closing a table. Adding new records to a table. Filtering the records in a table. Validating the information entered into a table. 8.2 ~ Creating a Macro Unlike some Windows applications, such as Microsoft Word, Access does not provide a macro recorder. Therefore, you must create the macro yourself. Exercise: To create a macro: Open the PERSONNEL database On the OTHER section of the ribbon, choose MACRO In the Macro window, which is subsequently displayed, click on the down arrow in the first field of the Action column Click on OpenTable In the ACTION ARGUMENTS section click in the Table Name box. Then click on the down arrow that appears. Click on PERSONNEL

ÂŠ Sarah Mason

118

Advanced Access Training Manual In the ACTION ARGUMENTS section click in the DATA MODE box. Then expand the options list, and click on READ ONLY. Click in the first field of the COMMENT column. Type “Open Personnel table as read-only”.

Click in the second field of the Action column. Then expand the options list, and click on Filter. In the COMMENTS section type: “Apply the Tues only filter”.

In the Action Arguments section choose the QRY Tues filter Click in the third row of the Action column, Choose SendObject. In the comments section type “Send Tuesday figures to Tom”. In the Action Arguments section choose the properties or similar shown here. Close the Macro window and Save as MCRO Filtered Tuesday. Exercise Define a second macro as shown here.

119

Advanced Access Training Manual

Save the second macro as MRO Open Form Test the two macros by double clicking on the name. If they need to be tweaked…..right click on the name and choose DESIGN VIEW

120

Advanced Access Training Manual 8.3 ~ Creating a Command Button Procedure: A command button is one that executes one or more operations when you click on it. The easiest way to create a command button is to use the Command Button Wizard, which generates both the button and the necessary code for carrying out a specified operation. This wizard is accessed via the Command Button tool in the Toolbox. Exercise: Keep the PERSONNEL database open and open the PERSONNEL FORM created previously. Switch into DESIGN VIEW. Click on the BUTTON option in the CONTROLS section. Draw the button on the form CANCEL the wizard that appears Right click on the button and open the PROPERTIES SHEET From the EVENT tab, choose the ON CLICK section drop down list and add the MRO Filter Table. Change the text on the button to SEND INFO TO TOM Save the form and test the button.

ÂŠ Sarah Mason

121

Advanced Access Training Manual 8.4 ~ Add an image to a button or other control Using an image instead of a text label on a command button or other control can help a user complete a task in less time. Images and icons can transcend language barriers, and they can often convey the meaning or implication of performing a task more effectively than a text caption. Open the Personnel form in Design view. Click the command button to which you want to add the image, and then click Properties on the toolbar to open its property sheet. You can also right-click the command or button, and then click Properties on the shortcut menu. Click the Format tab, and in the Picture property box, type the path and file name for a .bmp, .ico, or .dib file. If you're not sure of the path or file name, click the Build button

to open Picture Builder.

Select an image from the list in Picture Builder, or click Browse to locate another image. If you select an image from the list in Picture Builder, click OK. If you browse first, locate and select the image, click Open, and then click OK.

Note When you add an image to button or command, the image replaces any text labels or captions. Add the text in again on the PROPERTY SHEET.

ÂŠ Sarah Mason

122

Advanced Access Training Manual 8.5 ~ Writing an Event procedure Procedure: In the last subsection, you examined the program that was generated when you created a command button. This program, called an event procedure since it is executed when a particular event (for example, a mouse click) occurs, is an example of an Access module. Modules, like macros, allow you to perform operations that are considerably more complex than those performed with Macros. Access includes two types of modules: Class modules: This type is usually associated with a particular form or report. Class modules include event procedures. Standard modules: This type is not associated with any Access object. Standard modules include general procedures that can be run from anywhere within a database. An Access module begins with a declarations section, which sets certain module defaults and requirements. The declarations section is followed by one or more procedures. A procedure includes a number of statements, which perform specific operations. It can also include non-executed comments, which are used to explain the various tasks performed by the procedure. The statements of a procedure are written in the Microsoft Visual Basic for Applications (VBA) programming language. You enter and/or edit VBA code in the Visual Basic Editor. Exercise: write an event procedure Open the Training Data 4 database Display the Training Schedule Form in Design View. Create a button and open the PROPERTIES In the EVENT tab of the properties list click in the ON CLICK box. Then click on BUILD. Click on CODE BUILDER, OK

ÂŠ Sarah Mason

123

Advanced Access Training Manual Copy the lines of text shown here Close the VB editor and save the form. Test the Event Procedure By clicking the button, it runs the code which open the Course List form.

ÂŠ Sarah Mason

124

Advanced Access Training Manual Chapter 9 ~ Advanced Procedures 9.1 ~ Setting Database Startup Options Procedure The Access option on the start button allows you to control how a database appears and behaves when it is opened. Using this option, you can, for example, have a certain form or data access page displayed automatically as soon as you open a particular database. You can also specify what menus and toolbars are available when working in the database. Exercise: Open the database Training Data 4 Choose the Microsoft Start Button and then Access Options. From the list choose CURRENT DATABASE .

ÂŠ Sarah Mason

125

Advanced Access Training Manual In the Application Title area type: Blueband Training Company. This text will now appear on the Title bar of the Application window. Expand the Display Form section and choose Training Schedule. This form will now automatically open every time the database is opened. Click OK, Close and reopen the database to check it has worked. Hiding Object Types in the Navigation Pane In Office Access 2007, the Navigation Pane contains some extra features to display and organize objects in the database. One interesting way to protect the objects in the database is to hide complete groups of objects, such as all the tables, as shown below. To hide object types Click the Microsoft Office Button, and then click Access Options. In the left pane, click Current Database, and then click Navigation Options. Select Object Type, and then clear the check box next to Tables From now on, if a user selects the Object Type group, tables are not visible.

Ensure you show the tables again ready for the next exercise.

ÂŠ Sarah Mason

126

Advanced Access Training Manual 9.2 ~ Making a Backup Copy Procedure: Before you encrypt a database or split the table it is always a good idea to make a backup of the database. Exercise: To make a backup copy of the database Click the Microsoft Office Button. Click Manage Database, and then click Back Up database. Save the backup database into the course folder and give it a file name of your choice.

ÂŠ Sarah Mason

127

Advanced Access Training Manual 9.3 ~ Opening Databases Exclusively Procedure: Before encrypting an ACCDB database, you must open the database exclusively. First, close the database. Then you can open the database exclusively (if no one else is using the database) as follows: Exercise: to open a database exclusively Click the Microsoft Office Button, and then click Open. In the Open dialog box, find the file that you want to encrypt. Click the arrow next to the Open button, and then click Open Exclusive, as shown below:

ÂŠ Sarah Mason

128

Advanced Access Training Manual 9.4 ~ Encrypting Databases and Adding Passwords Procedure: Encryption and Database Passwords Since the earliest versions of Access, two of the simplest security measures you could apply to a database were to create a database password and to encode the database. In Access 2007, these two measures have been combined to create a stronger protection measure for ACCDB format files. For the MDB database format, the password and encoding system remains as it was. When you add a database password in Access 2007, Access encrypts the database, with the encryption key being derived from the password. Fortunately, the password is not retained in the file so the file becomes more secure. This style of encryption is common across all 2007 Office system programs. After Access applies the password encryption, the database file can be opened only after the user enters the password. A benefit of encryption is that users cannot open the database in another program, such as a text editor, and hunt through the file for pockets of valuable information. If you do not encrypt the database, a dedicated person can piece together valuable information from the file. When your database is open exclusively, you are ready to encrypt it. Exercise: To password encrypt the database

Click the Database Tools tab. In the Database Tools group, click Encrypt with Password. In the Set Database Password dialog box, type a password and verify it in the Verify field.

Removing Passwords and Encryption To remove a password, open the database exclusively. In the Database Tools group, click Decrypt Database.

ÂŠ Sarah Mason

129

Advanced Access Training Manual 9.5 ~ Splitting Databases and Linked Tables Procedure: With Access 2007, you can compile your database to remove source code, and you can secure your data by using Microsoft SQL Server. To implement either of these two significant security approaches, you must understand how to split your database into a back-end, data-only database, and a front-end database that holds your queries, forms, reports, macros, and modules. Doing this enables you to develop your software in a different location from your live database and to install your new frontend solution when your testing is completed. Exercise: To split a database Open the database. Close any open objects in the database so that you are left with the Navigation tab. On the Office Fluent Ribbon, click the Database Tools tab. In the Move Data section, select Access Database. This starts the Database Splitter wizard. Read the instructions and click Split Database. Choose an appropriate name that matches the current database and indicates that the database contains only data. The wizard indicates this by using a name ending in _BE.ACCDB. Click Split. All the tables will be moved to the back-end database that you created. In place of those tables in the Navigation pane, you will find table icons with an arrow next to them. The database that you started with is now your front-end database.

ÂŠ Sarah Mason

130

Advanced Access Training Manual 9.6 ~ ACCDE Format: Security for Programmable Objects

Procedure: If you want to help secure the forms, reports, and modules in your database, there is no better way to do this than to convert your front-end database to the compiled format, called the ACCDE format. ACCDE is the new file extension for Office Access 2007 files that are in "execute only" mode replacing the MDE extension from 2003. .When you compile a database, the readable code from the objects is stripped from the database. Exercise: To create an ACCDE compiled database Open the development version of the ACCDB front-end database. On the Office Fluent Ribbon, click the Database Tools tab. In the Database Tools group, click Make ACCDE. Enter the name of the file (generally, the same name as the original file with the ACCDE file name extension). Link the compiled ACCDE file to the live back end.

ÂŠ Sarah Mason

131

Advanced Access Training Manual 9.7 ~ Creating a Switchboard

Procedure: A switchboard is a special type of form that displays a series of option buttons. Using these buttons, you can quickly navigate between the forms and reports of a database, as well as perform other operations (for example, run a macro) in the database. When you use the Database Wizard to set up the database, the wizard automatically creates a switchboard that appears when you open the database. You can create a switchboard for any other Access database by using the Switchboard Manager. Exercise: To create a switchboard: From the Office Fluent Ribbon, click the Database Tools tab and choose Switchboard Manager. In the prompt box that appears, asking you if you would like to create a switchboard, click on the Yes button. Doing this displays the Switchboard Manager dialog box, which lists one (main) switchboard page. In the switchboard Manager dialog box, click on the New button. In the Create New dialog box, which is subsequently displayed, enter a name for the switchboard page. Click on the OK button

ÂŠ Sarah Mason

132

Advanced Access Training Manual To add buttons to a switchboard page: In the Switchboard Manager dialog box, select the switchboard page name. Click on the Edit button. In the Edit Switchboard Page dialog box, which is subsequently displayed, click on the New button. In the Edit Switchboard Item dialog box, which is then displayed: Enter the text that is to identify the first switchboard button. Select the command that is to be associated with the button and, if necessary, the item that is to be associated with the selected command. Click on the OK button. In the Edit Switchboard Page dialog box, which is redisplayed, repeat the above procedure, as necessary, to create any other button that is to appear on the switchboard. When you are finished, click on the Close button (in the Edit Switchboard Page dialog box) to return to the Switchboard Manager dialog box. Notes: When you create a switchboard, Access also creates a Switchboard Items table. This table contains a list of the switchboard buttons, along with the operations performed by those buttons. You can have a switchboard displayed automatically when you start Access by choosing the Tools, Startup command and by selecting the Switchboard form in the Display Form/Page box of the Startup dialog box.

ÂŠ Sarah Mason

133

Advanced Access Training Manual Chapter 10 Working with other applications 10.1 ~ Exporting Access data to an XML Document/Importing an XML Document into Access Procedure: Extensible Markup Language (XML) is a standard language that, like Hypertext Markup Language (HTML), is associated with Web documents. However unlike HTML, which describes the appearance of data (for example, how it should be displayed in a Web browser), XML describes the structure of the data. The real power of XML is its ability to deliver data in a format that can be easily transferred from one application to another, both on and off the Web. This is accomplished by putting structured data, such as entries in an Access table, in text files, following standardised guidelines and rules. From Access, you can both export Access data to XML documents and import XML documents into Access. Exercise: To export Access data to an XML document: Open the EMPLOYEE DATA database. Right click the COURSES table, choose EXPORT, XML. Specify a name and destination for the file.

A dialog box will appear asking which one(s) of the three files you want to have created: Tick all three check boxes for this exercise.

ÂŠ Sarah Mason

134

Advanced Access Training Manual If you want to create a more elaborate XML application with advanced options, you can click the More Options button. This would close the previous dialog box and open another one:

This dialog box allows you to specify more details on how the table should be exported. For example, you can change the encoding scheme you want. The two options available are UTF-8 (which should be enough for characters in US English) and UTF-16 (if you are planning to use Unicode or international characters) for characters referred to as wide characters. By default, when you ask Microsoft Access to create an extensible style sheet (XSL), it would create the file using the same name as the table. If you want a different name, you can click the Transforms button. This allows you either to select a different file or to create the file with a different name. Also, by default, if you ask Microsoft Access to create a schema, it creates an XSD file using the same name as the table. If you want a different name, you can click the Schema tab and specify another name for the file. After making your selections, you can click OK. The file(s) you specified would be created in the folder that was indicated.

ÂŠ Sarah Mason

135

Advanced Access Training Manual Exercise: To import an XML document into Access: Open the Employee Data 2 database From the Office Fluent Ribbon, click the External Data tab and the Import section. Choose XML file. In the Import dialog box, which is subsequently displayed, expand the Files of Type box, and select the XML (*xml) option. Specify the appropriate filename in BROWSE and then OK. EG COURSES

In the Import XML dialog box, which is then displayed, select the desired import option(s) as necessary. Click on the OK button

ÂŠ Sarah Mason

136

Advanced Access Training Manual Index

ACCDE Format ................................... 133 Action Query ........................................ 83 Advanced Filter.................................... 77 Append query ...................................... 88 Backup Copy ..................................... 128 Bound controls ..................................... 63 Calculated controls ............................. 63 Calculated Fields ............................... 109 Cascade Delete Related records ..... 47 Check box ............................................ 66 Combo box .......................................... 66 Command Button .............................. 121 Command tabs .......................................5 Conditional Formatting ....................... 76 Contextual command tabs...................5 Cover Sheet to a Report ................... 115 Crosstab query ..................................... 91 Database Startup Options ................ 126 Date ....................................................... 58 Delete query ......................................... 90 Display bound images ........................ 55 Encrypting Databases and Adding Passwords ........................................ 130 Event procedure ................................ 124 Field Templates..................................... 16 Form Properties ..................................... 60 Galleries ....................................................5 Group information in a query............. 93 Grouped report .................................. 103 Image on a button ............................ 122 Images ................................................... 30 Input Mask ............................................. 22

List box.................................................... 66 Logical (condition based) expression 96 Lookup field ........................................... 17 Macros ................................................. 118 Make Table query ................................ 84 Master-Detail Form ............................... 48 Master-Detail Report .......................... 106 Multiple Conditions in a Query ........... 78 NOT values........................................... 101 NULL values ......................................... 100 Opening Databases Exclusively ....... 129 Option groups ....................................... 66 Parameter Query.................................. 80 Percentage calculations................... 112 Picture as a watermark ....................... 53 Quick Access Toolbar ............................ 5 Relationship ........................................... 32 Report Properties ................................ 108 Report sections ................................... 102 Running summaries ............................ 116 Sequential order of controls................ 72 Splitting Databases ............................ 131 Switchboard ........................................ 134 Tables ..................................................... 15 Templates .............................................. 12 Unbound controls ................................. 63 Update query........................................ 86 Validating Field entries ........................ 18 Wildcards as parameters .................... 82

Joins ....................................................... 41 XML Document ................................... 136 Linked Tables ...................................... 131

ÂŠ Sarah Mason

137