PC Passport: Databases
PC Passport [pic]
Databases
Student Workbook
[pic]
Published date: August 2008
Publication code: CB4126
Published by the Scottish Qualifications Authority
The Optima Building, 58 Robertson Street, Glasgow G2 8DQ
Ironmills Road, Dalkeith, Midlothian EH22 1LE
.uk
The information in this publication may be reproduced to support the delivery of PC Passport or its component Units. If it is to be used for any other purpose, then written permission must be obtained from the Assessment Materials and Publishing Team at SQA. It must not be reproduced for trade or commercial purposes.
© Scottish Qualifications Authority 2008
Introduction
This student workbook is one of a range of eight titles designed to cover topics for the refreshed PC Passport. Each title in the range covers the required subject material and exercises for candidates studying PC Passport.
This workbook covers all three levels of PC Passport — Beginner, Intermediate and Advanced. It is not necessary for candidates studying Beginner and Intermediate levels to do the sections on Creating Forms and Database Macros, but it is required for candidates at Advanced level.
There are a number of exercises associated with each subject and it is recommended that centres download and use the sample exercise files provided.
Each workbook will help prepare candidates for the assessments for the refreshed PC Passport. It is recommended that centres use the most up-to-date Assessment Support Packs appropriate for their type of centre, eg either school, FE or work-based.
Contents
What Is a Database? 1
Database Design Considerations 1
Relationships and Keys 5
Exercise 1: Database Elements 16
Creating a New Database 17
Exercise 2: Creating a Database Structure 18
Creating Tables 20
Exercise 3: Creating Tables 26
Creating Forms 31
Exercise 4: Creating a Form Using the Wizard 41
Exercise 5: Refining Form Properties 65
Exercise 6: Creating a New Database with Forms 66
Creating Queries 67
Exercise 7: Query Types 73
Exercise 8: Sorting Information in a Table 76
Exercise 9: Creating a Query with a Wizard 86
Exercise 10: Creating a Query in Design View 87
Exercise 11: Using a Query 96
Using Reports 96
Exercise 12: Report Functions 106
Exercise 13: Creating a Report Using a Wizard 113
Exercise 14: Creating a Report Manually 114
Database Macros 118
Exercise 15: Design a Report 120
Finally 120
Appendix 121
What Is a Database?
A database is an organised list of information. A simple database program — a flat file — can store information in only one table. More complex database programs can store information in multiple related tables.
Database Elements
A database is a collection of information stored in an organised manner. Two common database types are relational databases and flat-file databases. In a relational database such as Access, data can be subdivided into containers called tables. Tables organise data into rows and columns. (Rows are also called records, and columns are also called fields.) A record contains information about a particular item, such as a customer, while fields identify the category of information, such as the contact name or address.
In a flat-file database, different types of information are not broken out into separate tables but are included in one table. The same data might (and generally does) appear in multiple records in the table. For example, a customer’s full name and address might be stored in many records of an Orders table. If that customer’s address changes, it must be changed in all those records. Flat-file databases were used in early database systems, and many mainframe databases still have a flat-file format, as do some low-end PC databases intended for personal use.
Database Design Considerations
Before you begin creating a database, it’s a good idea to do some planning and preparation. Sometimes when you’re creating a database for another person you’re given a vague description of what the database is supposed to do, without any help about the interface or what type of users will be performing what tasks with the database. At other times, you might be given instructions in too much detail, accompanied by stacks of hard-copy documents more suitable for pencil and paper work than computer data input.
Sometimes these instructions are based on non-database programs such as Microsoft Excel, so the instructions might be difficult or even impossible to implement. A typical example is data entered all in uppercase, which might have been the only way data could be entered in some long-ago database.
As a database creator, you need to analyse the database’s requirements before starting work. Rather than try to follow vague or inappropriate instructions you need to determine what the database should do, then construct the database so that it does what the users need it to do and is easy for them to work with. The following sections introduce some of the considerations involved in designing a database.
Planning the Database
Before you begin work constructing the database, make sure you know what your customer is expecting in response to the following questions:
◆ What does the customer want the database to do?
◆ What specific tasks will the database perform?
◆ How do users want to output information from the database?
◆ What kinds of users will be working most with the database, and what is their level of comfort with using a database and computers in general?
◆ Are the tasks or procedures to be implemented in the database finalised and working correctly now?
◆ What type of database and what version will users have access to — are some using an older version of the database?
◆ Are there any specific features the customer wants you to incorporate into the database?
You don’t need to build the database with all the latest features. Talking with your customer about the above issues beforehand will help you plan for a smoother development process.
Identifying How the Database Will Be Used
When you’re designing a database, you need to consider how the database will be used and how (and where) its data should be stored.
There are typically three types of databases: personal, corporate/networked, and web-enabled.
Personal Databases
A personal database stores its data in the same database as the interface objects. Personal databases are used to store information such as addresses, phone lists, CD collections, and other personal data.
A personal database isn’t necessarily a small or simple database — you might need a large and complex database to manage a personal interest or hobby. The main distinction between a personal and a corporate database is that you have complete design control over a personal database, and you can spend as much time as you want on each database element.
Corporate/Networked Databases
A database designed for business use should usually be broken into a front-end database (containing the interface elements, such as forms and reports) and a back-end database (containing just the data tables).
Separating the data from the front-end makes it possible to update the database interface without disturbing existing data. It also enables you to place the back-end on a server, with separate front-ends on individual workstations, which can improve performance and allows for customised front-ends for different users.
A front-end/back-end database isn’t the same thing as a client-server database. In a true client-server database, the application needs to be divided into a client component, which contains interface objects, and a server component, which stores data and performs data manipulation on a server (a central computer used for high-powered data processing and storage of massive amounts of data).
This often speeds up processing, but if you have many users accessing data, it requires specialist database administrators to look after the server to optimise its performance and perform routine maintenance to ensure the data is always available.
Web-enabled Databases
Using an Access front-end you can be connected to a database table located on a web server. This enables users to connect to the database via the web, either on a corporate intranet or using the internet. A web-enabled database is suited for mobile users who have web access. Users can log onto the database and view or modify data (with the proper permissions) from anywhere in the world.
Protecting the Database
To ensure the data stored in your database is safe, you must create a secure environment to keep your database and the information it contains in a robust and reliable way. It is important that when entering data, allowing other users to access the data and maintaining the data (during updates) that you ensure you back up your database regularly. This will depend on the sensitivity of the data, but it may be daily, weekly or monthly, or a combination of all three. For example, a customer account database is critical to a business receiving orders — if the data is lost, valuable orders may not be processed.
When allowing access to the data it is also important the users only see the data relevant to what they need. For example, in the stockroom it is not necessary to see the customers’ credit history, but it is important to see their name and address and details of their order to ensure it is delivered correctly by the delivery driver. In the finance department it is necessary for the administration person to process the customer’s invoice correctly so they need to access customer details, orders and payments to correctly produce the right invoice for the customer. Creating different forms, or reports, or even queries to allow access by users is an important part of being a database administrator.
All computer systems can be attacked by viruses, so it is important that your databases are protected. Make sure you have the most current active anti-virus software installed and that you check your system every day.
Relationships and Keys
Keys are fundamental to the concept of relational databases because they enable tables in the database to be related with each other.
Relationships are established by linking a unique field (called a primary key) in the first table to a corresponding field (called a foreign key) in the second table. The two tables are joined together with common fields, so data does not have to be duplicated in each table. An advantage of a relational database over a flat-file database is that there is less duplication of data. In a relational database you can create relationships between multiple tables. This means that two or more tables can share information.
Navigation around a relational database depends on the ability of the primary key to unambiguously identify specific rows of a table. Navigating between tables relies on the foreign key being able to correctly and consistently reference the values of the primary keys of a related table.
When a field in a table is set as the primary key, each record in the table must have a value in that field and each record’s value must be unique. Sometimes a table already has a field with unique data that can be set as the primary key field, such as an EmployeeID field in an Employees table or a student examination number in a student table.
The keys you pick in related tables do not need to be primary keys or even have the same format or the same name, but they must be of the same data type. For example, you might have an EmployeeID as a primary key in an Employee table and an Employee Number as a foreign key in a Work table. Even though the keys are different types and the format of the names (EmployeeID and Employee Number) are different, we can set up a relationship between the two tables as long as the data type is set to a number for both data types.
Access lets you add a field of the AutoNumber type to a table and use it as the primary key field, providing a unique ID number for each record in the table. However, it is important to note that if the record is deleted you cannot reuse the AutoNumber that was assigned for that record. A foreign key field doesn’t need to have unique values in its table.
In a one-to-one relationship, a record in one table can have only one related record in the other table.
[pic]
The one-to-one relationship shown above links two tables so that one record in tblEmployees matches one record in tblEmployeeSalary.
How to Create a One-to-One Relationship
A one-to-one relationship links two tables, each with a key field (so they have only one record per key field value). Typically, two tables are linked in a one-to-one relationship because some of the data is more confidential, or is used less frequently. To set up a one-to-one relationship between tables follow the steps below:
1 Open the Relationships window and add your tables to the diagram.
2 Drag the linking field from the first table to the field of the second table; the Edit Relationships dialogue box opens, with the relationship type listed as One-To-One.
3 Click the Enforce Referential Integrity check box and then click Create to create the relationship, which is indicated by a 1 at each end of the linking line in the Relationships window.
In a one-to-many relationship, a record in one table can have multiple matching records in another table, as shown below:
[pic]
In this one-to-many relationship, many records in the tblOrders table match one record in the tblCustomers table. Notice how the little symbol on the right-hand table is different from a one-to-one symbol on the left-hand table.
How to Create a One-to-Many Relationship
One-to-many relationships are by far the most common relationships in databases. A one-to-many relationship links a table with one record per key field value to another table that may have multiple records for that same value. To set up a one-to-many relationship between these tables, follow the steps below:
1 Open the Relationships window by clicking the Relationships button on the toolbar, or selecting Relationships from the Tools menu.
2 Click the Show Table button in the toolbar to open the Show Table dialogue box. A matching field can have different names in the two tables, but it must be of the same data type, with one exception: An AutoNumber field matches a Number (Long Integer) field.
3 Drag the linking StudentID field from each table to the field of the corresponding table.
4 The Edit Relationships dialogue box opens, as shown in the next figure. Access detects the appropriate relationship type (One-To-Many) and lists it in the Relationship Type box at the bottom of the Edit Relationships dialogue box.
[pic]
5 Click the Enforce Referential Integrity check box then click the Create button to create the relationship, which appears as a line with a 1 on the ‘one’ side of the linking line, and an infinity symbol on the ‘many’ side, as shown below:
[pic]
In a many-to-many relationship, many records in one table can have many matching records in another table. Not all databases provide an explicit many-to-many relationship type, but a many-to-many relationship can be set up indirectly by using a linking table containing just the key fields of the two tables to be linked, as shown in the next diagram.
[pic]
A many-to-many relationship allows one record in the tblStudents table to link to multiple records in the tblClasses table, and vice versa, using tblStudentsClasses as an intermediary.
How to Create a Many-to-Many Relationship
A many-to-many relationship is a pair of one-to-many relationships, with a linking table between the two tables to be linked. The linking table needs only the two fields that are key fields in the two main tables, though it may contain other fields as well. Follow the steps below to set up the many-to-many relationship.
1 Open the Relationships window, and add your three tables with appropriate matching fields to the diagram.
2 Drag the field from the first table to the second table to set up the first of the two one-to-many links that comprise the many-to-many link; as before, select the Enforce Referential Integrity check box and click Create to create the one-to-many relationship.
3 Similarly, drag the field from the third table to the second table to set up the second one-to-many link. Select the Enforce Referential Integrity check box and click Create to create this one-to-many relationship.
After you have set up a relationship in the Relationships window, the link will automatically appear in the Query Design view when you add the linked tables to a query.
When creating a relationship between two tables, an error message will occur if the fields being related are not the same size. This would create a referential integrity error.
If the Enforce Referential Integrity check box is selected in the Edit Relationships dialogue box, the database uses a set of rules to ensure that relationships between records in related tables are valid and that you don’t accidentally delete or change related data.
To enforce referential integrity, the following requirements must be met:
◆ The matching field in the primary table must be a primary key or have a unique index.
◆ The related fields must have the same data type, with two exceptions: an AutoNumber field can match a Long Integer field, and an AutoNumber field with a field size of Replication ID can match a Number field with a field size of Replication ID.
◆ Both tables must be in the same database. For linked tables, you must set the relationship in the original database.
It’s best to set up relationships and enforce referential integrity as soon as possible after creating tables, to avoid problems with trying to set up a relationship with referential integrity when existing data in the tables conflicts with referential integrity rules.
Avoiding Data Duplication
The primary advantage of using a relational database is that information isn’t duplicated in different tables, or in multiple records in a single table. In a properly designed relational database with no duplicated data, when you change a customer’s address in one table (for example), any table that links to the address information will automatically pick up the updated data through its linking field.
Problems arise if there are no linking fields defined between tables, as this would result in all the records being returned — often referred to as a ‘Cross or Cartesian Product’. These queries might take a long time to run and ultimately might produce less meaningful results.
Data Consistency
When you have many users working with your database they can create, remove, rename (move), or modify any data in the file system at will. There is therefore a possibility that the set of links in a database may not represent the current result of evaluating its query. For example, a user is editing a record that should be present in your query, or deleting it after it appears in your query result. This gives rise to a data-inconsistency problem.
Data inconsistencies manifest themselves in the following ways:
1 A query result can contain an invalid link to a record that no longer exists, has been renamed, or has been modified so that it no longer satisfies the query.
2 A query result may not contain a link to a new or modified record when it actually should.
Locks are used to ensure that data remains consistent even when multiple users try to access the same data at the same time. From an auditing standpoint, you can use locks to ensure that only one user is privileged to change data at a given time. You can also ensure that no user is privileged to access uncommitted data.
Data Redundancy
Data redundancy is a data organisation issue that allows the unnecessary duplication of data in your database. A change or modification, to redundant data, requires that you make changes to multiple fields of a database. While this is the expected behaviour for flat-file database designs and spreadsheets, it defeats the purpose of relational database designs.
The relationships inherent in a relational database allow you to maintain a single data field at one location and make the relational model responsible for any changes to the data across the database.
Redundant data wastes space and creates troubling database maintenance problems. These give rise to various data anomalies.
To eliminate redundant data from your database, you must take special care to organise the data in your data tables during the design stage. Normalisation is the name given to the method of organising your data to prevent data redundancy.
Normalisation involves establishing and maintaining the integrity of your data tables as well as eliminating inconsistent data dependencies. Establishing and maintaining integrity requires that you follow Access’s prescribed rules to maintain parent-child table relationships.
Eliminating inconsistent data dependencies involves ensuring that data is stored in the appropriate Access database table. An appropriate table is a table in which the data has some relation to or dependence on the table.
Normalisation is a set of rules established by the database community to ensure that data is organised efficiently, and also to remove the data anomaly problems. These rules are called normal form rules. Normalisation may require that you include additional data tables in your database. Normal form rules number from 1 to 3, for most applications. By the time you have reached the third normal for the data, anomalies have been removed. There are further stages of normalisation.
Reviewing Data
Creating tables and fields can be very easy if you use a Database Wizard or a Table Wizard. Once you get beyond the standard wizard-created tables and fields, however, you need to understand your data to determine how you’ll divide the data into fields in the tables.
Before you create your tables, or any other database elements, you should analyse the data you’ll be storing. For example, if you’re creating a database to store information about your CD music collection, you need to decide whether you want to store only data about the CD number, title and artist for each CD or whether you also want to include notes, the individual performers, publisher, instruments, language, type of music, and so on. Additionally, you might want to store separate information about title, artist, and other information for each track on a multi-track CD.
If you’re creating the database for a customer, you’ll need to establish which data elements are important to the customer, to ensure that those elements will be represented by fields in the tables you create, and also to determine how the user wants to input and output the data.
For example, suppose that you don’t expect to need information about individual performers, so you enter only group names (such as Nickleback) for the CDs in your CD database. Later, if you want to find all CDs that feature a certain performer (such as the name of the lead singer, Chad Kroueger), you won’t be able to retrieve that information for CDs on which that performer sang as a member of the group. However, if you enter the names of individual performers as well as groups, you can retrieve information about either groups or performers.
Entering Name and Address Data
Many databases store name and address data, and it’s especially important to enter this data in separate fields for each name or address component, rather than entering the complete name in a Name field and the complete address in an Address field. Countless databases have had to be redesigned later because names were entered into a single field instead of being split (at a minimum) into FirstName, MiddleName and LastName fields (or better still, into Prefix, FirstName, MiddleName, LastName and Suffix fields). Splitting a name into its components enables you to perform sorting tasks, including alphabetising by last name in a report, or printing name badges using first names only.
The same principle applies to addresses — for maximum usefulness you should split addresses into (at least) Street Address, Region, Postal Code, and Country fields. In some cases you might want to split the street address into several fields, especially if you need to track the physical address and mailing address separately.
Microsoft Access is a database program included in the Microsoft Office suite and has a number of features for accessing, entering, editing and displaying data. In Access, you can use many of the techniques you know from using other Office applications. You can also easily share information between Access and Word, Excel or other programs, because Access is integrated with other members of the Microsoft Office suite.
Microsoft Access has many types of database objects that you can work with: Tables, Queries, Forms, Reports, Pages, Macros and Modules. Of these entire object types, only Tables are used to store information. The rest are used to manage, manipulate, analyse, retrieve, display or publish the table information.
Access databases have a file name extension .mdb. You will normally open a database by double-clicking its file name in Windows Explorer, but you can also start Access by clicking New on the File menu to display the New File task pane, which offers a variety of options for opening new or existing databases.
In any database, tables are the core database objects. Their purpose is to store information. All other database objects interact in some manner with one or more tables. One database can contain thousands of tables. The two most common views for tables are the Datasheet view, where you can see and modify the table’s data, and the Design view, where you can see and modify the table’s structure. When you view a table in Datasheet view, you see the table’s data in columns (fields) and rows (records), as the following illustration shows:
In Design view, you see the underlying table structure, as shown below:
[pic]
To navigate through a table, you can move one record at a time by pressing the up arrow key or the down arrow key. To move one screen at a time, you can press the [Page Up] key or the [Page Down] key. You can move to the first or last field in the table by pressing [Ctrl] [Home] or [Ctrl] [End].
A quick way to get help in Microsoft Access, for example, is through the Ask a Question box and the Office Assistant feature. If you are not satisfied with the answers that are provided, the Help features allow you to look for more help on the web.
The Office Assistant automatically provides Help topics and tips on tasks you perform as you work — before you even ask a question. You can customise the Assistant, and decide if you want it to automatically display tips, messages and alerts, make sounds, move when it’s in the way, and guess a Help topic that it thinks you may need. You can also type a question and the Assistant will display a list of possible matching topics.
Many people find the Office Assistant useful; however, you can choose to hide the Office Assistant. To do so, click the Help menu, and click Hide the Office Assistant.
When you click the Help menu and select Microsoft Access Help, a Help window displaying several tabs appears. You can receive help in different ways, using the tabs to select the method most suitable to your needs:
Contents — On this tab, you can view the Table of Contents for Access.
The Answer Wizard — On this tab, you can type a question and Help will display a list of possible answers about Access.
Index — On this tab, you can search for specific words or phrases, or choose from a list of keywords in the Access Help Index.
If you still can’t find the information you need, Access Help provides a link to Microsoft Help on the web.
(Exercise 1: Database Elements
Answer the following questions.
1 What is a database?
___________________________________________________________
2 What are records?
___________________________________________________________
3 What is a field?
___________________________________________________________
4 What are the objects in Access?
___________________________________________________________
5 What is the Design view?
___________________________________________________________
Creating a New Database
Creating a database has become quick and easy. Database programs like Microsoft Access often have wizards to help you create database applications. Although the wizard may not always create exactly the database application you want, it will create something very close to what you have in mind.
The Database Wizard uses pre-defined templates to create databases. In addition to these templates, if you are connected to the internet you will find more templates and other resources by following the link to Templates on . This link is in the New from template section of the New File task pane.
After you have gone through all the stages of creating the structure of your database, the Database Wizard displays an alert box that shows you the process of creating the database. When the wizard finishes, it opens the newly created database with the Switchboard displayed, as shown here:
Any Microsoft Windows file can contain a maximum of 215 characters including spaces, but such a long name is not recommended. File names cannot contain the characters \ / : * ? < > |. The extension for an Access database file is .mdb.
For storage purposes, the default folder for storing Access database files is My Documents. To change this default folder, click Options on the Tools menu when a database file is open, enter a new path in the Default database folder box on the General tab and click OK.
To move back through previous pages, click the Back button (if active). To finish your work any time, click the Finish button (if active). Most options set by a wizard can be modified, so clicking Finish does not mean whatever the wizard creates is final.
Using a wizard to create a database is quick. The Database Wizard creates a database application, complete with a switchboard, several tables and some other objects. If the wizard’s work doesn’t quite suit your needs, you can modify any of the database objects or use another type of wizard to add more objects.
Unlike other computer applications, with Access it is neither important nor possible to manually save your data. After you have entered or edited information in a record, Access saves that record. However, you must remember that any data entry changes you make are permanent and can be undone only by editing the record again.
(Exercise 2: Creating a Database Structure
This exercise is based on Microsoft Access for Office XP. Task panes are new to this version. Commands displayed in a task pane can often be found in the View, Toolbars menu.
1 To display the task pane and begin creating a new database, on the standard toolbar click the New button.
2 To select a pre-defined template, on the New file task pane on the right side of your screen, in the list New from template, select General templates.
3 To view the list of pre-defined templates, click the Databases tab. Select the Contact Management template by double-clicking Contact Management.
4 To rename the Contact Management template, in the File, New Database dialogue box, in the File Name box type Contacts. Notice your screen changes to the Contact Database dialogue box in the top left of your screen and the Database Wizard appears. Click Next.
5 To view the fields available on the Call information table in the Database Wizard, click Call information and then click Next.
6 To preview the display for Blueprint, click Blueprint in the list. To select a display, click Blends and then click Next.
7 To select a style for any printed reports, double-click Bold.
8 To assign a new title to the database, type Contacts and click Next.
9 To finish building your database on the Wizard click Finish.
10 To close the Main Switchboard dialogue box, click the red x in the upper right corner of the box.
Checking the Work of a Wizard
1 To maximise the Contacts database, click the maximise button to begin checking the wizard’s work.
2 To open the switchboard, double-click the word Switchboard.
3 To view the Forms Switchboard, click Enter, View Other Information.
4 To add a new record click Enter, View Contact Types.
5 To view records and then add a new record, use the Record navigation bar at the bottom of the screen. Click the right arrow (next record) twice.
6 To type in a new record in the Contact Types form, type Supplier then press Return [Enter].
7 Close the Contacts Type form.
8 To return to the Main Switchboard click the button marked Return to Main Switchboard.
Creating Tables
Databases allow you to create tables in different ways. The Access Table Wizard allows you to choose fields for your table from a variety of pre-defined tables. A database should be created with one table for each category of information.
The power of a good relational database system is its ability to quickly gather information from separate tables. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in a table. This information is called a primary key.
For example, you might have a table containing employee information. If you have two employees with the same name like Smith, it will be difficult to keep their information separate. However, if you set a primary key for a field that is unique, such as a National Insurance number or matriculation ID, you can keep each record separate.
You can provide a name for your new table and specify whether the wizard should set a primary key for the table.
The Table Wizard displays a list of either Business tables or Personal tables. Depending on the nature of your business or preferences, you would find the sample table you want in either list.
Each category of tables (Business or Personal) in the Table Wizard contains a list of sample tables. When you click an item in the Sample Tables list, the Sample Fields list to the right displays all the fields available for that table. (You can add more fields after creating the table if you need them.)
Selecting an item in the Sample Fields list and then clicking the > button moves the selected field to the Fields in my new table list.
Clicking the >> button moves all sample fields to the Fields in my new table list.
The < and 0, which specifies that values greater than zero must be entered.
Validation rules can be set in tables or in forms, but generally it’s better to set them in tables because validation rules set in tables will be inherited by fields placed on forms, and additionally they will work if data is entered directly into a table.
A validation rule that applies to only one field is called a field validation rule, and a validation rule that includes conditions on two or more fields in a record is called a record validation rule. Both types of rules are specified by the ValidationRule property of a field.
You can either type a validation rule directly in the ValidationRule property, or in Microsoft Access you can click the Build button to the right of the property to open the Expression Builder dialogue box, where you can easily create a complicated expression by selecting components from lists of database objects, fields and functions. The following illustration shows a simple expression being created in the Expression Builder — in this case, a validation rule that checks whether the date entered in the Due field is later than tomorrow.
[pic]
[pic]
Most validation is set in the General tab in the field’s properties sheet. You can add text to explain what the validation rule is expecting from the person inputting the data. For example, you might want to ensure a user always fills in a section of the table or form like the purpose of their journey. You can set a validation rule which expects the property of the field to not be blank.
Input Masks
An input mask controls how data looks when you enter it and the format in which it is stored. When you enter a date in Datasheet view, you will see a mask that looks like this: __/__/__.
The mask indicates that the date must be entered in the dd/mm/yy format. But as soon as you press [Enter] to move to the next field, the date will change to whatever format is specified by the Format property.
[pic]
Creating Indexes
You can set an index on a field in a table to enable the database to find and sort records faster. Indexes can be set on a single field or on multiple fields; multiple-field indexes are useful when the first field might have duplicate values and you need a value from another field to create a unique key for the table.
If you have a primary key field, which is used to relate a table to foreign key fields in other tables, it has a unique index that won’t allow null values in the field and requires an entry in every record.
You can also index a field so that it allows duplicates — this type of index is often used in a field that might be used for searching or sorting, such as a Post Code field.
(Exercise 3: Creating Tables
1 To display the task pane and begin creating a new database, on the standard toolbar click the New button. Create a new folder under My Documents called Garden Company.
2 In the File menu, pick the General Tab, New Database and give it a name called GardenCo, save it into the folder called Garden Company.
3 To start building your table within your database, click New on the Database toolbar.
4 To create the table using a wizard, double-click Table Wizard and view some of the sample tables supplied with Access like the personal table format.
5 Change the table format to Business, then access the Customers table field properties — you can select one or many of the fields by using the > or >> buttons. Select all fields in the Customers table by clicking >> and then click Next.
6 You can select whether to use a primary key set by Access or pick your own. For this exercise select No I’ll pick my own primary key. The next screen displays the most suitable field to become your primary key in the drop-down list box — in this case CustomerID. Click Next and check the box Numbers I select when I add new records.
7 To enter data into your table when it’s created, and the default selection, pick the Enter data directly into tables check box and click Finish.
8 Your table is created. Click the Close button to close the table.
9 To create another table double-click Create table by using wizard, and pick Business and Employees. Move all the fields over by clicking >>, and then rename the State/Province field by clicking on the Rename field button and renaming the field to State. Accept the change and click Next.
10 Leave the table as Employees and allow Access to set the primary key for you by accepting the default setting. Click Finish to view the table, then close the window.
Refining How Data Is Displayed
1 To open and edit the Employees table, double-click Employees. To view the table’s design, click the view arrow on the left side, and pick Design view from the drop-down list. Notice the key icon indicating the primary key in the Design view of the Employees table.
2 To see all the possible data types, but not change any, click on any data type field in the second column and then the arrow to view all types. Press [Esc] to return to the original choice.
3 To view the properties of the EmployeeID field, move down to the General tab at the bottom, and look at the Field size which also has a drop-down list with various fields. Check some of the other fields you can alter for EmployeeID.
4 Check the format of the data type for Date Hired, and click in the Validation rule box to ensure that the date is a valid date. Type < Now() to ensure the date is at least equal to the current date — it can be earlier.
5 Move down to Validation text and type “Date must be today or earlier” — remember to put in quotes. You will be prompted to save the design of your table.
6 Change to Datasheet view from the toolbar, and enter the date at least one year ahead in the Date Hire field — you should get a message box saying the date must be today or earlier. Enter a valid data, eg today’s date and this year, and press [Enter].
7 To change data fields in another table, open Suppliers in Design view, change the State/Province field to just State by highlighting the text and typing State. Change the caption for it to also say State (see bottom of screen for caption).
8 To delete fields check the box next to the field and click [Delete]. Delete both Country and E-mail address — if you get a message saying one or more indexes will be lost, click OK.
9 Change the field size for SupplierName from 50 to 60 by editing the Format field property, then close your table and click OK to save changes.
Formatting Tables
1 To open and format the Customers table if not already selected, click Tables on the object bar. To adjust column widths, position your mouse pointer between the Address and City columns until the mouse pointer changes to a crosshair, then double-click — notice how the column width adjusts automatically.
2 You can adjust the row height this way also, by moving the mouse pointer between two records till the crosshairs appear, and dragging the line down to the height you want. When you release the mouse button all rows are adjusted.
Any column or row can be adjusted via the Format menu as well.
3 The Format menu can be used to hide/unhide columns and also freeze columns for easy viewing. Try hiding the first Name column, then unhide it.
4 To freeze columns click the Customer ID column header and, holding down the mouse button, pick First Name and Last Name. Go to the Format menu and select Freeze columns. Now go to the Format menu and unfreeze the columns to return your table to normal. Close the Customers table.
Filtering Data in a Table
Just as an index can help you sort data better and faster, you can also use the filtering tools to filter data on specific fields in your table datasheet and also use the sort facilities to sort data in a table, for example sorting data in alphabetical order.
To filter on a table, you need to decide what you want to filter for, for example text, numbers, dates, times, AutoNumber fields, Boolean Yes/No fields. There are four filter types:
◆ Filter by form is used with forms or table datasheets to filter for data using an empty field to specify the data you want to filter for.
◆ Filter by selection is used in forms or table datasheets, and retrieves only records with the filter value.
◆ Filter excluding selection is used in forms or table datasheets, and retrieves only records that do not match the filter value.
◆ Advanced filter/sort windows is a design window for creating more complex filters in forms and table datasheets.
The types of filters can be found in the Records menu in Microsoft Access and they can be applied to table datasheets, views, queries and forms. Reports will show any filtered results if a filter has been applied on the table datasheet. There are also a number of icons on the standard toolbar that perform the same function. There is even an option to remove any filters you may have created.
The filter icons look like upturned speakers and if you hover over each icon it will indicate what type of filtering it does.
Note: Filters are normally applied on a single table datasheet; if you need to apply more complex filtered analysis, a query might be better.
Let’s look at an example.
To quickly display a list of all the library books that had been borrowed from the category Music you would use a filter, as shown in the diagram.
[pic]
Notice that applying the filter only shows records relating to music now:
[pic]
Sorting Data in a Table
You can also use the sort facility, either from the Records menu or the icons above, to sort the data alphabetically A–Z or Z–A. There are two types of sorts you can do: simple and complex.
Simple Sorts
When you sort in Form view, Table datasheet view or Page view, you do a simple sort, which means you can sort all records in ascending or descending order (but you cannot use both sort orders on more than one field).
Complex Sorts
When you sort in Query Design view, the Advanced Filter/Sort window, Report Design view, Page Design view, PivotTable view or PivotChart view, you can perform complex sorts. This means you can sort records in ascending order by some fields and in descending order by others.
Creating Forms
Database forms offer an easy way to enter, modify and view data. Forms can be bound (associated) to data obtained from a table, a query or a Structured Query Language (SQL) statement. In this section, you’ll learn how to use the form features to create different types of forms for different purposes.
You can lay out forms in a way that allows you to see a large amount of data, perhaps all the fields in a record at a glance, using a variety of form controls to simplify entering and selecting values for fields. You can make forms that display several or many records at once. Additionally, you can embed subforms in forms, which allows you to work with data from two or more linked tables. You can also write code that responds to form events to perform error trapping or to help automate certain types of data entry.
Working with Form Views
You can open forms in a number of views for entering and editing data, as well as in a view that allows you to modify the form’s design. When you open a form from the Forms group in the Database window, you can click the Open button or the Design button on the Database toolbar (or choose Open or Design from the form’s shortcut menu). Or you can just double-click the form name in the Database window to open it in Form view to work with its data.
Clicking Design opens a form in Design view, where you can add or delete controls and modify various form properties. Clicking Open opens the form in the form’s default view. The following illustration shows the Format tab of the Form properties sheet, with the list of available views dropped down.
[pic]
You can set the default view in Design view by selecting one of the following choices for the Default View property of the form:
◆ Single Form — displays one record’s data. This is the default view for new forms.
◆ Continuous Forms — displays several records of data; can be a multirow display.
◆ Datasheet — displays multiple records of data in a datasheet format (similar to a Microsoft Excel worksheet).
◆ PivotTable — displays the form’s data as a PivotTable.
◆ PivotChart —displays the form’s data as a PivotChart.
You can switch views for a form by using the View selector on the Form (or Form Design) toolbar, or by choosing options on the View menu. Datasheet forms can’t display controls such as command buttons or option groups, and each record has a single row, so they are best suited to displaying a few columns of data, with minimal interactivity.
Creating Forms with the Form Wizard
The Form Wizard in Microsoft Access offers you several choices for creating forms automatically or semi-automatically, which can save you time. Remember that you can always fine-tune a wizard-generated form to get exactly the look you want.
When you use the Form Wizard to create your form, you choose one of several pre-packaged form layouts or Design view, which allows you to design the form from scratch. If you choose the Create Form By Using Wizard selection on the Forms tab of the Database window, you can step through the creation of a form based on a table or query, with the following form layout selections:
◆ Columnar
◆ Tabular
◆ Datasheet
◆ Justified
◆ PivotTable
◆ PivotChart
The other method of selecting a form layout is to click New in the Database window to open the New Form dialogue box, which offers the following form layout options:
◆ Design View
◆ Form Wizard
◆ AutoForm: Columnar
◆ AutoForm: Tabular
◆ AutoForm: Datasheet
◆ AutoForm: PivotTable
◆ AutoForm: PivotChart
◆ Chart Wizard
◆ PivotTable Wizard
◆ Custom Form Wizard
Creating Forms Manually Using Design View
If you need to create a form that isn’t laid out like any of the wizard selections, you can create a blank form in Design view and place controls on it just as you like. To create a form manually, open the New Form dialogue box (see next illustration) and select a table or query for the form’s record source.
[pic]
The screen then opens with a blank form as shown in the following image:
[pic]
Several toolbar buttons can be useful when you’re working with forms.
The Open and Design buttons on the Database toolbar allow you to open the selected form in either the default Form view or Design view, as shown here:
[pic]
When a form is open (in any view), you can switch to another enabled view using the View selector button on the Form View or Form Design toolbar, as shown in the next image.
[pic]
By default, all views are enabled for a new form. If any of these properties is set to No, that view won’t be available for selection on the View selector toolbar button’s drop-down list. If a view isn’t available, that is probably because the form wasn’t designed to display well in that view.
If you want to be able to switch to a specific view, set the view property to Yes in the form’s properties sheet.
Creating Data Entry Forms
Good database developers think carefully about the people who will be using their forms to enter data into the database, and design forms to meet the users’ needs.
With some databases, the same person who will be working with the data later on adds new records one at a time. For these database users, an easy-to-understand and attractive form is of primary importance.
The form should be laid out in a logical fashion (such as grouping name and address components together), and should have appropriate tools to help in selecting or entering data. Option groups, check boxes and combo boxes are very useful on these forms.
In other cases, data is entered in large batches by data entry operators and is later reviewed and modified by managers and others. Data entry operators need a form that is designed for quick data entry, which means limiting the use of controls (such as option groups and combo boxes) that require use of the mouse, and arranging controls in a simple (usually top-to-bottom) layout.
Whether a form is designed for review by managers or for full-time data entry, it should have features that ensure that data entered into the form is accurate.
Ensuring Data Accuracy
A standard form has its fields positioned in a logical way so that users have a visual guide to the sequence of data entry. For example, most users are familiar with forms that ask for name, address, city, postal code, phone number and e-mail address. It’s a logical and familiar progression. Replicating a familiar structure that people find comfortable will help make the database easier for users to use, which is a primary goal of a developer.
If you are creating a database for a customer, begin the process by interviewing your customer about what they want the database to do, and be sure to ask for any paper forms that are currently used to capture the data. By creating standard forms that follow the field sequence in existing paper forms, you’ll cut down on the time it takes users to get comfortable with the new process.
Replicating a paper form is feasible only when the paper form is itself well designed. Attempting to duplicate a poorly designed paper form won’t lead to a good form design. Additionally, you shouldn’t hesitate to use form features (such as combo boxes). Selecting a value from a list, rather than typing it in, eliminates the possibility of making a mistake when typing an entry.
For managers, making the standard form layout similar to the layout of a familiar paper form can be helpful. For data entry operators (as discussed earlier), the top-to-bottom layout of a quick data entry form facilitates rapid, interruption-free data entry.
In addition to creating a form that is easy to understand and based on a familiar interface, you can ensure that the form fits the user’s needs by including error-trapping features, such as input masks and validation rules.
Error trapping helps control the accuracy of data, making sure, for example, that dates are entered in the correct format, that text data is entered in text fields, that numeric values are in the appropriate range, and that numeric data is entered into numeric fields. If data is entered incorrectly, a good form will prompt the user to enter the data in the correct format.
The techniques listed below can make a form easier to use (and reduce errors in the process).
Default values — Enter the appropriate value (or expression) in the DefaultValue property of a field to automatically fill in the field when a new record is created. For example, entering Date() as the DefaultValue property of a Date field puts the current date into the field when a new record is created. Entering Now() puts the current date and time into the field, which is useful as a time stamp indicating when the record was created.
Input masks — Force data entry to adhere to a specific sequence of letters and/or numbers, such as telephone numbers and social security numbers. Telephone numbers, although containing no letters, should be defined as a text field. This is because telephone numbers may contain leading zeros and spaces.
The Input Mask Wizard has a selection of standard input masks, and you can create your own custom input masks as well. Input masks are particularly useful for IDs, product codes and other such highly structured data.
Validation rules — Check data after it is entered, and if data fails the validation test, a message pops up with information telling you what data is valid. For example, entering >Date+7 into a Date field’s ValidationRule property pops up an error message if the user enters a date earlier than a week from the current date.
ControlTip — Text is displayed in a floating yellow box when the mouse cursor is hovered over a control. For example, a txtNotes text box bound to a memo field might have a control tip saying Discussion Notes. Use this feature sparingly; it can be distracting if overused.
How to Design a Simple Quick Data Entry Form Using the AutoForm Selection
A simple top-to-bottom form layout, with all the controls lined up in a single column, although less than glamorous in appearance than other form layouts, is well suited to quick data entry. To create a simple quick data entry form, follow these steps:
1 Select a table in the Database window. This table will be the form’s record source.
2 Click the New Object tool in the Database toolbar to display the drop-down list.
3 Choose AutoForm. Access creates a simple data entry form.
[pic]
The form created with the AutoForm feature isn’t fancy, but it’s functional for quick data entry.
How to Design a Quick Data Entry Form by Dragging and Dropping Fields
If you want to be selective about the fields you include on your data entry form, you can use the drag-and-drop method to create your quick data entry form. Here are the steps:
1 Select a table in the Database window.
2 Click Forms, then click New. The New Forms dialogue box appears.
3 Design view is selected; click OK to create the new form.
4 If the field list is not visible, click the Field List button on the toolbar to display it.
5 Drag the fields you want to use from the field list to the form, as shown in the following image:
[pic]
You can create a quick data entry form by dragging the fields you want to a form displayed in Design view.
The next figure shows a standard form from a sample database, designed primarily for viewing and modifying information, although it could also be used for entering new records.
[pic]
The following illustration shows the quick data entry version of this form, designed for rapid entry of multiple data records. Notice that the quick data entry form is streamlined and includes only a selection of fields.
[pic]
(Exercise 4: Creating a Form Using the Wizard
1 To create a form, in the database windows, on the objects bar, click Forms.
2 To start the Form Wizard, double-click Create form by using wizard.
3 To select a table or query from Tables/Queries, click the down arrow and pick Customers.
4 Move all the available fields for Customers by clicking >>, then click Next.
5 Preview the Form layout types by moving down the list, select Columnar and press Next.
6 Now select a Form style, scroll down the list and pick Sumi Painting, click Next.
7 Leave the default selections in the next screen (fields you want to see in your form), and select Finish.
8 You can use the Record navigation bar to move between records. Now close your form.
Form Features and Properties
Forms have a great many properties, which are divided into five tabs on a form’s properties sheet, as shown in the next illustration.
Three of the tabs (Format, Data and Other) list subsets of the form’s properties. The All tab lists all the form’s properties in a single list. The Event tab lists the form’s events, which are used for writing VBA (Visual Basic Application) code.
The same properties sheet displays the properties of the entire form, a form section, or a control on the form. To open a properties sheet, click the Properties button on the Form Design toolbar, press Alt+Enter, or press F4 which is a new hot key in Access 2002.
You have two other (slower) ways to open the properties sheet: choose View, Properties, or right-click the form or control and select Properties from the shortcut menu.
In Microsoft Access you can open a form’s properties sheet in Design view, Form view and Datasheet view. Once the properties sheet is open, you can use the following techniques to display the appropriate information:
◆ To display the form’s properties, click the Form selector (the grey square in the upper left corner of the form, in Design view).
◆ When the form is selected, a smaller black square appears in the middle of the grey square, and the properties sheet’s caption is Form.
◆ To display the properties of a specific form section, click the section selector (the grey bar over the section). The figure below shows the form header selector being clicked to display the form header’s properties in the properties sheet. The properties sheet’s caption is Section: FormHeader (or the appropriate section name).
[pic]
To display the properties of a control, click the control. The properties sheet’s caption displays the control type and control name, such as Text Box: Text4.
To display the properties of a group of controls, select the controls. The properties sheet’s caption becomes Multiple Selection, and only the properties shared by all the controls are available.
Sections and Pages
All forms have a detail section. Forms can also have a form header and form footer or a page header and page footer. Headers and footers are paired sections; you can’t have a page header without a page footer, for example, although you can keep a section from being displayed by shrinking it to zero height.
To add form header and form footer sections to a form, choose View, Form Header/Footer. The Page Header/Footer option on the same menu toggles a form’s page header and page footer sections.
Form header and footer sections are quite useful. You can place controls such as record selector combo boxes or command buttons in these sections to perform actions that apply to the entire form. Command buttons are useful for new users to help them navigate or perform actions on the form, for example adding a print command button allows users to print the form more easily.
The figure below shows the primary form in a sample database, shrunk vertically to show only the form header and footer sections. The form header includes a record selector combo box (actually, two combo boxes; only one is visible at a time) and two command buttons, which are used to display one combo box or the other.
[pic]
If you shrink a form vertically, its header and footer sections won’t shrink; only the detail section shrinks. The form footer section has two command buttons: one to go to a new record (it could also be used to pop up a New Book form), and another to close the form and return to the main menu.
Format Properties
The properties displayed on the Format tab of a form’s properties sheet are related to the appearance of the form. Some of the more frequently changed properties are described in the following list:
◆ Caption. Use this property to change the text displayed in the form’s title bar.
◆ Default view. This property offers a selection of available views for the form.
◆ Allow…view. Set any of these four properties to No to make the specified view unavailable.
◆ Scroll bars. This property controls which scroll bars will be visible on the form.
◆ Record selectors. This property turns on or off the record selector (the vertical grey bar at the left of the form), which is used to select the entire record.
◆ Navigation buttons. This property turns on and off the navigation bar at the bottom of the form.
◆ Control box, Min/Max buttons, Close button, What’s This button. These properties control the small buttons in the upper left and right corners of a form.
You can get help for any property by positioning the mouse pointer in the property’s current value and pressing F1.
Data Properties
The following properties are the most frequently used properties on the Data tab of a form’s properties sheet:
◆ Record Source. This property specifies the table, query, or SQL statement that provides the form’s data.
◆ Filter. This optional property can contain an expression that specifies a field and a value for filtering the form.
◆ Order By. You can add a field or expression to this property to sort the form’s records.
◆ Allow. You can set these properties to allow or prohibit filters, edits, deletions or additions.
◆ Data Entry. This property specifies whether only the records entered in the current session are visible.
Other Properties
The following properties are the most frequently used properties on the Other tab of a form’s properties sheet:
◆ Pop Up. When you set this property to Yes, the form will always be on top of other forms.
◆ Modal. When you set this property to Yes, users can’t do anything else until they close the form. Use this property cautiously — modal forms can drive users crazy.
◆ Cycle. For users tabbing through form controls, this property offers a choice of cycling among all records, the current record (generally the best choice) or the current page.
◆ Menu bar, Toolbar, Shortcut menu, Shortcut menu bar. These properties allow you to specify one of four types of custom command bars.
◆ Has Module. This property specifies whether the form has an attached code module.
◆ Allow Design Changes. If All Views is selected, this property allows you to make design changes in all views. However, this doesn’t mean that you can (for example) resize a control in Form view; all changes must be made in the properties sheet.
Form Record Sources
The data displayed and modified on a form comes from its record source (also called a data source). When a record source has been specified for a form, that form is called a bound form. (Similarly, a bound control displays data from a specific field in the form’s data source.) Forms can be directly bound to data by simply selecting the data source (usually a table or query) as the form’s record source property. Once you select the record source, all of its fields are available for placement on the form, using the field list.
In addition to selecting a table or query for a form’s record source property, you can also use an SQL statement to get data for a form.
Tables and Queries
Specifying a table or query as a form’s record source is easy. If you create the form by using the Form Wizard, you can choose a table or query as the record source for the new form in the New Form dialogue box.
SQL Statements
Sometimes you won’t have a saved query that does the filtering or sorting you need, and you’ll need to create a record source for a form on the fly. You can do this with an SQL Statement. (You don’t have to be an expert in the SQL language — Access allows you to create SQL statements visually by using the Query Builder.)
How to Create an SQL Statement
1 Open the form in Design view.
2 If the properties sheet is not open, open it by pressing F4.
3 Click the Data tab of the properties sheet, and then click the Record Source property.
4 Click the Build button, which appears to the right of the property, as shown below.
[pic]
5 A message box appears, asking whether you want to create a query based on the table. Click Yes.
6 The Query Builder window opens, much the same as when you create a query, but without the Query Design toolbar.
7 The next figure shows a completed SQL Statement in the Query Builder.
[pic]
8 Close the Query Builder window, and click Yes in the confirmation message box that appears.
The SQL Statement now appears as the Record Source property for the form.
Working with Form Controls
The purpose of a form is to display data from the table, query, SQL statement, or recordset that is the form’s record source. But a blank form won’t display any data; you need to place controls on the form to display data from the fields in the form’s record source. Sometimes you’ll also want to add decorative elements to a form; you use controls for this purpose as well.
Controls are interface elements that are placed on a form (or report). Controls bound to fields (data-bound controls) are automatically placed on forms when you use the Form Wizard to create a new form. However, when you create a form using the Design view selection in the New Form dialogue box, you have to place all the controls on the form manually. You must always place decorative controls (such as lines and rectangles) on forms manually.
Placing Controls on Forms
To begin, you’ll need to create a form on which you can place your controls. Choose Insert, Form to open the New Form dialogue box. Select Design view from the list of selections, select the table or query containing the data to display on the form, and then click OK. The new blank form appears in Design view. You can manually place controls on a form in two ways: by using the toolbox and by using the field list.
Fields added to a form from the field list are always inserted as text box controls, with two exceptions: Yes/No fields are inserted as check boxes, and Lookup fields are inserted as combo boxes. If you want to insert text box controls bound to fields, the fastest way is to drag the fields from the field list; otherwise, click the tool icon for the type of control you want to use in the toolbox and then click the form to insert an unbound control, or drag the field from the field list to insert a bound control of the selected control type. Alternatively, you can click the appropriate tool, click the form to place a control, and then select the control’s Control Source property from its properties sheet.
For example, if you want to insert a field as a combo box, click the Combo Box tool in the toolbox and then drag the field from the field list to the form. A combo box bound to the selected field is placed on the form.
Data-bound Controls v Unbound Controls
Bound controls display data from a field in the form’s record source, whereas unbound controls are not linked to data (like caption and text boxes). The table below lists some of the most commonly used controls and indicates whether they can be bound or unbound.
Bound and unbound controls
|Control |Bound |Unbound |
|Label |X |X |
|Text Box |X |X |
|Operation Group |X |X |
|Toggle Button |X |X |
|Option Button |X |X |
|Check Box |X |X |
|Combo Box |X |X |
|List Box |X |X |
|Command Button | |X |
|Image | |X |
|Unbound Object Frame | |X |
|Bound Object Frame |X | |
|Page Break | |X |
|Tab Control | |X |
|Subform/Subreport |X |X |
|Line | |X |
|Rectangle | |X |
Using the Toolbox to Place a Control on a Form
To place a control on a form using the toolbox, click the tool you want to use and click on the form where you want to place the control. You can also click and drag on the form to control the size and position of the control as it is placed on the form.
Tools and Controls
The buttons you see in the toolbox are called tools. Most of them represent controls you can place on a form or report. To avoid confusion, this workbook will refer to the control-inserting tools on the toolbox as tools and the controls they place on forms as controls. For example, the List Box tool on the toolbox is used to place a list box control on a form. This type of control is useful when you have a number of items to display or people can interpret data in different ways, for example countries are often typed in differently (UK, United Kingdom, Great Britain; USA, U.S.A., America). You can see that putting the correct way you want the data displayed in a list box will ensure data entry people will pick the correct choice.
The tools in the toolbox are described in the following list. Some of the tools (Select Objects, Control Wizards, More Controls, and Toolbar Options) aren’t used to insert controls; they are tools you use to work with controls or with the toolbox itself.
[pic] Select Objects. When this tool is enabled, you can select controls on a form to move or resize them. This tool is enabled by default when no other tool in the toolbox is selected; it is temporarily disabled when another tool is clicked.
[pic] Control Wizards. A Control Wizard is a dialogue box that guides you through the process of associating data in a table or query with a control or creating a simple event procedure for the control. Clicking this tool enables Control Wizards so that when you select a control to add to the form, the appropriate Control Wizard appears to guide you through creating the control.
The following controls have Control Wizards:
◆ Combo Box
◆ Command Button
◆ Label
◆ List Box
◆ Option Group
◆ Subform/Subreport
[pic] Label. Use this control to display descriptive text. Labels are always unbound.
[pic] Text Box. This control displays data from a field, the results of an expression, or text entered by the user.
[pic] Option Group. This control presents the user with a set of choices, only one of which can be selected at a time.
[pic] Toggle Button. When you bind a toggle button to a Yes/No field, the button is up to indicate Yes or down to indicate No.
[pic] Option Button. Use this control to indicate a choice. (A black centre means that an option is selected.)
[pic] Check Box. This control also indicates a choice. (A check mark means that the item is selected.)
[pic] Combo Box. This control presents a drop-down list of selections to choose from and also allows text entry.
[pic] List Box. This control displays a full list of selections with no option for manual entry.
[pic] Command Button. This control performs an action by running an event procedure or a macro. A command button, for example, can control how people will navigate through records in a form, going forward, backwards, to the first record or to the last record.
[pic] Image. This control displays an unbound image (an image that is not stored in an Access table).
[pic] Unbound Object Frame. This control displays an unbound OLE (Object Linking and Embedding) object, such as a Microsoft Excel spreadsheet.
[pic] Bound Object Frame. This control displays a bound object, such as an image stored in the form’s record source.
[pic] Page Break. This control indicates a new form page.
[pic] Tab Control. This control displays information in groups, each on a tabbed page.
[pic] Subform/Subreport. This control displays information from a form, query or table.
[pic] Line. Use this control to add a decorative line to your form.
[pic] Rectangle. Use this tool to add a decorative rectangle to your form.
[pic] More Controls. Use this tool to open a list of ActiveX controls that you can place on a form.
[pic] Toolbar Options. This tool allows you to add or remove toolbox tools by means of a menu.
The list that you open with the More Controls tool includes all the ActiveX controls available to any applications on your computer; the list isn’t limited to the controls that work on Access forms.
Depending on your screen size and resolution, you might not be able to see all the tools in the toolbox. If that’s the case, a double chevron will appear at the bottom (or right side) of the toolbox. Click the double chevron to see the missing tools displayed on a submenu.
Using the Field List to Place a Control
The field list is a dialogue box containing a list of the fields in the table or query that is the form’s record source. You can place a control on a form manually by dragging a field from the field list. Access selects the control type based on the field type. As shown in the next figure, the Current field in tblCategories (the table used as the form’s record source) is a Yes/No field, so it’s placed on the form as a bound check box.
[pic]
The next figure shows a form with at least one of each type of control. Some controls appear in two modes: for example, the label control appears as both a stand-alone label and an attached label. Notice that the option button is also displayed in two modes: a stand-alone option button on the right side of the form and a set of option buttons in an option group on the left side of the form.
[pic]
Naming Controls
Before you start placing controls on a form, you should consider how they would be named. When you drag fields to a form, the controls are given, by default, the same names as the fields to which they are bound. For example, if you drag the LastName field to a form, the text box control that appears on the form is also called LastName. If you click a tool in the toolbox to place a control on a form, the control appears with a name such as Frame4 (the control type with a number appended).
Problems exist with both types of default names. If a text box has the same name as its field (LastName), you might get a circular reference error.
If a control is named Frame4, you have no clue as to what the name represents — for example, when you select a control from the drop-down control selector list on the Formatting toolbar in Design view. To avoid these problems, you might want to use the Leszynski Naming Convention (LNC) control name tags to identify controls, as listed in the table in the appendix.
The LNC tag is used as a prefix to a base name which is the field name for bound fields and a descriptive word or phrase for unbound controls. Finding the option group used to select a shipper is much easier if the control is named fraShippers rather than Frame4!
Control Name Requirements
Access imposes the following restrictions on control names:
◆ Control names can’t be longer than 64 characters.
◆ Control names can’t include a period (.), an exclamation point (!), a grave accent (`), or brackets ([ ]).
◆ Control names can’t begin with leading spaces.
◆ Control names can’t include ASCII characters 0 through 31 (control characters).
◆ Control names can’t include a double quotation mark (") (for projects).
Apart from these restrictions, you can use any combination of numbers, letters and punctuation marks (including spaces). However, simplifying control names by following these guidelines is recommended:
◆ Keep control names to 30 characters or fewer.
◆ Use only letters and numbers; avoid using punctuation marks and spaces.
Commonly Used Controls
Of all the controls you can place on a form, labels and text boxes are the most frequently used. The text box displays data from a single field, and the label’s caption describes the data. The controls most commonly used on forms are described in this section; and the less commonly used controls are discussed in the following section.
Labels
You can use labels in two ways: as stand-alone labels and as attached labels. Stand-alone labels add descriptive text to a form. For example, you might use a label to identify a group of controls, as shown in the form below, in which stand-alone labels identify groups of online IDs and telephone numbers.
[pic]
This form also contains attached labels. An attached label is linked to another control, usually a text box, combo box or list box.
Each text box control on the form has an attached label control. By default, when you place a text box, combo box or list box control on a form, it has an attached label control.
Label Format Properties
You can use the Special Effect property to give labels and text boxes a variety of styles. There are six Special Effect selections for labels and text boxes.
The format properties of labels (and text boxes) are interrelated in the following ways:
◆ If you set a label’s Special Effect property to Etched or Chiselled, its Back Style property will be set to Transparent.
◆ If a label has any Special Effect property setting other than Flat and you set the Border Width property to anything other than Hairline, the Special Effect property will be set to Flat.
◆ A label with the Etched or Chiselled Special Effect property setting should have its Border Style property set to Transparent. If you set this property to Normal, the visual effect of the Etched or Chiselled style will be lost.
◆ If a label has any Special Effect property setting other than Flat and you set the Border Style property to anything other than Transparent, the Special Effect property will be set to Flat.
◆ Labels with the Raised Special Effect setting should have the Back Style property set to Transparent. If you set this property to Normal, the visual effect of the Raised style will be lost.
Text Boxes
A bound text box control displays data from the field to which it’s bound; an unbound text box control can be used to accept a user entry that doesn’t need to be saved in a table or (more commonly) to display the results of a calculated expression.
For example, the following expression in the Control Source property of a text box displays the extended price for that record:
=[UnitPrice] * [Quantity]
This can be useful whether using forms or reports for performing and displaying calculated fields.
You can change the appearance of a text box by adjusting the Border Style, Border Width and Special Effect properties, just as you can modify labels.
Option Groups
An option group allows you to choose one of a set of alternative choices, such as a choice of North, South, East or West. Each alternative is represented by an option button which is selected.
The form’s user can select only one option in an option group at any time. Using an option group is like using the controls on a car radio — you can be tuned to only one station at a time, which is why option buttons are sometimes called radio buttons.
Option Buttons
An option button is a small circle with a black dot in the centre when selected and no dot when not selected. Within an option group, option buttons represent the choices the user can make. (Only one option button can be selected at a time.) Stand-alone option buttons work like check boxes, representing a Yes/No choice.
Check Boxes
Like option buttons, check boxes represent a Yes/No choice. Check boxes are familiar to users from paper forms, so they are the best choice for stand-alone controls used for Boolean fields such as Yes/No or True/False or other binary choices. Check box controls also have a property that cycles a check box control through three states instead of the usual two: Checked (True), Unchecked (False) and Dimmed (Null).
Combo Boxes
A combo box control has a box at the top for the user to enter text and a drop-down list for selecting an entry. This flexibility gives users a choice of typing in text or clicking a selection in the list. A combo box can be bound to a field in the form’s record source; this is the field that’s modified when the user makes a selection (or enters text) in the combo box.
Combo boxes also have two special properties for specifying the source of data for the drop-down list: Row Source Type and Row Source. The Row Source Type property of a combo box offers the following three choices, each of which determines the type of data that can be entered in the Row Source property:
◆ Table/Query. Lets you use a table or query from the current database (including linked tables) as the Row Source value. Using a table or query makes the list dynamic because it will always be up-to-date as records are added to or deleted from the underlying table.
◆ Value List. Lets you manually enter the list choices as a list of semicolon-separated values typed into the property box, such as Regular; Preferred; Deluxe. Value lists are handy if you want your form to present a small number of fixed choices.
◆ Field List. Shows the fields in a table specified in the Row Source property. This choice is useful when you need to select a field to set up a query condition.
You have several other options for setting the row sources of combo boxes. You can click the Build button next to the Row Source property to open the Query Builder window and create an SQL statement to use as the source of the combo box’s list.
Combo boxes have many specialised uses; some of the most common uses are described in the following sections.
List Boxes
List boxes are similar to combo boxes; they display a list of items from a table, a value list, or a record source assigned in code. However, list boxes don’t offer the opportunity to add a new entry, and they take up more space than combo boxes because they display multiple items at once.
Generally speaking, combo boxes are more useful than list boxes for selecting one item from a number of items on a form, but list boxes can be useful for selecting one of a small number of choices or for special purposes such as those discussed in the following sections. The choices appear as pop-up values, to ensure that only valid entries can be picked from the list values.
Command Buttons
You would use command buttons to run code to perform an action, adding the code to the command button’s Click event procedure. You would also use command buttons to help new users navigate or perform actions like a print command button, or a move to next record button. Command buttons are powerful because they can be used to add records, delete records, or simply navigate from one record to another, forwards, backwards, etc.
Tabs
Tab controls allow you to manage large quantities of data on a form without crowding controls together or creating a difficult-to-navigate multipage form. You can use a tab control in two ways: to divide the controls from a single record source into groups, displaying one group of controls on each page of the tab control, or to display data from different tables or queries on each page by using subforms, one subform per page of the tab control.
Subforms and Subreport
The subform/subreport control (usually called a subform control when it’s used on forms) can display data from a variety of source objects, such as other forms, tables or queries (or reports, only when this control is placed on a report). Typically, subform controls are used to display data from other forms because a subform whose source object is another form (as opposed to a table or query) gives you more control over what data is displayed and how it’s displayed.
The figure below shows the Source Object drop-down list for a Subform control. The list displays forms first (without a prefix), then tables, and then queries.
[pic]
Lines
As the name suggests, you use line controls to place lines on forms, usually to separate areas containing different types of controls. In general, lines are more commonly used on reports than on forms, but they can be useful on forms too, especially to separate controls into groups. A line can be vertical, horizontal or slanted.
As shown in the figure below, the six line controls on the right side of the Lines and Rectangles form all have a border width; they differ only in the special effect property. The Raised, Sunken, Etched, Shadowed and Chiselled lines do not display their border width or border colour property selection and, therefore, they look exactly the same.
[pic]
Rectangles
Rectangles can be solid or transparent, and they have the same border style and special effect property selections as lines. However, unlike lines, the six special effect choices for rectangles all look different. You can use the top margin and bottom margin properties of a label or text box control to manually adjust text to a vertically centred position.
Setting Control Properties
Controls (especially those that display text, such as text box controls) have an enormous number of properties. For the most part, you won’t need to change control properties except for those related to size, position, font and colour.
For more information about a control property, position the insertion point in the property box of the properties sheet and press F1 to open context-specific Help. For a list of commonly used properties see the appendix.
Command Buttons
You can add command buttons to a form or report to include special functions. Many command functions are possible — you could add a button to advance you to the end or to the beginning of the records, or to open a word-processing program when clicked.
How to Add a Command Button to Jump to the First or Last Record
1 Open your form or report in Design view. The toolbox appears:
2 Click the Control Wizards button in the toolbox. This button is at the top of the toolbox and resembles a wand and three stars.
3 Click the Command Button ‘button’. It resembles a small rectangle.
4 Move to the form and click in the spot where you want the button to go. Once you have completed this action the Command Button Wizard screen will appear.
[pic]
We wish to jump to the last record in the database when the button is clicked.
You are given a choice of six categories. Each category has a selection of actions to choose from. We are interested in record navigation and going to the last record. So you would select Record Navigation and the action will be Go To Last Record.
5 Click on Finish.
6 Save the form or report.
7 Test your command button by opening the form and clicking on the button.
How to Add a Command Button to Add a Record
Adding a button to add a record is very similar to the actions carried out previously. The steps are:
1 Open your form or report in Design view. The toolbox appears.
2 Click the Control Wizards button in the toolbox.
3 Click the Command Button ‘button’. It resembles a small rectangle.
4 Move to the form and click in the spot where you want the button to go. Once you have completed this action the Command Button Wizard screen will appear.
We wish to jump to add a record into the database. We are interested in record operations for this task and adding a record. So you would select Record Operations and the action will be Add New Record.
At this stage you could click on Finish, save the form or report and test the command. However there are further customisations we can perform using the wizard.
5 Click on the Next button.
[pic]
At this stage we can specify either a picture or text to appear on the button. Previously the wizard automatically put a picture onto the button. There are four standard pictures shown and you can select any of these; you could also browse for your own picture if you wish. We are going to select the text option this time so click on the Text radio button.
6 Click on Finish.
7 Save the form or report.
8 Test your command button by opening the form and clicking on the button.
How to Add a Command Button to Delete a Record
Adding a button to delete a record is carried out in the same manner as the adding a record actions carried out previously. The steps are:
1 Open your form or report in Design view. The toolbox appears.
2 Click the Control Wizards button in the toolbox.
3 Click the Command Button ‘button’. It resembles a small rectangle.
4 Move to the form and click in the spot where you want the button to go. Once you have completed this action the Command Button Wizard screen will appear.
Select Record Operations and the action Delete Record.
5 Click on Finish.
6 Save the form or report.
7 Test your command button by opening the form and clicking on the button.
(Exercise 5: Refining Form Properties
1 To edit the Customers form, click Customers, then Design view on the database toolbar. To change properties of labels etc, click the label you want to change, eg Customer ID; it is highlighted by black size handles around it.
2 To change the font and font size, select the down arrows and pick the Font Microsoft Sans Serif, with a Font size of 8 to change the Customer ID label.
3 To modify the properties of the text boxes, highlight the CustomerID text box, and on the View menu click Properties, or press F4 with the text box highlighted.
4 Change the Customer ID text box font to MS Sans Serif, with a Font size of 8, and a Font weight of Bold.
5 Go to the Edit menu and click Select All — now change all the properties at once in the multiple selection box, by picking Font Ms Sans Serif, Font size 8, Font Weight Bold and Back Style to Normal.
6 Let’s change to colour by clicking Back Colour; ellipsis appear (…) and if you click the button, a colour palette will appear. Pick the pale Yellow colour.
7 Move down to Special Effects and set the property to Shadowed.
8 Move down to Border Colour and type 32768 and press [Enter].
9 To deselect controls click anywhere in the form’s detail toolbar section.
10 To change a label name, highlight the label First Name, and in the properties box change it to just Name.
11 Select the Form selection box in the top left corner of the form, so you can insert a picture. From the Properties sheet for the form, click the scroll bar until you see Picture, then click in the box. Double-click the Garden Company folder and select the icon tgc_logo2.
(Exercise 6: Creating a New Database with Forms
Create a new database for the owner of a shop which sells music.
Design three tables to hold information about:
◆ Music, Type of Format (CD, MP3, Video, etc)
◆ Artist (Name, Type of Artist, eg Pop, Rock, Club)
◆ Album Title(s), Price, Number in Stock, Number Sold
You must make use of the following types of fields in their design:
◆ Date/Time functions
◆ Text/Number
◆ Boolean/Graphic
You should create a form to allow data entry to display the album table and update facilities for stock orders. The forms must contain the following:
◆ Text/List/Check and Image boxes
◆ Navigation command buttons and labels
Keep a copy of your work so you can perform other tasks on it in this section.
Creating Queries
You use queries to view, change and analyse data in different ways. You can also use them as a source of records for forms and reports. There are several types of queries in Microsoft Access.
◆ Select queries are the most common type of query. They retrieve data from one or more tables and display the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.
◆ Parameter queries when run display a dialogue box prompting you for information, such as criteria for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall between those two dates.
◆ Crosstab queries calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information — one down the left side of the datasheet and another across the top.
◆ Action queries are queries that make changes to or move many records in just one operation. There are four types of action queries:
— Delete queries. A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries you always delete entire records, not just selected fields within records.
— Update queries. An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 per cent for all dairy products, or you can raise salaries by 5 per cent for the people in a certain job category. With an update query you can change data in existing tables.
— Append queries. An append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you'd like to append it to your Customers table.
— Make-Table queries. A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other Microsoft Access databases or a history table that contains old records.
SQL queries are queries you create by using an SQL statement. You can use Structured Query Language (SQL) to query, update and manage relational databases such as Access.
Before diving into an examination of queries, you should be familiar with the query environment, in which you’ll encounter the Query Design view, and the SQL view. A query is often used instead of a simple filter to analyse and retrieve more complex data from more than one table. Filters are limited to retrieving data from one table only. This section focuses on these aspects of the query environment.
The Query Design View
The figure shows the Query Design view, in which you’ll identify the tables and queries that you want to include in your query. Notice that the title bar includes the query name and the query type.
[pic]
The upper part of the window displays a field list for each table or query you add to the query. The lower part of the window, known as the design grid, will define the query’s fields and accept expressions as criteria for limiting the results of the query.
The SQL View
The Query Design view is probably where most of us create and view queries. Access doesn’t directly interact with queries, however. Instead, Access must translate the Query Design view’s version of a query into Jet Structured Query Language (Jet SQL), one of the many versions of SQL.
SQL is a large and varied language, with many versions, but you don’t need to know everything about SQL to take advantage of it. Access translates your query into Jet SQL for you.
The Simple Query Wizard
If you’re new to queries, you should start with the Simple Query Wizard. Although the wizard won’t limit data, it will retrieve data and evaluate expressions, creating what’s known as a ‘simple’ query. For example, you could create a simple phone list for all your customers, but you couldn’t limit that list to customers with a particular postcode.
How to Use the Simple Query Wizard
To create a phone list for the Northwind customer database (the database that comes with Access), follow these steps:
1 Locate the Northwind.mdb database. By default, it’s installed in C:\Program Files\Microsoft Office\Office10\Samples. (If you don’t have this database installed, you’ll need to install it from your Microsoft Office XP CD-ROM.)
2 Make a copy of the database. (Some of the procedures in this section will modify the database, so it’s a good idea to make a copy.)
3 Open the copy of the Northwind database.
4 On the Database window’s Objects bar, click Queries, and then click New on the Database window toolbar. (Or on the Database toolbar, click the arrow next to the New Object selector, and then click Query.)
5 In the New Query dialogue box, double-click Simple Query Wizard. If you get a message stating that this feature isn’t currently installed, let Access install it.
6 In the Tables/Queries drop-down list, select Table: Customers.
7 In the Available Fields list, double-click CompanyName, ContactName, ContactTitle, and Phone (in that order) to add these fields to the Selected Fields list. Then click Next.
8 On the last page of the wizard, name the query qryPhoneNumbers, and click Finish. The results of the query are shown, and contain just the fields specified in the Simple Query Wizard.
How to Summarise Data
You can also use the Simple Query Wizard to summarise data, for example to compute the total of a field. To do so, you must specify a Number field in the query.
To use the Simple Query Wizard to summarise shipping costs, follow these steps:
1 On the Database window’s Objects bar, click Queries, and then click New on the Database window toolbar.
2 In the New Query dialogue box, double-click Simple Query Wizard.
3 In the Tables/Queries drop-down list, select Table: Orders.
4 In the Available Fields list, double-click CustomerID and Freight to add both fields to the Selected Fields list. Click Next.
5 Select the Summary option, and then click the Summary Options button.
6 In the Summary Options dialogue box, select the Sum check box and the Count Records In Orders check box. Click OK, and then click Next.
7 On the final page of the wizard, enter a new name for the query or accept the default, and then click Finish.
As you should see, this query returns one record for each customer. The Sum Of Freight field displays the total freight cost for each customer; the Count Of Orders field shows the total number of orders for each customer.
Retrieving Data
The simplest queries retrieve, group and organise data, but they don’t limit the results or evaluate expressions. You saw two examples of simple queries in the previous section. Using the Simple Query Wizard might be convenient, but you can create a select query without the wizard. In addition, whether you start with the Simple Query Wizard or build the query from scratch in the Query Design view, you can modify a query in a number of ways.
After you open Query Design view, the Show Table dialogue box should appear automatically. If the Show Table dialogue box isn’t displayed, click the Show Table button on the Query Design toolbar.
The Show Table dialogue box allows you to identify data sources that you want to add. If the data source is a query instead of a table, click the Queries tab. The Both tab will display both tables and queries. You can also add more than one data source.
There are different methods for adding fields to the design grid, as follows:
◆ Double-click the field in the field list. Access will add the field to the first blank column.
◆ Drag the field to a column.
◆ Double-click or drag the asterisk (*) in the field list to add all the fields from a table. The asterisk represents all the fields in the data source.
◆ In the design grid, choose a field from the Field drop-down list.
◆ In the design grid, type the field name in a column’s Field cell.
When the query is based on more than one table or query that have a field name in common, choose the appropriate table from the Table drop-down list. (If the Table row isn’t displayed choose View, Table Names.)
It is important that whenever you create a query, when you update the underlying table, you must re-run the query to ensure it captures all the data (including the new data). This is especially important if you have reports which display the results of a query, as the report will only show the query results, when the query was last run.
It is also important to note that deleting a query will not affect the underlying table, although it might affect reports which display the query results. In reports, deleting fields not required for the report will have no effect on the underlying table or query either.
Evaluating Expressions with Calculated Fields
In addition to selecting a specific set of records, a query can return the results of expressions for each record. You can add calculated fields to almost any query.
When working with a form, you can enter an expression in the ControlSource property of a control to create a calculated control. When you’re working with a query, you can enter an expression in a Field cell in the design grid. A calculated field, or calculated column, is any field that contains an expression.
An Order Details table is a good example of using a calculated field. The table contains details for each item in each order. Here is how we calculate the extended price of each item:
1 Create a new query in Design view.
2 Add the Order Details table to the query.
3 Add the OrderID and ProductID fields to the design grid.
4 In the Field cell of the third column, enter the expression:
ItemPrice: ([UnitPrice]*[Quantity])- ([UnitPrice]*[Quantity]*[Discount])
5 Running the query shows the extended price for each item.
(Exercise 7: Query Types
1 How can you order information in a table?
___________________________________________________________
___________________________________________________________
2 How does the Filter By Selection function work?
___________________________________________________________
___________________________________________________________
3 What is a query with parameters?
___________________________________________________________
___________________________________________________________
4 What is a criterion?
___________________________________________________________
___________________________________________________________
5 What is an action query?
___________________________________________________________
___________________________________________________________
6 What is a multi-table query?
___________________________________________________________
___________________________________________________________
7 What is a calculated field?
___________________________________________________________
___________________________________________________________
Simple Queries
Let’s look at some simple queries. Here is a sample table which displays hobbies:
[pic]
Suppose we want to query the table to find out a list of 12-year-olds who did skating. We could use a filter to filter for the criteria of 12 and skating, or we could run a query to return the required records. Look at the Query Design screen on the next page — see how we specified criteria for age and group to produce the required results.
[pic]
We designed the query to look for an age of 12 and a hobby group of skating, but changing the query to look for ages >12 or a hobby of “Skating” gives different results — notice it gives everyone whose age is greater than 12 and also Jane, whose age is less that 12 but who does skating. The ‘or’ criterion overrode the age criterion.
[pic]
In this query we specified that the age criterion should be >10 and the hobby is cycling — notice the use of the ‘and’ changes the data retrieved.
[pic]
Finally, in our simple query look at the query below:
[pic]
Here we have sorted our data with age in descending order and names in ascending order — notice we can sort on multiple fields within the same query. If there were any blank records they would appear at the top of the sorted list, but with Microsoft Access the new record (see the bottom of the Age sorted column) contains a zero until we specify otherwise — therefore it remains lowest.
Remember that when you query data it can be from one or more tables. Queries are useful when creating reports which require data from more than one table. For example, you have a report you have created from information for one table about student scores, but you want to include information from a related table which holds their date of birth. Create a query to gather the extra information you need for the report and then base the report on the query to retrieve the student scores and their dates of birth.
(Exercise 8: Sorting Information in a Table
1 To start sorting information, double-click the Customers table to open it in Datasheet view.
2 To sort data in the Customers table by Region, click in the first region record, and then go to Records, Sort, Sort Ascending on the standard toolbar. Alternatively you could click either of the Sort buttons on the standard toolbar.
3 You can sort multiple fields by highlighting the columns you want. If you want to sort by Region then City, you must place the Region column before the City column. Everything is always handled left to right in Access.
4 Highlight the Region column header and while still holding down the mouse button you will see a black line appear — move Region between Address and City.
5 Because Region is already sorted, and you want to include City in the sort, highlight Region, hold down the shift key and highlight City. Click the Sort, Ascending button on the standard toolbar. Now move Region back to its original position, by highlighting the column header, holding down the mouse button and moving it between City and Postal Code.
6 Save your changes, and close the Customers table window.
Limiting Data
You can limit the results of your query by specifying criteria expressions. In other words, your query will return only those records that satisfy some condition.
The criteria cell in the design grid accepts expressions that limit the records returned by the query. You might want to see only customers from a specific country. Or perhaps you want to view orders that were taken on a certain day. The reasons for limiting the results of a query are almost limitless.
Suppose you want to find all employees with the last name Fuller. One way to do this is to create a new query in Design view, add the Employees table to the query, add the LastName field to the design grid, and type Fuller in the Criteria cell.
The Jet SQL statement that’s created is shown here. Notice that it includes an expression that indicates that the LastName field must equal the string “Fuller”.
SELECT Employees.LastName
FROM Employees
WHERE (((Employees.LastName)="Fuller"));
The elements common to expressions are described below.
◆ Operators — the symbols that represent mathematical, comparison, logical and concatenation tasks. (The table below contains a list of operators.)
◆ Operands — the values you want the expression to evaluate. Operands come from more than one source, including literal values, identifiers and functions. The previous example consisted of just one literal, the string "Fuller".
Some operators that can be used in expressions
|Arithmetic | |Comparison | |Logical | |
|+ |Addition |= |Equals |Or |Meets any one |
| | | | | |condition |
|- |Subtraction |> |Greater than |And |Meets all conditions |
|* |Multiplication |< |Less than |Not |Negates argument |
|/ |Division |>= |Greater than or |Between |Falls between two |
| | | |equal to | |extremes and includes |
| | | | | |the extremes |
|^ |Exponentiation |>, then move back up to Tables/Queries to select another table. Pick Tables: Customers and select the fields Address, City, Region, PostalCode and Country and move them to the selected fields side, then click Next.
4 To accept the default options of showing the query in Datasheet view, click Next, and to modify the name of the query type Orders Qry, then click Finish. Access shows the query results in Datasheet view. If you want to view the design, click the design icon on the standard toolbar.
5 Remove the OrderID, CustomerID and EmployeeID fields from displaying by unchecking the tick in the show boxes. Change back to Datasheet view by clicking on the datasheet icon, to make sure the fields are not displayed.
6 To add a parameter to the query to select a range of dates to be displayed position the pointer on the criteria line for OrderDate. Type Between, [Type the Beginning Date:] And [Type the End Date:].
7 To run the query click the exclamation point icon on the toolbar. A parameter box appears with the text Type the Beginning Date — put in the date 1/1/01 and click OK. Another parameter box appears with the text Type the End Date — put in 1/31/01 and click OK. Only the orders between those dates are displayed. Save the query by clicking on the Save icon on the standard toolbar. Now press the black x in the top right corner to close the query.
(Exercise 10: Creating a Query in Design View
1 To start creating a query in Design view, click Create a query in the queries pane. You need to specify the tables you want to work with, so a Select Tables box appears with a list of the tables available. There are three tabs: Tables, Queries, Both — make sure it is on Tables.
2 Pick the Order Details and Products tables by double-clicking them, then close the Select Tables box by clicking on the Close button. To move fields down onto the query grid, position the mouse over the field names in the tables, and holding down the mouse button drag the following fields onto the query grid — Order Details: OrderID, Products: ProductName, Order Details: UnitPrice, Order Details: Quantity, Order Details: Discount — in this order.
3 To run the query, click the exclamation point on the standard toolbar. This will display the results in Datasheet view. To return to Design view, click the design view icon on the standard toolbar again.
4 To sort the query, for OrderID only, click in the Sort box, and pick Ascending from the drop-down arrow that appears. Now you will add a calculated field.
5 Click the first empty column, click the Build button on the standard toolbar. The Expression builder box appears. Move down and double-click Functions in the lower left pane.
6 Click the Built-in functions folder, in the middle column click Conversion, and in the end column double-click CCur — the expression is beginning to build in the upper pane.
7 Click on the bit in the upper pane. To replace this text with something else, click on Tables in the lower left column, pick Order Details as the table, and double-click UnitPrice from the fields available in the middle column. Your expression is nearly complete. Click on the asterisk * in the Expression builder pane, and a second appears.
8 Click on the and double-click Quantity in the middle of the bottom columns to replace .
9 You are going to add another * (at the end of the expression in the top pane, followed by Discount, which you add from the middle column by double-clicking it). Press the [Home] key to move to the beginning of the expression. Click OK to move the expression to the Query Design screen, then press [Enter] on your keyboard.
10 You need to give the expression a name, click on and change it to ExtendedPrice — run the query by clicking on the View datasheet icon on the standard toolbar. See the ExtendedPrice column. To save your changes, click the black x in the top right corner and click Yes to save your query.
11 When prompted for a query name, give it the name Order Details Extended and click OK.
How to Perform Calculations in a Query
1 To start performing calculations on a query, you need to build one. Double-click Create a query in Design view to open up the Query builder. From the table’s selection box, double-click Products, and then click the Close button.
2 While holding down the mouse button, move ProductID and UnitPrice onto the query grid. Click the Totals icon on the standard toolbar to add a totals line into the grid. Move you pointer down to the Totals row and click on the arrow, pick Count in the ProductID field and Avg in the UnitPrice field. Run the query by clicking on the exclamation icon on the standard toolbar. The results are displayed.
3 Now we will build another expression. In the next empty column, click the first field and type =UnitPrice*UnitsInStock then press [Enter]. Now in the totals row, click the down arrow and pick Sum. Change the name, by clicking on it, to Value of Inventory. Run the query to see the results by clicking on the exclamation icon on the standard toolbar. Close the query without saving it by clicking No when asked if you want to save changes.
The Update Query
A situation may arise where we need to update many records in a database table when certain information changes, or needs to be modified. An Access Update Query updates specified values in a table for all records or for those records that match specified criteria.
It is possible to update records manually in a database table, either via a form or through the tables datasheet, however this may take a very long time and is not very efficient. Changing records manually is not only time-consuming but it lends itself to errors as you update the individual records.
The best way to handle this type of mass change, the updating of many records in a database table, is to use an Update Query to make the changes to data in one operation. You will save yourself time, and eliminate the possibility of manual errors.
Important note: Due to the permanent effects of working with an update action query, you should always make a backup copy of your tables, or your database, before attempting this option.
How to Create an Update Query
1 Create a Select query to determine the records that will be updated. Apply any required query criteria, and view the data that will be updated by pressing the Datasheet button.
2 In the Query Design view, click on the drop-down arrow to the right of the Query Type button and choose Update Query.
3 After you are satisfied that the information to be updated is correct, run the query using the Run button.
Now consider the following example to illustrate the use of an Update Query.
One of your product suppliers has decided to increase the price of their products by 3 per cent (%) for all their supplied items. You have decided that you will use an update query to change all of the relevant records within your Products table.
Our original table appears as this:
[pic]
We need to update the Unit Price field, to update all records from our supplier (Exotic Liquids) as they have informed us of the 3 per cent price increase.
Creating the Update Query
Here are the steps that we follow to produce the required outcome.
1 Create a new query using the Products table and the Suppliers table. Include the fields that you are going to use to update the data (ProductID, ProductName and UnitPrice from the Products table, and CompanyName from the Suppliers table).
Note: We have also included criteria in the CompanyName field to limit the results to only those of the supplier that we are updating the records for.
Also, as shown in the image on the next page, you will see that we have included an additional field, just to test our expression. This will give us a value for the 3 per cent increase, to check that the results will be returned correctly. This field will be removed before we run the update, but we will use the expression later.
[pic]
2 To check what results this will produce, run the query by clicking on the Datasheet view button.
[pic]
3 When we are happy with the resulting data, we can switch back to Design view to convert the query to an update query.
From the Query Type button on the toolbar, select the Update Query option.
In the curUnitPrice column, in the Update To cell, type in the expression [curUnitPrice]*1.03 and press [Enter]. This expression will update the original unit price by 3 per cent.
The Query Design will now look like this:
[pic]
We now run this query using the Run button. This runs the action query to update the data in our Products table that meets the specified criteria. The warning dialogue box indicates the number of records that will be updated. Click Yes if you are happy to accept this.
[pic]
Important: Remember that the Update Query will permanently update records from the specified table(s), therefore it is very important that you have backed up the table(s) or database before running the query.
Creating an Append Query
An Append Query adds (appends) records from the database table that you are using to another database table. If you want to append records in a database, the table that you want to append records to must exist. Records can be appended to a table in the current database that you are working in, or into another Access database.
Access Append Queries are good for adding data to a different table where the data is based upon selection criteria. However, Append Queries are not always the most efficient way of adding records to another database. If you need to add all records and fields from one table to another table, the Append Query is not the best way to do it. Using the Copy and Paste options would be the best solution in this case.
When working with an Append Query you need to be aware of the following:
◆ If you are attempting to append records to another (external) database, you will need to know the name and location of that database.
◆ If there is a primary key field in the database table that you are appending records to, the records that you are adding will not be allowed to contain either a duplicate primary key value or a null value. Note: If this happens, Access will not append the data and you will not be warned.
◆ If you choose all of the fields using the asterisk (*) in the Query By Example (QBE) window in Access, you cannot also use individual fields in the same table. It will be assumed that you are attempting to add the field contents twice; therefore the data will not be appended.
How to Create an Append Query
1 Create a Select query to determine the records that will be appended. Apply the required criteria.
2 In the Query Design view, click on the drop-down arrow to the right of the Query Type button and select Append Query.
3 In the Append dialogue box, select the table that you want to append to from the list of tables in the current database, or select to append this to another database. You can either browse to this, or enter the full file path including the database name.
4 If needed, modify the query further so that the correct fields will be appended with the desired data.
5 Click on the Run button to run the action query.
6 When informed of the number of records to be appended in the Access dialogue box, click the Yes button.
7 Close the query, saving if required.
Creating a Delete Query
A Delete Query deletes records from a single database table or database tables. Of all of the different action queries available in Access (Append Queries, Update Queries, Make-Table Queries and Delete Queries) the Delete Query is one of the most dangerous. Unlike the others mentioned, the Delete Query will remove records from your tables forever.
As with the other types of action queries we have looked at, the Delete Query will work with a group of records that meet specified criteria. You can use the Delete Query to remove all records or only records that meet the criteria.
If you wish to use the Delete Query to remove records from multiple tables rather than just a single database table, you will need to ensure that:
1 You have relationships defined between related tables in your Access database.
2 You have enforced Referential Integrity for the relationships between your chosen tables.
3 You have opted to Cascade Delete Related Records for the relationship type.
If you are using the Delete Query to remove records from multiple tables that are related in a One-To-Many relationship without having defined the option to Cascade Delete Related Records, Access will only delete the records from one table at a time. If this is the case, you must delete the records from the Many side of the relationship first (to ensure against orphaning records), and then delete the records from the One side of the relationship.
Note: Due to the permanent effects of working with a Delete Action query, you should always make a backup copy of your tables, or your database, before attempting this option.
How to Create a Delete Query
1 Create a Select query to determine the records that will be deleted. Apply any required query criteria.
2 In the Query Design view, click on the drop-down arrow to the right of the Query Type button and choose Delete Query.
3 If needed, modify the query further so that the correct fields will be deleted with the desired new data.
4 Click on the Run button to run the action query.
5 When informed of the number of records to be deleted in the Microsoft Access dialogue box, click Yes.
6 Close the query, saving if required
Optimising Queries
Queries are a fundamental database tool, and you will want them to be as efficient as possible. Here are a few guidelines for producing high-performance queries:
◆ Avoid using the asterisk (*) method. A query runs faster if you list all the field names in the design grid.
◆ Limit fields. Include only the fields you really need. If you must include a field (say, for setting a criterion) but you don’t need to display the field, clear its Show check box in the design grid.
◆ Avoid nested queries. A nested query is a query based on another query.
◆ Avoid multiple SQL Group By clauses. A single Group By clause doesn’t affect a query’s performance, but several Group By clauses in the same query can slow performance.
It is important that you understand that queries can be run on a single table or on many tables to pull information together. This is often used when you want to create a report with information taken from many tables. For example, you can create a query that would return a list of overdue library books; not all fields from all tables need to be displayed — only those required for the borrower to see. The query could return the results of the top ten most popular books borrowed in a year, or generate a list of books to be returned by a certain date.
(Exercise 11: Using a Query
Take the database you created for the owner of a shop which sells music.
Design a query to retrieve results about the music shop:
◆ All music by a particular musician
◆ Average price of all CDs
◆ Total number of CDs sold on a particular date
You should save your work for future exercises.
Using Reports
After entering data into tables, using forms to make the data entry easier, you’ll probably want to print out the data in an organised fashion. Forms are great for reviewing data online, but they are designed primarily for entering and editing data; to print data in an attractive and easy-to-understand fashion, you need to create reports.
Reports can display data record by record, as in a name and address or mailing label report, or reports can be grouped by fields significant to users. A report will only display the data that exists in the table or query, so if you run a report and it displays details of employees’ names and address and then you delete several employee records, the report will display the new table details (excluding the records you deleted).
If your report is based on a query and you change the underlying table, you must re-run the query again, otherwise the report will still contain the original data from the original query (neither the query nor the report function know how to update the data in them — they rely on you to do that). Also, if you want to change the criteria of a query, you must re-run it again to update the report. For example, if you want the report to only show people with a salary of over £30K instead of all salaried people.
A grouped report (sometimes known as a banded report) divides the data into one or more groups, arranged hierarchically, usually suppressing any data that’s repeated in the report. For example, a grouped Customers report might group data first by country, then by region, and finally by salesperson, to compare sales in different countries and regions. Grouped reports are helpful for presenting an overview of data from a specific perspective.
Another common report type is the summary report. A summary report summarises data using a grouping function such as Sum or Count. Summary reports omit the details and give you just the totals (or counts) of the relevant data, such as total sales by region.
To design a report to display data in the most efficient way, you need to be familiar with the controls that work best on reports and with the special report features that let you group, sort and summarise data in reports.
Printing Forms v Printing Reports
Although a database allows users to print forms, when forms are printed the format generally isn’t as attractive or as useful as a properly designed report. Forms display all the data you want to see for each record, and they typically use colour (even if it’s only a light grey background) and other decorative elements to make it easier to enter, review and edit data one record at a time.
When you print a form, you get page after page of complete data, with coloured backgrounds, combo boxes, option groups and command buttons. These elements are distracting on a printed page and don’t have any practical use.
What you usually want in a report is a compact listing or summary of selected data, suitable for review on the printed page. Reports rarely show complete records. Instead, they typically display multiple records per page, often with subtotals or other summary data, grouped by significant fields.
A properly designed report lists relevant information (usually just a subset of the fields in the data source) grouped by one or more fields, in a simple black-and-white layout that uses lines or rectangles sparingly so that the report can be printed quickly and is highly readable.
Working with Report Controls
Forms and reports use the same toolbox, which means that you can place any type of control on a report that you can place on a form by clicking its tool in the toolbox. Keep in mind, however, that controls designed for on-screen interaction (such as combo boxes, option groups, command buttons and tab controls) are not suitable for reports.
Check boxes, although you can’t select or clear them on reports, are still useful for displaying Yes/No data. These controls are described in the following list.
[pic] Label — displays descriptive text. Labels are always unbound (that is, they aren’t bound to a data field).
[pic] Text Box — displays data from a field or the results of an expression.
[pic] Image — displays an unbound image.
[pic] Bound Object Frame — displays a bound object, such as an image.
[pic] Page Break — indicates a new report page.
[pic] Subform/Subreport — displays information from a form, report, query or table.
[pic] Line — adds a decorative line.
[pic] Rectangle — inserts a decorative rectangle.
Designing Reports
The look and feel of the reports you design will vary depending on the type of information you want to present. If you’re creating a simple report that lists book sales in a particular region of the United Kingdom, you can use the Report Wizard to create a basic two-column report that lists only regions and sales.
Plan to spend some time thinking through what data your report needs to display and how the data should be presented. This will save you editing and revision time later on. As you think about what you want to accomplish with the report you’re creating, consider these questions:
◆ Who will be using this report? What information will users expect to see, and how do they want the information to be laid out?
◆ Will you need to edit the report because the underlying data source (table or query) does not have the data type in the format you want? This is especially important for fields like dates. You may want to display the date in a different form from the table. The data type in the table may be a short date format (19/06/65) and you want to display it in the medium date format (19th June 1965). You will need to edit the report to achieve this.
◆ What report format is best suited to your needs? For example, will you use multiple columns to present your data? How many columns do you need?
◆ Does the report need group and/or report subtotals?
◆ Should the report include all the data in the record source or just summarised data?
◆ Should you use headings and graphic elements (such as lines and boxes) to help readers understand the report?
◆ If you’re creating the report for a client, do you need to replicate current paper reports your client is used to seeing?
Access has a Report Wizard to help you create reports. The various report types created by the wizard can save time, and one of the wizard’s selections may be exactly the type of report you need. Sometimes, however, you need a report type the wizard doesn’t create. When that’s the case, you can create a report starting with a blank report in Design view, adding controls to the report grid manually, and setting report properties as desired.
Selecting Report Record Sources
Reports can use the same types of record sources that forms use: tables, queries, SQL statements and recordsets. You select (or change) a record source for a report the same way you would for a form. For example, if you were designing a simple name and address list report, a table of contact data could be the report’s record source. A report that needs to list only a subset of data in a table could be based on a query that selects the output you need, for example selecting records for the Southeast sales region and the year 2006.
A report that displays only summarised data could be based on a Totals query that uses the Sum or Count function to present the total amount of sales in pounds or the count of orders. Just as for form record sources, SQL statements and recordsets can be used instead of queries as report record sources.
Using Grouped Reports
Reports have several other features, one of which is grouping. A grouped report divides data into bands that correspond to the values in fields that you select for each group level. An example is grouping a name and address report by Region and then by LastName.
You can have up to ten group levels in a report, but reports rarely need more than three or four levels, and often one group level is all you’ll need.
The Report Wizard has a Grouped Report selection that you can use to create a grouped report, but before you start the wizard you should create a query to use as the grouped report’s record source. This query should contain all the tables you need to obtain the data you want to include in the report, with just the fields needed for the report selected as query columns.
The fields used to create report groups are also fields used for sorting. You can use the Sorting and Grouping dialogue box to sort the report data as you want. This is especially useful if the report is based directly on a table.
For example, to create a report in the Crafts database that lists books and videos, grouped by author, you need a query that contains tblBooksAndVideos, tblBookAuthors and tblAuthors, as shown in the figure below. These three tables define the many-to-many relationship between books and authors.
[pic]
Using Wizards and AutoReport to Create Reports
The Report Wizard has several selections for creating reports of different types. To start the Report Wizard, double-click Create Report By Using Wizard on the Reports tab of the Database window. This wizard creates grouped reports in a variety of formats.
There are several other ways to create a new report: If you click New at the top of the Database window, the New Report dialogue box appears, offering several choices.
In addition, the New Object selector on the Database toolbar offers an AutoReport selection which creates a plain columnar report. The table lists the available choices for report layouts and indicates where the selection is available. The sections that follow describe how to create reports using some of these layouts.
Available report layouts
|Layout selection |Available from |Description |
|Chart |New Report, Chart Wizard |Data from selected field is charted in a variety of chart formats, |
| | |using MS Graph |
|Columnar |New Report, AutoReport: |Fields are laid out top to bottom, with visible borders around each |
| |Columnar |text box control |
|Crosstab |New Report, Design View |Generates a report based on a previously prepared crosstab query |
|Datasheet |New Report, Design View |Lets you manually create a report with a datasheet subform |
|Embedded Subreport |New Report, Design View |One or more embedded subreports are manually placed on the report; |
| | |the subreports can be linked or unlinked |
|Free-Form Report |New Report, Design View |No controls are automatically placed on the report; you can place |
| | |controls where you want them. |
|Grouped |Report Wizard, choice of |Groups data by selected fields, with a choice of indented or |
| |six layouts |left-aligned formats |
|Label |New Report, Label Wizard |Data is formatted to be printed on one of a number of commercial |
| | |labels or according to a custom label format |
|Multicolumn |New Report, Design View |Report is manually set up as a multicolumn report in the Page Setup |
| | |dialogue box. |
|PivotChart |New Report, Design View |Creates a report with an embedded PivotChart subform, formatted to |
| | |remove interactive elements |
|PivotTable |New Report, Design View |Creates a report with an embedded PivotTable subform |
|Tabular |New Report, AutoReport; |Fields are laid out left to right, with column headings |
| |Tabular | |
Creating a Columnar Report Quickly
To create a simple columnar report in the minimum amount of time, select a table or query in the Database window, and then select AutoReport from the New Object selector on the Database toolbar, as shown in the following illustration:
[pic]
The report is created automatically. It displays all the fields in the selected table or query, arranged in a single column from top to bottom.
Grouped Reports
When you use the Report Wizard to create a grouped report, you have six choices for the report layout. You can identify the layout you prefer by looking at the layout illustration on the wizard screen. Whichever layout you select, you’ll probably need to modify it somewhat to create an attractive report.
To create a grouped report, first prepare a query (such as qryBooksVideosAll) containing all the data needed for the report groups, then follow the given steps. If you have forgotten how to do this, go back to the section on creating queries.
How to Create a Grouped Report
1 Click the Reports tab of the Database window, and click New at the top of the Database window.
2 In the New Report dialogue box, select qryBooksVideosAll in the Record source drop-down list. Select Report Wizard from the list of report types, and then click OK.
3 Select the fields to place on the report, as shown in the figure. Click Next.
4 Select the table that you want to use as the primary table. In the figure below, tblAuthors is selected as the primary table, so the report’s top grouping will be by author name. Click Next.
[pic]
5 Select further grouping levels, if desired. PublisherCode has been added as a group field. The resulting report will be grouped by publisher. Click Next.
6 You can select up to four sort levels. Selecting the sort field Title will sort the Publisher group by book title. Click Next.
7 Choose a layout from the Layout option group. You can also format the report for portrait or landscape orientation. Click Next.
8 Select a style for the report and then click Next.
9 Give the report a title. The title is used as the report name, as its caption in print preview, and as a field in the report header. Click Finish.
10 Note: Layout options can vary between versions of Access.
Columnar Reports
Columnar reports, though plain in appearance, are useful for proofreading and checking data; their one-field-per-row layout leaves lots of room for marking corrections. The quickest way to create a columnar report is to click the New Object: AutoReport button on the toolbar. However, you can also create a columnar report by following these steps:
1 Click the Reports tab of the Database window, and then click the New button at the top of the Database window.
2 In the New Report dialogue box, select a table or query from the Record source drop-down list, and then select AutoReport: Columnar from the list of report types.
3 Click OK. The rather ungainly report shown below is created.
[pic]
(Exercise 12: Report Functions
1 What is a report?
___________________________________________________________
___________________________________________________________
2 Can you create calculated controls in a report?
___________________________________________________________
___________________________________________________________
3 How do you specify which pages of a report are to be printed?
___________________________________________________________
4 What is a subreport?
___________________________________________________________
___________________________________________________________
5 What is a main report?
___________________________________________________________
___________________________________________________________
6 What does creating a report from scratch mean?
___________________________________________________________
___________________________________________________________
7 What is a pivot table?
___________________________________________________________
8 What is a pivot chart?
___________________________________________________________
Creating Reports Manually
In the previous section you learned how to use the Report Wizard and the New Report dialogue box to generate reports using Microsoft Access’s pre-designed report styles. But not all the reports you create will fit into one of the automated report styles. Some reports you’ll need to create manually.
Free-form Reports
A free-form report is one that you put together any way you like. No controls are placed on the report by default; you place controls on the report manually.
How to Create a Free-form Report
1 Click the Reports tab of the Database window, and click New at the top of the Database window.
2 In the New Report dialogue box, select Design view for the report type, and select a table or query as the report’s record source.
A blank report opens in Design view. Use the toolbox to place controls on the report in the layout you want, format them as desired, and then open the Sorting And Grouping dialogue box to set up report groups and sort the data as you prefer. This might include specifying how you want data displayed in a number field (how many decimal places you want to display). This can be done using the properties of each field you add to your report from a table or a query. Let’s look at an example.
Here we have a simple report based on the hobbies query, but with a field added for the cost of the hobby — notice the properties of the field have been set to two decimal places. (Auto is the default of two decimal places.)
The report generated from this query will display the cost of the hobby to two decimal places, but we would prefer it to display no decimal places. To do this we must go into the report’s Design view and highlight the fields we want to change — Cost of hobby. Notice when a field is highlighted it has positioning markers around it. If we right-click, we will see the properties for that field:
[pic]
Now the report is in the format we want — see below:
[pic]
Notice there are five tabs and the Format tab allows us to change the decimal places from auto (two places) to zero places.
You will see there are many format properties you could change like colour, height, back style, etc. Whether it’s a report or a form you can change the format of any field type to suit your display or printing requirements. Look at the other tabs to see what other types of formatting options you can display — for example you might want to display a field in a different colour if the value is zero. You would do this in the Data tab.
Formatting Reports
Reports generally don’t need as much formatting as forms do, but the Report Wizard does offer you a number of formatting choices. As you can with forms, you can apply a selection of styles to reports by using the AutoFormat selection on the Format menu. You can also create a report template with controls and other features set up the way you want them to be when you create a report in Design view.
Using Report Templates
New reports in Design view are based on the Normal report template, which has a white background and uses the Arial 8-point font for controls. For simple data-checking or review reports, this look might be fine.
However, in some cases you might want to customise the report template to go along with the look and feel of other publications in your department. For example, you might want to:
◆ include your company logo on the report
◆ establish standard formatting for controls in different sections of the report, much as you use different styles for various heading levels in a Microsoft Word template
◆ put controls displaying the page number, report name, or date and time printed in the page footer
◆ use colour (sparingly) for emphasis (if you have a colour printer) — for example, in a control displaying the report caption or on lines used to separate groups of data
◆ add a line identifying your department name, website, phone number and other information.
You can make your own report templates to use when creating reports by following these steps (remember to substitute colours, fonts and other formatting elements according to your own preferences):
1 Click the Reports tab of the Database window, and click New at the top of the Database window. In the New Report dialogue box, select Design view for the report type, and then click OK. This creates a blank report that you’ll use as the basis of your report template.
2 Although leaving the report background white makes it easier to read and quicker to print, you can select a background colour if you want (and if you have a colour printer), by using the Build button to the right of the BackColour property of the report’s detail section or other section.
3 Place the controls you plan to use on the report, format them as desired, select them all, and choose Format, Set Control Defaults. This ensures that controls placed on a report made from the template will have the same formatting.
4 Save the blank report with a name such as rtmpReport.
5 Open the Options dialogue box (Tools, Options), click the Forms/Reports tab, and enter the new template’s name in the Report Template box.
Using AutoFormat
You can use the AutoFormat dialogue box (opened by choosing Format, AutoFormat in Design view) to apply a new format to a report. These formats are the same ones that you can select within the Report Wizard.
The AutoFormat feature works differently with reports than with forms. For example, you can’t update one of the built-in styles with your own custom style, as you do with form styles. However, you can create a new AutoFormat style based on the currently open report, as shown in the next figure. You simply have to use a slightly different technique for report AutoStyles.
[pic]
Clicking OK in the Customise AutoFormat dialogue box opens the New Style Name dialogue box, in which you can enter the name of the new style.
After you save the new style, it will be available, along with the built-in styles, from the AutoFormat dialogue box.
Understanding Report Features and Properties
As you’ve seen, reports share many of the same properties as forms. In this section, we’ll look at a few useful properties unique to reports. You can use report properties to control the way headers and footers are displayed, for example, or to establish report groups, sort report data, choose the column format for report sections, and determine whether you want repetitive data to be hidden or displayed. To get help for any report property, place your insertion point in the property box on the report’s properties sheet and press F1 to open context-specific Help.
Report Format Properties
The report format properties that differ from form properties are located on the Format tab of the report properties sheet. These report format properties are described as follows.
◆ Page Header, Page Footer. These properties offer you a choice of All Pages, Not With Rpt Hdr, Not With Rpt Ftr and Not With Rpt Hdr/Ftr. Choose the appropriate setting to prevent the page header (or footer) from being printed on the same page as the report header (or footer).
◆ Grp Keep Together. This property offers you a choice of Per Page or Per Column, allowing you to decide how groups should be kept together.
Sorting and Grouping
Use the Sorting And Grouping dialogue box (View, Sorting And Grouping) to set up report groups, sort report data, and adjust various group properties.
There are three group levels, of which LastNameFirst is the top level. The Sort Order column for each group level offers a choice of Ascending (the default) or Descending. Looking at the bottom of the dialogue box, you can see the following properties for this group level:
◆ Group Header is set to Yes so that the group has a group header section.
◆ Group Footer is set to Yes so that the group has a group footer section.
◆ Group On is left at the default setting Each Value so that a new group is started with each author.
◆ Group Interval is set to 1. You can set this property to another value to start a new group for every five records (or whichever number you choose).
◆ Keep Together is set to Whole Group, to keep the LastNameFirst group from being broken up if there isn’t enough room for the whole group on a page.
(Exercise 13: Creating a Report Using a Wizard
1 To select a table to associate with your report, click Products from the table list, and then go to the Insert menu and pick Report. The Report Wizard pops up. Double-click Report Wizard.
2 The next screen prompts you for which fields from the Products table you want on your report, click ProductName, QuanityPerUnit and UnitsInStock from the available fields side, by clicking the > to move them over to the selected fields side. Now change to the Categories table by clicking the down arrow and choosing Categories from the Table list. We want to insert the CategoryName under the ProductName in the selected list. Position the mouse pointer on ProductName, then double-click the CategoryName field on the available side. It should move over to the selected side. Click Next to move on.
3 Accept the next screen’s defaults and click Next. We want a group order so double-click ProductName to add it to the top of the Grouping screen on the right side — now click the Grouping options button. This will allow you to set grouping intervals in your report.
4 In the groupings option screen, in the Grouping Intervals drop-down list box, click 1st Letter and then click OK. Click Next to move onto the next Report screen.
5 Select ProductName in the first Ascending field, by using the drop-down arrow in the top box, then click Next. Select a layout for your report, pick the Outline 1 check box and click Next.
6 In the next screen, select a style for your report, pick Compact, and then click Next. Give your report the name Alphabetical List of Products and click Finish. Your report is displayed in preview mode. Close your report.
(Exercise 14: Creating a Report Manually
1 In the Query menu, click Sales by Category to highlight it, and from the Insert menu pick Report. To open a report in Design view, pick Report Design View and click OK in the Report dialogue box.
2 To define the Report Header/Footer sections, on the View menu click Report Header/Footer option. To pick a field that will be used for grouping, go to the View menu and pick Sorting/Grouping. In the Sorting/Grouping Expression box, pick CategoryName from the field list and change the Group header option to Yes by clicking the down arrow in the text box in the bottom half of the window. Close the Sorting/Grouping window by clicking on the red x in the top corner.
3 The report selector is visible in the top left corner of the report (a little black square). To change properties for the report, go to the View menu and click on Properties. Change the property settings from Grid X and Grid Y to 10 and then move to the Report Header section by clicking in the small rectangle to the left of the Report Header bar.
4 Change the Height property from 1.25” to 1” and set the Height property for each section as follows:
□ Page Header, Detail and Report Footer all set to 0”
□ Category Name Header set to 2”
□ Page Footer set to .5”
5 To save the report, click the Save icon on the standard toolbar, and in the Save As dialogue box, give the report the name Sales by Category and click OK.
6 To view the top of the report, click once in the scroll bar on the left side of the screen. You’re going to insert a label from the Toolbox Wizard (second icon from the top) and position the mouse in the middle of the Report Header section and type Sales by Category in the Label box.
7 You will amend the font properties of the label by opening up the View menu, Properties box (if it’s not still open). Change the Font to Times New Roman, the Font Size to 20, the Font Weight to Bold and the Font Italic to Yes. Click the Format menu, size, to fit to increase the size of the label in the Report Header section.
8 You will insert a date in the Report section under the label you just did. From the Insert menu, pick Date and Time, and when the Date and Time dialogue box opens, make sure you uncheck the Time section — we only want the date, not the time as well. Click OK to proceed. Move the Date field box under your label Sales by Category — adjust it until it is about the same width as the Sales by Category label and the date is centred — choose Centred text from the icon in the Format toolbar.
9 Save your report by clicking the Save icon on the standard toolbar, and view the report by clicking on the Preview icon to the right of the Save icon on the standard toolbar. Close your report.
How to Preview and Print a Report
1 To open an existing report in Design view, click Alphabetical Listing of Products in the Reports screen and click the Design icon on the top of the screen.
2 Preview your report by clicking on the File menu, Print Preview. To view the whole report on screen, click the zoom arrow and choose Fit from the drop-down list — the report is now visible on-screen. There are different views for the report — in the View icon, down arrow, on the standard toolbar see how each view is different, click Close to return to the Report design screen.
3 To print the report, go to the File menu, click Print, and then choose if you want to print the whole report or selected pages, and how many copies you want.
Report Sections
Reports can have up to ten group levels, but generally two or three are sufficient. Each report section has its own properties sheet, with several significant properties on the Format tab. Each report section has a selection of the following properties (along with other properties shared with forms):
◆ Force New Page — offers a choice of None, Before Section, After Section, and Before and After Section. Use this property to specify where a page break occurs.
◆ New Row Or Col — offers a choice of None, Before Section, After Section, and Before and After Section. Use this property to specify where a section break occurs on a multicolumn report.
◆ Keep Together — offers a choice of Yes or No, for preventing a page break within the section.
◆ Visible — allows you to choose whether the section is visible.
◆ Can Grow, Can Shrink — offer a choice of Yes or No. These properties specify whether the section can grow or shrink to accommodate data in text boxes that have grown or shrunk.
◆ Repeat Section — offers a choice of Yes or No. If you set this property to Yes, the header section repeats when the group data is advanced to another page so that every page will have a header.
Report section properties, and the properties of report controls, can be set from VBA code to display or hide information when certain conditions are met. Additionally, you can use the conditional formatting feature of text box controls to set their properties conditionally.
Placing Totals on Reports
It is easy to place subtotals and totals on grouped reports. All you have to do is put a text box under the column of figures you want to add and then use an expression such as the one below as the text box’s control source, where cost is the field you want to sum. (You can use Count instead of Sum to get a count of items.)
=Sum ([Cost])
Make sure you reference the field name in the expression, not the control name (the control bound to the Cost field should be named txtCost); otherwise, you might get a reference error. Use the same control source expression in any group footer in which you want a subtotal and the report footer; the only difference is in the name of the control. The correct group subtotal or report total will be displayed in each section.
To get a row total rather than a column total, use an expression such as the following for a text box placed at the right side of the detail section:
=[Jan] + [Feb] + [Mar] + [Apr] + [May] + [Jun] + [Jul] + [Aug] + [Sep] + [Oct] + [Nov] + [Dec]
Database Macros
A macro can be used in a database just like any other application to:
◆ capture a set of actions to automate tasks in a database; for example you can run a macro that prints a report when a user clicks a command button
◆ run a single command (or use a single icon) to perform a series of tasks
◆ create a series of instructions to update, change or delete data from a database
◆ be run on specific dates (the rest of the time they are not active; they lie dormant until required).
You can run a database macro directly, in a macro group, from another macro or an event procedure, or in response to an event that occurs on a form, a report, or a control on a form or report. Macros are an easy way to take care of simple details such as opening and closing forms and running reports.
A macro virus is a type of computer virus that’s stored in a macro. To help protect against macro viruses, you should purchase and install specialised antivirus software. You can also use a list of trusted sources or publishers who issue digital certificates. These certificates are used to confirm their trustworthiness and a digital certificate will include the issue dates (to and from), the e-mail and web address of the publisher.
The levels of security to help reduce macro virus infection are as follows:
◆ Very High — This setting is not available in the Security dialogue box in Microsoft Office Access 2003. It is possible, however, to use system policies to set the security level in Access to Very High. When the security level is set to Very High, Access cannot open any Access database or Access project files.
◆ High — You can open files that have been digitally signed and that you confirm are from a trusted source. Before deciding to trust a source, you should confirm that the source is responsible and uses a virus scanner before signing macros.
◆ Medium — A warning is displayed whenever a file is opened from a source that is not on your list of trusted sources. You can choose whether to open the database.
◆ Low — If you are sure that all the files and add-ins you open are safe, you can select this option. At this security level, macros are always enabled when you open files.
How to Create a Macro
1 In the Database window, click Macros under Objects.
[pic]
2 Click the New button on the Database window toolbar.
3 Add an action to the macro.
□ In the Macro window, click the first empty row in the Action column. If you want to insert an action between two action rows, click the selector for the action row just below the row where you want to insert the new action, and then click Insert Row on the toolbar.
□ In the Action column, click the arrow to display the action list.
□ Click the action you want to use. This could be to run a command, open a form, a table or any other type of action.
□ In the lower part of the window, specify arguments for the action, if any are required. For action arguments whose settings are a database object name, you can set the argument by dragging the object from the Database window to the action's Object Name argument box.
□ Type a comment for the action. Comments are optional.
4 To add more actions to the macro, move to another action row and repeat step 3. Microsoft Access carries out the actions in the order you list them.
To make a command button do something on a form, you write a macro or event procedure and attach it to the button's OnClick property.
(Exercise 15: Design a Report
Take the database you created for the owner of a music shop.
Design reports to retrieve both single and multiple fields and values about music:
◆ Produce an ‘All CDs’ report, with suitable image, labels and text boxes.
◆ Produce a report listing the average price of CDs and the total number of CDs in stock.
Finally
The candidate should (after consultation with their tutor) be able to do the assessment for this Unit.
This completes all the learning Outcomes for the PC Passport Database subject. Microsoft Access is one of a range of databases that could be used to achieve the same results.
Appendix
Data Types
◆ Text — Text data, including numbers that don’t require calculations. Text can be up to 255 characters in length. You may choose to use a text field in place of a number field, for example using the text No Credit if there is a zero credit limit.
◆ Date/Time — Dates and times
General date format: 19/06/1994 17:34:06
Long date format: 19th June 1994
Medium date format: 19-Jun-94
Short date format: 19/06/1994
Long time format:17:34:06
Medium time format: 05:34PM
Short time format: 17:34
◆ Currency — Currency (monetary) values. Also used to prevent rounding off during calculations.
◆ AutoNumber — Creates unique sequential (incrementing by 1) or random ID numbers and replication IDs.
◆ Yes/No — Two-valued data, such as Yes/No, True/False. Null values are not allowed. These are referred to as Boolean data types.
◆ Hyperlink — Hyperlinks (either URLs or UNC paths).
◆ Memo — Longer blocks of text, up to 65,535 characters.
◆ Number — Numeric data that might be used in calculations. There are different types:
Byte — Whole numbers from 0 through 255
Integer — Whole numbers from –32,768 through 32,767
Long Integer (default) — Whole numbers from –2,147,483,648 through 2,147,483,647
Single — Numbers from –3.402823E38 through –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values
Double — Numbers from –1.79769313486231E308 through
–4.94065645841247E–324 for negative values and from 4.94065645841247E–324 through 1.79769313486231E308 for positive values
Decimal — Numbers from –1038–1 through 1038–1 (ADP), or from
–1028–1 through 1028–1 (MDB)
Note: If choosing a currency or number data type negative numbers will appear in a different colour, normally red.
LNC Control Tags
|Bound Object Frame |frb |
|Check Box |chk |
|Combo Box |cbo |
|Command Button |cmd |
|Custom |ocx |
|Hyperlink |hlk |
|Image |img |
|Label |lbl |
|Line |lin |
|List Box |lst |
|Option Button |opt |
|Option Group |fra |
|Page (on a tab control) |pge |
|Page Break |brk |
|Rectangle (shape) |shp |
|Subform/Subreport |sub |
|Text Box |txt |
|Toggle Button |tgl |
|Unbound Object Frame |fru |
Commonly Used Form Properties
The following list introduces a number of the most common control properties, with a brief description of their function.
Back Colour. Sets the control’s background colour.
Back Style. Specifies whether a control is opaque or transparent.
Border Colour. Sets the colour of the control’s border.
Border Style. Sets the style of the control’s border.
Border Width. Sets the width of the control’s border.
Bottom Margin, Left Margin, Right Margin, Top Margin. Set the amount of space between the bottom, left, right, or top edge of the control and the text it displays.
Bound Column. Sets the column of a combo box or list box that is bound to a data field.
Caption. Sets the text displayed on a control.
Column Count. Sets the number of columns in a combo box or list box.
Column Heads. Specifies whether to show column headings in a combo box or list box.
Column Widths. Sets the width of columns in a combo box or list box.
Control Source. Sets the field to which a control is bound.
ControlTip Text. Specifies the text displayed in a control’s control tip.
Decimal Places. Sets the number of decimal places (for a numeric field).
Default Value. Sets default value for the field.
Enabled. Specifies whether the control is available for use. The control is unavailable if Enabled is set to No.
Font Name, Font Size, Font Weight, Font Italic, Font Underline. Specify the properties of the font used for text displayed in the control.
Fore Colour. Sets the colour of text displayed in a control.
Format. Sets the format to apply to text displayed in a control.
Height, Width. Set the width or height of a control.
Hyperlink Address, Hyperlink SubAddress. Specify hyperlink address components.
Left, Top. Set the left or top position of a control.
Line Spacing. Sets the spacing between lines of text displayed in a control.
Option Value. Sets the numeric value to save to the option group’s bound field when an option button is selected.
Picture. Sets the image to display on a control.
Row Source. Sets the source of data for rows in a combo box or list box.
Row Source Type. Sets the type of row source.
Source Object. Sets the source document to display in a subform control.
Special Effect. Sets the style for a control (Raised, Sunken, and so on).
Text Align. Sets the alignment (Left, Centre, Right) of text displayed in a control.
Vertical. Controls whether text in a control is displayed vertically.
Visible. Controls whether a control is visible.
-----------------------
Primary key
Primary key
‘One’ table
‘One’ table
Foreign key
‘Many’ table
Primary key
‘One’ table
Linking table
‘One’ table
‘One’ table
Section selector
Build button
Design grid
Field lists
The same AND condition
Ascending sort
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- free research databases for students
- databases for peer reviewed articles
- educational databases for students
- understanding databases for beginners
- databases for dummies pdf
- free databases for students
- web of science databases journals
- free databases for college students
- using databases with python
- free donor databases for nonprofits
- scholarly databases for nursing
- best databases for nursing research