Microsoft Access

SECTION ONE

 Planning an Access Database

Of all the Office applications, Microsoft Access is by far the most demanding and conceptually challenging.  Anyone who’s ever composed an interoffice memo or put together a budget can relate to Word and Excel instantly; an Access database, on the other hand, is made up of many individual objects, each of which must be built individually.  With a wizard’s help, it’s possible to put together a simple database application in a relatively short time--to handle everything from tracking the contents of a wine cellar to managing inventory.  Access applications can also scale up to enormous sizes, serving the information needs of large organizations and acting as a front end to data stored on mainframes and other network database servers.

Using an Access database, you can store and manage large quantities of data for a wide variety of business and personal activities.  If you’ve previously used flat-file database managers to perform simple list management tasks, you’ll discover that Access is for more powerful, with the capability to maintain and link multiple tables and create applications using a robust programming language.  Before you begin laying out the structure of a database, it helps to understand the components that make up Access.

• The database engine is the (generally invisible) software that actually stores, indexes, and retrieves data.  When you create a standalone database, Access uses its own engine to manage data.  If you choose File, New and select one of the Project options, you can use the Microsoft Data Engine (MSDE) instead.  MSDE is compatible with Microsoft’s enterprise-wide database program, SQL Server.
• Database objects provide the interface you use to view, enter, and extract information from a database.  The most common database objects are tables, forms, queries, and reports.
• Access includes a full set of design tools that you use to create objects.  The reports designer, for example, enables you to sort data, group by fields, and add headers and footers to each page as well as the entire report.
• Finally, Access includes a rich set of programming tools you can use to automate routine tasks.  Confusingly, the Access database window includes an object type called macros; these automation tools are completely different from the Visual Basic for Applications code you can add to most database objects.

The basic file type in Access is a database, which uses the extension *.mdb.  Curiously, an Access database file doesn’t have to contain any data at all; if you use Access strictly as a front end to retrieve information from a SQL Server database, the *.mdb file need contain only form, report and query objects.  If you design your database to hold data, you must create one or more table object to hold the data, and those objects as well as the data itself are stored in the database file.

Special Note:

Access wizards make it relatively easy to create and work with database objects, but don’t underestimate the challenges of build a robust, easy-to-use database application.  Access applications have a way of creeping up in complexity, and it’s all too easy to get in over your head.  Defining database table relationships and their associated referential integrity rules, adhering to standard database normalization rules, adding error-handling routines, and setting up security for multiple users are just a few of the significant challenges you’ll face as you build an application.  Don’t even consider using Access to handle mission-critical data, such as the accounting system for your business, unless you’re a skilled programmer.  And even then, chances are good that a commercial program will handle the job more smoothly and safely than anything you can build on you own.

A Database Design Checklist

The biggest single reason an Access database application fails to materialize into a functional smooth working applications is the lack to planning, pre-development analyze and table layout.  Before you begin creating database object, think carefully about who will use the database and what kind of reports you will need to produce from the data within it.  Use the following checklist to define your design:

• Storing data:
• How much data do you plan to enter?
• Home much training and expertise will be required of those who are responsible for that data entry?
• Will you need to create systems to ensure accurate and reliable data entry?
• Retrieving data:
• What relationships do you need to establish between the different set of data that you produce?
• How can you exploit these relationships to create new combinations of dta from different sources?
• Do you need to subtotal or summarize the data in any way?

• Modifying data:
• Who will be responsible for editing data, and what level of understanding will be required of them?
• What safeguards will you need to create to ensure that changes in your database do not result in duplicate records or inconsistent data?
• If several people are using the database, how do you prevent unauthorized changes to data, while still allowing users to retrieve the information they need?

How are Tables Related?

There are many pitfalls and potential show stoppers you may come in contact with when you develop a database from scratch.  Of these, the three that most often lead to failure are: Lack of planning, data analysis, and table design.  

Lack of planning is often the direct result of how easy it is to open Access, generate a new database, generate a table or two, import or input some records and generate a few queries and a report or two.  Several weeks later after several hundred records have been posted, things start to go wrong.  Suddenly the reports no longer want to work, data you know is in the database tables is not showing up on these reports, duplication of data seems to be running amok, queries that should be returning dataset with a few records suddenly are returning thousands of records.  All the praise you have been receiving for this database has turned to smoke and ashes.  “How come?”, “What happened?”, “This data is garbage”, and “What are you going to do about it?” seem to be the remarks of the day. To fix the problem, you will often find yourself totally redesigning the database from scratch.  You may have some luck in saving all or most of the current data but that is not a given.  Planning, data analysis, and table design/definition can eliminate this. 

When designing a database, you should provide at least one field in each table that may be repeated in at least one of the other tables. This field, called a Primary Key, needs to be unique and once defined and saved will not allow duplicate entries within the database table. The primary key in a primary table is related to secondary table(s) as a Foreign Key within the secondary table.  This relational database management system (RDMS) function allows you to create queries such as shown above to generate ad hoc or formal reports needed for data analysis or data presentation.

In comparison, a manual database, such as a library database, would use a book’s call number where you could create a report listing both the book’s title and author (first table) and the price and publisher (second table). In this case, the call number would be the primary key for the book table and a foreign key for the publisher table.  There are several special rules you must adhere to when defining primary and foreign keys within Access.  For the purpose of this unit, we will not go into defined these keys.

Database Objects

Figure 2 Use the database window to see a list of all database objects, arranged by type

The basic building blocks of an Access database are objects.  Although Access supports many types of object, the most common by fare are tables, queries, forms, and reports.  A database can contain any number of objects.  The database wind, which is visible by default when you open a new database, let you create and edit objects contained in the current database.  As you can see in figure 1, the Access database window consists of two distinct regions: a larger region on the right that displays a list of object in the current database.

Tables

A table is the basic unit for storing a collection of data in an Access database.  A table’s definition consists of a list of fields, each of which stores a discrete piece of information for a single record.  For example, an Employees table might contain the fields EmployeeID, LastName, FirstName, Position, DateHired, and HourlyRate.  Because each record consists of one complete set of fields, a single record in the Employees table contain all available fields for a single employee.  Fields such as HomeAddress, HomePhone, MarrageStatus, NumberDependents, WifeName, and SocialSecurityNumber may also be part of this table or be part of an associated table due to company policy or security reasons.

The arrangement of fields and records is most obvious in Datasheet view, which displays data in rows and columns.  When you open a table in Datasheet view, each record consist of a single row, and each column represents a single field.

Queries

Queries enable you to extract a subset of data from a single table, from a group of related tables, or from other queries, using criteria you define.  By saving a query as a database object, you can run the query at any time, using the current contents of the database.  When you display a query in Datasheet view, it looks exactly like a table; the crucial difference is that each row of the query’s result set can consist of fields drawn from several tables.  A query can also contain calculated fields, which display results based on the contents of other fields.

CAUTION.

Be careful when working with queries.  Queries typically contain live data.  If you change data in a query Datasheet or form, it changes in the underlying table as well without any warning.

Forms

Access forms enable user to enter, view, and edit information, generally one record at a time.  Yu can design forms that closely resemble pages forms such as invoices and time sheets, or you can create forms that are organized for data entry, complete with data-validation rules.  A form window can also include a subform that displays information from a related table.  For example, a form that shows a single record from the Departments table might include a subform that displays all the employees who work in a give department, allowing you to edit information about those employees.

Reports

Reports enable you to present data from one or more tables or queries in a readable style and a professional format, generally for printed output.  A report might include detailed list of specific data, with each row consisting of a single record, or it might provide a statistical summary of a large quantity of information.  A report design can include grouping and sorting options; for example, you might create a weekly sales summary that runs a query, groups the query result set by salesperson, and displays details of each sale in a list beneath each name.

TIP 

Access reports transfer well to other Office applications, when you can use more powerful editing and analysis tools to create good-looking documents and charts.  For example, buttons on the Access toolbar enable you to export a report to Microsoft Word, edit the page, and then print it or save it on a Web server.  You can also sent a report to Excel, perhaps to create a chart or PivotTable report for use in another document.

Data Access Pages

Pages--also known as data access pages--enable you to publish database information on a corporate intranet or (with some difficulty) on the Web.  You can design pages that present data, enable users to edit the contents of a database--including changing data and appending new data to a table--or provide tools for data analysis.  It’s relatively easy to create a data access page, thanks to the Page Wizards; however, editing a data access page and managing its security setting require advanced skills.  Unlike all other database objects, a data access page is stored on disk as a separate HTML file; icons in the database window are shortcuts to the actual file.

Macros and Modules

The final two selections in the database window allow you to automate actions in an Access database:

• Macros enable you to define a sequence of actions in an Access database.  Macros are generally easy to create, even for users who have no programming background.  You select each action by name, fill in the appropriate action arguments, and optionally supply a condition under which the action will be performed.  For example, you can specify that a particular macro is to run every time you open a specific for, or you can attach the macro to a command button in a form.  To run a macron, select the object in the Macros list and click the Run button on the Access toolbar.
• Modules are collections of Visual Basic procedures and declarations, designed to perform specific tasks in the context of your database.  Unlike Word, Excel, and PowerPoint, Access does not have a macro recorder that can generate VBA code automatically.

Choosing the Right File Format-

Access 2003 and Access 2002 share a common file format, making it easy to share database files between the two versions.  If you expect that you will want to use your database with earlier version of Access, you need to pay special attention to your file format options.

To guarantee backward compatibility with older version, any new databases you create using Access w003 or Access 2003 use the older Access 2000 format.  This option ensures that your database will work properly in a large organization where some users are still running Access 2000.  Using this format allows all Access 2000/2002/2003 users (with correct permissions) to modify the design and content of the database.

The Access 2002-2003 format include a handful of new functions, methods, and properties, and its method for sorting objects within the database file is more efficient.  If you’re certain you will use a database only on systems running Access 2002 or later, you can convert it to the new format by choosing Tools, Database Utilities, Convert Database, To Access 2002-2003 Format. (If you choose this menu option with no database open, you will be prompted to supply the name of the database to convert.)

Working with Database Objects

As noted earlier all the objects in a given database are available for browsing in the database window.  You can visit this location and create a new object with a single click.

The default action for database object is Open--if you double-click a query icon, Access executes that query and returns its result set in Datasheet view.  Likewise, double-clinging a form or report icon opens the select object using the current contents of the database.  The effect is the same if you select an object and click the Open button at the top of the database window. (If you select a Report object, the Open button is replaced by the Review button.)

To view and edit the definition and structure of an object, select any object and click the Design button.  In Design view, you can modify the appearance of an object (the fonts and colors on a form), change the table or query from which it derives data, or adjust any of hundreds of other properties for the selected object.

Creating new Objects

To create a new table, query, form, or report, use either of the following techniques:

• Click the New button on the toolbar at the top of the database window, and select one of the options listed in the resulting dialog box.  Figure 2 show the New Form window, listing a variety of wizards and Auto Form tools for create new forms.  In addition, the first entry in the list enable you to go directly to the Design view for a form, and begin your work on a new form object from scratch. 

• Double-click any of the template icons show at the beginning of any object list.  These icons (which resemble a scratchpad with a key icon on it) offer multiple ways to create a new object, including various wizards and the option to create an object from scratch in Design view.  As figure 3 demonstrates, the canned choice available form the New Table Wizard can be a useful starting point for business and personal databases.

When used judiciously, wizards can be enormously helpful in the initial design of any database object.  Some wizards are valuable mainly as introductory tools for newcomers to Access, whereas others are consistently useful even for experienced database developers.  The Table and Query Wizards are acceptable for generic databases, but in both cases, Design view represents a far more efficient way to develop individual objects that precisely match specify requirements.  On the other hand, the Form and Report Wizards almost always provide an excellent starting point for creating new forms and reports.  When working with forms and report in Design view, it’s usually easier to move or modify existing object than it is to add and edit new controls.

TIP.

When you use a wizard to create the initial version of an Access object, always expect to switch over to Design view to fine-tune the result.  A wizard seldom produces the exact object that you need; in particular, the Design views for Forms and Reports offer direct Access to important design elements such as controls, properties, fields, sections, and groups--and enable you to make detailed changes in the appearance and behavior of database objects.

Managing Database Objects

To see a concise list of available options for existing object, select any object in the database window and use the right-click shortcut menus.  Many of these options are also available from buttons on the Access toolbar, or from Access menus.  You can open any object to view its content or its design; you can also rename or delete an object, cut or copy it to the Windows Clipboard, or add it to a group of favorite shortcuts for quick access.

TIPs

All version of Access since Access 2000 are “smart” about handling changes to the names of objects.  When you rename a field in a table, Access automatically changes any reference to that field in queries, forms, report, and other objects.  If you change the name of a field, you shouldn’t need to edit any other objects.  However, any captions that reference those fields on a existing form or report are unchanged.

Modifying Object Properties

Confusingly, every database object has two set of properties. If you right-click the object’s icon in the database window and choose Properties, you see a bare-bones dialog box that list the object’s General properties.  These include the object’s name, a text description, the data the object was created, and the date it was last modified. Ho-hum By contrast, if you open an object in Design view and click the properties button, you see a complete list of properties that enable you to control the appearance and behavior of that object.  Figure 4 shows the Properties dialog box for a form, with all available setting organized by category on five tabs.   Because the properties dialog box is modeless, you can leave it open as you work with different object.  The contents of the dialog box always match the currently selected object.  Rather that closing and reopening the dialog box as you edit a form, move it off to the side where you can see its contents without hiding fields or controls on the form.  This technique is the most efficient way to adjust the properties of text boxes, subforms, labels, and other part of a form or report.

Using Expressions in Database Objects

When designing database objects, you do not have to limit yourself to data stored in a table.  Extend the power of a database by writing expression to transform the data on the fly.  An expression is a combination of symbols, values, and identifiers (the name of a field, control, or property) that calculates a numeric result, combines text, or produces a logical value.  Some of the operators you will use in expression include everyday arithmetic operators: + (plus), - (minus), * (multiplication), and / (division).  These are the same arithmetic operators you use in Excel.  Other operators used in expression might be less familiar.

Expressions are useful throughout Access in may types of objects.  The following are a few examples:

• In a query, you might include calculated fields, in which each entry is the result of an expression.  The operands in the expression might include other field in the same table or in a related table.  You can use an expression to calculate a due date for an invoice ([SaleDate]+30) or to product a total, such as [Qty]*[UnitPrice].  You supply the expression for the column and Access performs the operation for each record in the resulting Datasheet.
• The design of an individual field in a table might include a validation rule, which specifies a range of acceptable entries in the field itself.  You might create a rule that prohibits users from entering a value in the SaleDate field that is in the future or more than 30 days in the past.  If a given data entry does not meet the condition expressed in the rule, Access rejects the entry.   To create a validation rule, write an expression that will evaluate to True or False for each new entry.  If the result is True, the entry is accepted; if False, it is not.
• A criterion is an expression that you can use to select a target group of records for a particular operation.  Any record that meets the criterion becomes part of the group; a record that does not meet the criterion is excluded form the group.  Again, a criterion expression results in a value of True or False for each record examined.

In these and other examples, you use specify types of operators in expression to produce the appropriate types of values.  The following categories of operators are commonly used:

• The Arithmetic Operators -- In addition to the familiar four (+,-<*,/), these include ^ (exponentiation), \ (integer division), and MOD (the remainder from the division of two integers).  These operators require numeric operands and produce numeric results.
• The Comparison Operators -- < (less than), <= (less than or equal), <> (not equal, > (greater than), >= (greater than or equal), and Between (expressing a numeric range).  These operators produce logical values, indicating whether a comparison is True or False.
• The Logical Operators -- These take logical operands and product logical results.  A logical operator might combine the values of two comparison expressions.  Among these operators, the most commonly used are And (true if both operands are true), Or (true if one or both operands are true), and Not (produces the opposite value of an operand).  Other logical operators include Eqv (true if both operands have the same value), Imp (true if the first operand is true and the second is false), and Xor (true if the operands have different values.

WARNING:  When using logical operators within a query, form, or report it is very important for you to have a good idea as to what the resulting dataset should be.  Assuming you have used the correct operand can often result in bogus data.

• A String Operator -- The & symbol represent concatenation, the process of combining two text values. Example: [FirstName] &‘ ‘&[LastName]  in a report.

Because expression are so central to the design of database object, Access provide a special tool call the Expression Builder to help you write expressions quickly and accurately.  As you see in Figure 5, the Expression builder contains button representing operands, along with other categories of identifiers that might become part of an expression.

You can generally open the Expression builder by clicking the Build button (labeled …) next to the box where the expression is entered.  Or, right-click inside the box and choose Build from the shortcut menu.

As you can see, the Expression builder is a very complex wizard and will require some practice to become comfortable with its use.  The easiest way to start using the expression builder is to generate a simple expression you already know using the various buttons, drop downs and options.  In this way, you reinforce your use of the Expression builder and start to understand the mechanics of using it. 

Using Wizards to Create Databases and Objects

As noted earlier, Access offers a collection of wizards to help you create individual object in a database that you are developing.  In addition, Access includes another set of wizards that create complete, special-purpose databases, devoted to specific business procedures.  These wizards create fully developed database applications designed to handle common business tasks, such as expense accounting, time and billing management, and order entry.

The wizard-generated databases create a full set of tables, queries, forms, and reports, as well as a sophisticated database application.  The centerpiece of each of these canned databases is a form called a switchboard, which offers one-click options for viewing tables, opening data-entry forms, running queries, and producing reports.  The menus and forms are tied together with VBA code, which you can easily inspect and borrow for other applications.

To build a new database by using any of the database wizards, follow these steps:

1. Click the New button on the Access toolbar, or choose File, New. In the New File task pane, click the On My Computer link under the Templates heading.
2. Click the Databases tab and select any of the database wizard’s icons.  Don’t expect any help from the preview window on the right--all it shows is an abstract illustration intended to represent the functions of the corresponding database application.
3. Click OK to start the wizard.  In the File New Database window, enter a name for the database file the wizard will generate, choose a new location if necessary, and click the Create button.
4. Follow each step of the wizard to read a brief description of the database application, select fields for specific tables, and choose formatting options, such as background images and presentation styles.
5. After you have completed all the steps, click Finish.  The wizard generates the database and all its object and opens the main switchboard, such as the one shown in figure 6.

Figure 7 shows you the different canned databases you can select from.  Sometimes, it is easier to develop a database application by starting with a canned database and then modifying it to fit your current needs. 

By click on the Templates on Office Online button you can easily find list of other templates that may fit your current needs.

 Exporting and Importing Data

Sooner or later--probably sooner-- you will want to transfer information stored in an Access database to some other software environment, or move data originally created in another program into Access.  In some cases, you’ll want to move entire tables between database programs for use in different applications.  You might want to copy a table of supplier names and addresses from Access so that another database developer can incorporate that data into an application created with SQL Server or Oracle.  Or, if you are building an Access database to replace an application create in another program such as Excel.  Often you will need to import this data at least twice--once when you begin designing the database, so you can test forms, reports, and queries using real data, and a second time when your are ready to switch from the old system to the new one.

Even when you are extremely careful, exporting and importing information between database formats runs a serious risk of creating duplicate data sets.  If you keep information about customers and product in Access and in a SQL Server database, whoever is responsible for data entry has to enter changes in two places, and it’s almost certain that some records will be out of sync or contain errors and inconsistencies.  When you must use the same data in two different database programs, you should choose one program to store the data, and then create a link to that data from the other database program so that you can add or edit records or run queries.  Because Access can link to data stored in a variety of formats--including dBase, SQL Server, and Paradox--you will most often want to store shared data in another program and create links to it from Access.

In other cases, your need for Access data is strictly temporary.  If you’ve created a report or query in Access, you can transfer the data to Word to incorporate it into a larger report, or sent it to Excel, where you can easily analyze it with the help of PivotTables and charts.

The simplest and efficient tools in this category are known as OfficeLinks; these shortcuts are designed to send Access data directly to another Office application.  To use one of the three OfficeLinks options, first select a target object in the current database window, or open a form, report, or query’ then choose Tools, Office Links, or click the OfficeLinks drop-down list on the Access toolbar. This list includes the following three links:

• Merge It with MS Word -- This option sends a table of data to a new or existing mail merge document in Word.  When the transfer is complete, you can insert fields from the Access table as merge fields in the Word document.
• Publish It with MS Word -- This option creates a text field on disk--in RTF format from a selected Access object, and immediately opens the file in Microsoft Word.  This file might become the starting point for a larger business document or report.
• Analyze It with MS Excel -- This option creates a worksheet file on disk (in Excel workbook format) from a selected Access object, and immediately opens the file in Excel.  In the worksheet environment, you can perform mathematical, statistical, and other analytical operations that might not be possible in Access.  You can also make use of Excel’s versatile charting capabilities.  If the report you start with include grouping, the resulting Excel worksheet will include subtotals as well.

You can also create a Word or Excel file by selecting an object in the database window and the choosing File, Export.  In the resulting Export dialog box, you can choose a specific format (including formats for pervious versions of Office), and you can supply a nondefault name for the file that will be created.  You might prefer this approach to the OfficeLink options if you need more control over the formation or the name of the resulting file.

Importing external Text Files

In the initial development of a Access database, the ability to import current data from another source can shortcut hours of data entry and database development time.  However, external data is seldom in a acceptable database table format and will require some data manipulation.  Data can exist in many different formats and repositories.  Incorporating external data into a database is an essential time saving task.  There is no need to rekey existing electronically stored data, you can just import it.  Of course, Access can’t possibly read all of the different types of data formats that exist, but you can save data as a delimited text files in most applications.  Delimited indicates that each section of data is separated or defined by some sort of special character.  For example, the comma, the quote, and the space are very common delimiters.  The data can then be interpreted from this file and imported into Access.  This same method can be used to extract data from Access into other databases systems.  Maybe your are going to import the results of a Web-based survey and then analyze the data to summarize the results.

To import a delimit text file

1. On the File menu, point to Get External Data, and then click Import.
2. In the Import dialog box, in the Files of type drop-down list, click Text Files, and then navigate to the file you want to import.
3. Double-click the file, or Click the file, and click Import.
4. The Import Text Wizard opens, Click Next to accept a delimited text file.
5. Select the First Row Contains Field Names check box.  The data is shown in the main pane of this page of the wizard.  Click Next.
6. Click Next to indicate that you want to store your data in a new table.
7. Click Next to accept the field settings
8. Click No primary key, and then click Next.
9. Change the name of the table in you want, and then click Finish.
10. Click OK to respond to the information box that notifies you that the import completed successfully.

You not have a new table listed in the database Container that is named by your selection in step 9.

Figure 8 shows you some of the current File types that can be imported into Access in addition to a delimited text file. 

 

Viewing Your Data

There are several ways to view your data. When you first create a table, you can use the Design View to set up the column or field names, data type and description.

 After you have completed the process of defining the various fields within the Access database, you are ready to enter records.  There are three different ways to generate a Access database table and the above example is the one used most often. 

When you want to enter records or view all of the data in your file (table), you may use the Datasheet View. Once again, this is one of several ways you may choose to view your table data.  It is the one most often used because it closely resembles the normal Excel spreadsheet view of data.  When you are creating a form, query, or report, you will also have access to that particular view. These view options are accessed from either the View Button on the Toolbar

Or by double-clicking on the desired table icon within the Assess database form.

Creating a New Table

By default, when you open a new blank database you will Create your table in Table Design View. This is where the Field Names, Data Types, Description, and Field Properties are defined.

1. Select the Tables Tab (selected by default)

2. Click on the New button

3. Select Design View option

Field Name     The field name is the heading for each field. It can be up to 64 characters long and can include spaces and some special characters; however, it is wise to keep the field name short, especially when you have numerous fields in your table. Spaces are often used to help define a fieldname.  You should use the “underscore (_)” character for this because a space in a field name will cause problems when you are using the field name in other functions.  Although it is possible to use some special characters as part of a field name, this is not a good practice.  The use of upper, lower, or mixed case is optional.  It is strongly suggested that what ever case you use be used throughout the various database table(s) you will be defining.  There are several documented naming standards utilized by professional database designers and organizations.

The following URL will provide you with the naming standards for the National Park Service:  http://www.nature.nps.gov/im/units/swan/Documents/Data_Management/im_naming_guide.pdf

Data Type      The Data Type option is used to specify the type of data stored in a table field. Each field can store data consisting of only a single data type.

Setting	Type
Text	Text or combination of text and numbers that don’t require calculations. Maximum size is 255.
Memo	Lengthy text or combination of text and numbers. Maximum size is 65,535.
Number	Various forms of numerical data used in mathematical calculations.
Date/Time	Date and time values in various formats.
Currency	Currency values involving data with 1 to 4 decimal places in various formats.
AutoNumber	A unique sequential number assigned to each record. The number is automatically entered for each new record. Useful as a Primary Key.  However, it may not prevent the actual duplication of data which is the primary function of the primary key.
Yes/No	A check box with Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off. Use the spacebar to place or remove the checkmark for this cell.
OLE Object	A linkage to an object in another file or table.
Hyperlink	Data stored as text and used as a hyperlink address. A path to a file on your hard drive, a LAN server, or a URL address.
Lookup Wizard	Creates a field that allows you to choose a value from another table or from a list of values by using a list box or combo box. Clicking this option starts the Lookup Wizard, which creates a lookup field.

In addition each field name has various attributes (properties) associated with it to help define the specific field:

Saving a Table Design

After a database object has been designed, it must be named and saved.

1. Click on the File Menu option.

2. Click on Save or Save As.

3. You will be asked if you want to set a primary key (if you have not done so already).

     NOTE: You do not have to set a primary key. You can always set one later.

4. Select the drive (A: for disk) you want.

5. Name the file. You can use up to 64 characters and spaces but keep it short when possible.

     NOTE: Do not use a period or type an extension to the filename.

Entering Data into Your Table

1. Switch to Database View.

2. Enter the data into the appropriate cells. Notice this will look and feel the same as when you enter data in the cells within an Excel spreadsheet.  However, there is a very subtle difference.  In Excel input or changes to the spreadsheet are not saved until you specifically do a ‘save’ or ‘save as’ or exit the spreadsheet.  In Access as soon as you leave the row you are working on, the data is saved to the database table.  You can however ‘undo’ any changes or input to the current row you are working on.  This subtle difference can be a blessing and it can be a monstrous ‘OOPS’. 

  

3. Use the Tab key, the “mouse” or the keyboard “arrows” keys to move to the next cell; use Shift +Tab or the “mouse” to move back a cell.

4. It is not necessary to resave your table after data entry or data editing because Access does this for you.  However, if you are doing a lot of data editing to current data, it may be desirable to make a backup copy of the current table prior to the actual data editing to protect yourself against the ‘OOPS’ principle.  To do this:

1.      Select the table you will be editing

2.      Click on EDIT then Click Copy

3.      Click on EDIT then Click Paste.  You will be prompted for a table name.  Enter any name that will allow you to remember that this is a copy table.

4.      If you are successful with your data editing and you no longer need the copy table, you can click on the Copy Table and do an EDIT then delete to remove this table from the database.  You can also click on the table and then click the delete key to do the same function.

Printing a Table

You can print your data either in Datasheet or Form View.

1. Select either Datasheet or Form View
2. Click on File Menu
3. Click on Print Option
4. If you want to set options for paper, graphics, orientation, etc., click on the Properties button
5. You can also print a specific range of data and select how many copies you want
6. Click OK when you are ready to print

Closing a Database

1. Click on File, select Exit  OR

2. Click on the X in the upper right hand corner

SECTION II

Enhancing a Table

Using the Format Menu, you can do the following:

1. Change the Font size and type

2. Change the appearance of the cell (gridlines, background, effect) and the text within the cell (color)

3. Change Row Height (can also click and drag row height)

4. Change Column Width (can also click and drag column width)

5. Rename a Column

6. Hide or Unhide Columns

7. Freeze or Unfreeze Columns

Creating a Form from an Existing Table

The New Object Button can be used to create a new form in various formats or to generate a special ChatWizard or PivotTable your existing table. You also have the option of using the create form in design view icon or the create form using the form wizard icon located on in the table window.

The AutoForm option automatically creates a new form using the fields and data of the selected table.  In this example the AutoForm: Columnar format has been selected

Form Design View and Other Form Options

With a form open in view mode, you can click on the form header to open a form options popup menu.  From this menu, you have several options for modifying the current form.

 

 

 

 

 

 

Adding, Deleting, Moving, and Modifying Fields

Once you are in the Design mode you have several options to add, delete or modify the appearance of the fields within the form.  Each field will have an attached label which be default will be the fieldname you choose when designing or importing you table data.  This label can be detached, modified, or renamed as desired.  The actual field can also be modified using the various formatting options from the format toolbar.  It can also be moved, stretched or shrunk via the use of the mouse.  Click on the field and then select one of the field anchors. Use the Toolbox popup menu to add new objects to your form

Using Shortcut Menus

Commonly used commands for selected objects, controls or data may be found on the Shortcut Menu that is displayed when you right-click on the mouse. This is a quicker way to access menu options. All shortcut options are found within the Menu options.

Hiding/Unhiding Fields

You also have the option to Hide/Unhide fields within your form.  However, this is not recommended.  If you no longer need a field, it is better to remove if from your form.  If you determine later you do need this field, you can add it back via the toolbox popup menu.

Edit, Add, and/or Delete Records (can be done in either Datasheet or Form View)

Edit a Record  You can add or remove text by clicking on the location and inserting the new text or by pressing the delete or backspace key to remove text. Pressing the Esc key will cancel any changes made in a field and return it to the original data.  Remember when you move to a new record, any changes made to the previous record will automatically be saved to the database.

Add a Record:   Records can be added but only at the end of the existing records. Records can be rearranged later by using the Sorting option.  In most cases records will automatically be rearranged when you close the form by the primary key of the table.

Figure 28

 

Delete a Record:  Right mouse click within the black tab area of the record you want to cut or copy. This selects the entire record. Click on the desire option.  In a will designed database records from a master table may not be cut [deleted] if there are child records associated with the master records in other tables.  Example, you can not delete a customer record from the customer table if there are invoice records associated to the customer record you are trying to cut.  Access will popup a error dialog box to inform you of this and not allow for the deletion of this master record.

Find and Replace Records: You may need to find a specific record for editing or informational purposes. You can find records by searching the entire form or by selecting a specific field for the search. After opening the form, Form View, click on the Edit Menu and select the Find or Find and Replace option. After completing the Find popup menu options, click on the Find Next button.  Access will find the first record within the database table that meets the Find What option.  At this point, you can edit the record and modify it as needed or by clicking on the Find Next button again move to the next occurrence.

SECTION III

Working with date in the Datasheet view of a table:

Options related to the Datasheet

By clicking the right mouse button within the table header, you will open a popup menu of various options you can apply to the complete table.  Three options are often used from this popup menu:

Table Design:  In this mode, you can:

·        change Field names

·        Add, remove, or modify table field properties

·        Generate Lookup a table with values for a specific field

·        Change the Data Type and/or its value

REMEMBER:  Making changes to the Primary Key field(s) may or may not be allowed once table relationships and data integrity rules have been applied.  Access will notify you if you are affecting table relationships or data integrity rules.

Figure 33

Cut [Delete] a specific record(s):

By holding down the shift key and dragging the mouse through a block of records you can select multiple records for cutting (deleting) from a table. 

By holding down the ctrl key and clicking on individual records you can select a set of records for cutting (deleting) from a table. 

Make modifications to the layout and format of the Datasheet

WARNING:  If you select a record for cutting that has associated child records in another table, you will not be allowed to delete this primary record unless special data integrity rules have been turned on.  [Cascading delete and/or Cascading update]

Datasheet

Selecting the Datasheet option will allow you to Sorting allows you to rearrange the information so that you can look at it in a different way.   Remember these datasheet changes will apply to the overall datasheet.  Special Note: 

The field to the far right of this table with the “+” sign tells the user that there is a special popup datasheet associated with this datasheet.  It will usually be the associated child records from another table within the database.

Changes to a Specific Column within the Datasheet

Right click on the column header to open the column popup menu options.

The following options are often used from this method of working with data in the datasheet view:

Sort Ascending or Sort Descending:

By default tables are usually sorted in ascending order based on the primary key field(s).  However, by clicking on the Sort Descending option, the table will be sorted in descending order of the selected column.

Hiding Column(s)  Notice that the Company Name field is no longer visible within the current datasheet.  By selecting the column(s) to hide and clicking the Hide Column command, the selected columns will be hidden from the datasheet view.

 

Freezing Column(s):

When working with a large table with a lot of different columns, the use of the Freeze Column options can be a very effective method for data entry or data editing. 

Notice:  You can NOT freeze disjointed columns.  They must be adjacent to eachother.

Figure 41

Column Width

There are two methods of changing the column width.  The first and most often used method is to click on the column header.  Move the mouse to the far right edge and drag the column to its new desired size. 

The second method is much more accurate because you will be defining the new column width from a column width popup menu when you click on the Column Width option

Notice this popup will allow you to modify the actual width (user inputted), select the default standard width, or do a Best Fit. 

Find Option:  The find option is not any different then selecting using the find option from the Edit menu drop down.  It is not specific to a column of data.  It is just another way to find data within the table.

Delete Option:  When you select the Delete Option, you will be presented this popup to verify that you really want to do this.  If you select yes, you will be modifying the database table and this process may affect child tables.  It there are data integrity rules in effect for this column, you may or may not be allowed to delete the column subject to how the cascading delete and/or cascading update rules have been applied.

WARNING:   Before performing any table field deletes it is a normal Best Practice rule to backup the database. 

Rename a Column: 

When you select the Rename option the column header will be highlighted.  Type in the new Column name and click the enter key.  The Column will be renamed in the underlying table.  If this column is used as a foreign key in another table, the foreign key table name will NOT be changed.

NOTICE: The key to relationships between tables is not the name of a field but the field type. 

SECTION IV

Query Introduction

Queries enable you to extract a subset of data from a single table, from a group of related tables, or from other queries, using criteria you define.  By saving a query as a database object, you can run the query at any time, using in the current contents of the database.  When you display when you display a query in Datasheet view, it looks exactly like a table; the critical difference is that each row of the queries results can consist of fields drawn from several tables.  Queries can also contain calculated fields, which display results based on the contents of other fields.

CAUTION

Be careful when working with queries.  Queries typically contain live data.  If you change data in a query data sheet or form, it changes in the underlying table(s) as well without any warning

Using Queries to Extract Data from a Database

Queries are database objects that enable you to extract data from a database to use in another way--as the source of data a used in a printed report or to produce a list of items for use in a look up control on a data entry form.  A query can be based on a single table or on multiple related tables.  In addition to field drawn directly from tables, a query can also contained calculated fields that transform data--adding sales tax to an invoice amount or performing statistical analysis(totals, averages, and the like) on group on groups of records drawn from multiple tables.

Like other office wizards, the access query wizards are efficient at guiding you smoothly through the steps of a complex process, providing detailed explanations of the chores you need to make, and enabling you to view graphic representations of the results.

Although design view is a more versatile environment in which to create queries, it’s often easier to begin by using a wizard to create a basic query.  After you finish with the wizard, you can then open the query in design view to modify the results.

To view a list of available quarry wizards, press at 11 to open the database window, and then choose Insert, Query.  The New Query dialog box lists four query wizards.  Double-click any of the options to launch a wizard.

TIP.

In their Air Force to simplify task, query wizards sometimes unnecessarily restrict your choices.  In a Crosstab query Wizard, for example, you must base your new query on a single existing table or query.  If you want to use more than one table, you must first create a query that contains all of the target fields.  By contrast, if you create a Crosstab Query in designing view.  You can add fields from two or more related tables

Choosing the Right Query Type

Access enables you to create several types of queries.  The most common is a select query, which extracts information from one or more tables. You can also create Crosstab quarries, which group and summarize information in row-and-column formats such as an Excel PivotTable.  Some of the most powerful (and potentially dangerous) things you can do with access involved action queries, which actually change the data in the underlying table based upon criteria you defined in the query.

Like queries, filters enable you to work with a subset of records in a database.  Filters offer a quick way to temporarily limit the display of records in Datasheet or Form views.  You can create a filter by entering data in a form or by making a selective in datasheet view.  Even though the display of data is filtered, it still represents live data, not a separate copy as in a report.  If you enter changes in a Datasheet view based on a query, Access changes the data in the underlying table.

Tips

Filters represent an excellent way to create a query without diving into the sometimes confusing Query Design view.  For instance, you can open a table in Datasheet view and select a fragment of data in a single field (the word nylon, for instance).  Create a filter based on the selection, and then switch to Advanced Filter/Sort view to save the filter as a query (Products That Contain the Word Nylon) that you can reuse anytime.

  To create a new query, choose Insert, Query, or choose Queries in the database window and then click the New Button.  The New Query dialog box lets you start from scratch in Design view or use a wizard to build one of several specify types of queries.

SELECT QUERIES

When you use design view to create a new query from scratch, access creates a select query a default.  As the name implies, the purpose of select query is to gather data from want or more tables, and to present it in a format that you save as part of the query itself. Select queries can include any of the following elements:

• Fields drawn from want or more tables or queries.  You can base a query on another query, a comment to make when you want to create a summary view of data gathered from multiple tables.  Access uses declined relationships to match records from different sources and a find relevant connections between the data.  You can also define a new relationship between tables and/or queries and define them as part of the query.
• Calculated fields, which display the results of expressions, using fields from want or more source tables.
• Totals, which performs the statistical operations, such as some an average, on fields from a source table.
• Selection criteria, which the buying the specific set of records the query will return.  For example, in an invoice is table, you might define criteria for the InvoiceDate field to return only invoice is prepared in the past 30 days.
• Sorting instructions, which arrange the query results in numerical, alphabetical, or Chronicle order by want or more columns.
• Hidden fields, which are included for the purpose of defining criteria or sorting instructions, but are not actually shown in the query’s results.

When you save the query, you save the instructions for achieving and display and records from the database, not the records themselves.  As a result, running and saved quarry always display is the current dataset.

Notice the last column, which contains a calculated field that multiplies the retail price by the current quantity on hand for each product.  The label in front of the formula in the third column defines the name of the calculated field.  Use a colon to separate the label from the formula used to calculate the field results. 

Figure 47

In Design view, a query includes two panes:  The top pane contains field list for each table and query used as a data source; this pane also show relationships between the data sources.  The lower pane contains a grid with one column for each field that makes up the query.  When you design a query, you can drag any field reference directly from the lists in the upper pane, or you can choose from drop-down list that appear when you activate a given column in the grid.  (Double-click a field name to quickly add it to the grid.)  The You can enter calculated columns manual or with the help of the Expression Builder.

• The first step in using the expression builder is to click on an empty column in the lower pane (grid).
• Then right click the mouse to open up the option menu as shown.
• Select the Build option to open the Expression Builder wizard.

In the rolls below each field name, you can see specifications that explicitly determine the content of the query:

• The Table row shows the source of each field.  This row is visible by default.
• The Total row lets you specify operations to be performed on that field -- Sum, Average, and so on.  This row is normally hidden.  The default selection is Group By, which displays all values in the selected field without performing a calculation.
• The Sort row specifies whether a particular column will be used for sorting, and is so, whether the sort is in ascending or descending order.  If you specify a sort order in multiple columns, Access sorts by each column, going in order from left to right.
• The Show row contains a check for each field that will be displayed as part of the query’s result.  Clear this check box when you want to use a field for sorting or filtering but you don’t want it to appear in Datasheet view.
• The Criteria rows contain one or more criterion expressions for determining which records will be included in the query.  Example:  today() - 30  would show only records for the past thirty days.  Today() is a built-in function.

 

The Expression Builder dialog box [wizard] is somewhat complex and will require some experimenting to learn how to use it effective. 

Note:  Simple rules for building calculated fields:  Function Format - Sum((«expr»)),Avg («expr») Field Names - [UnitPrice], [UnitsOnHand] Arithmetic  Operators - +,-,*,/  Comparison Operators - <=,+,>=,> Logical Operators and, or, like not like String Operators & concatenation system Put it all together for a simple calculated field: Inventory: Sum([UnitsInStock]*[UnitPrice])

CROSSTAB QUERIES

Another kind of query, known as a Crosstab, transforms record-oriented data into a summary view that resembles an Excel PivotTable.

Figure 49

Step One:  Select Crosstab query wizard from the New Query dialog box and then select the table, row headings, column headings and function.  The last step will be to provide a name for this query when you click on the next button.  Access will provide a default name you can accept or modify as desired.  In the Design window of a Crosstab query, select fields for row heading, column headings and values.  The Crosstab Query Wizard fills in these values automatically.  However, creating a Crosstab query from scratch in the Design window is fairly straightforward process, but it’s much easier when you use the Crosstab Query Wizard.

PARAMETER QUERIES

Normally, saving a query includes all the criteria you’ve defined for that query.  If you want to see all sales results by product for all vendors, it’s easy to save a query that extracts those results from the current contents of the database each time you run the query.  But what do you do when you want to specify slightly different criteria every time you run the query?  For instance, what if you want to enter a specific vendor number or a maximum price when you run a query?  For that task, you need a parameter query.

 Each time you open a parameter query, Access displays a dialog box asking you to enter a piece of data to be used in the selection criteria for the query.  You define the input prompt as part of the query’s definition. To create a parameter query, open the query in Design view and click in the Criteria box for the field in which you want to add selections criteria.  The expression should include the text you want to display as the input prompt, enclosed in square brackets where you would normally enter a constant value.  For example the query shown to the right prompts you to enter the minimum retail price you want to use as the selections criterion in a select query.

The value the user enters into the prompt becomes the parameter in the expression, which in this examples specifies a selection criterion for the query of $1.00 dollar. 

The simplest parameter queries include a single value, but you can also use wildcards or multiple parameters as part of a query.  The following examples should give you some ideas:

• To create a input dialog box that prompts the user to enter the beginning of a search string, use an asterisk with the parameter.  For instance, entering like [Enter beginning of product name & “*” will search for all records in which the specified field begins with the value of user inputs.
• To search for a string anywhere in a given field, use two asterisks:  Like “*”&[Enter any text that appears in the product name]&”*” will do the trick.
• To define a beginning and ending range of numbers or dates, use two parameters in a single expression, such as: Between [Enter beginning date] And [Enter ending date].  When you run a query with multiple parameters, Access displays an input dialog box for each one. 

ACTION QUERIES

An action query potentially changes the data in an existing table, or creates a new table.  Access enables you to create four kinds of actions queries: 

• An update query replaces data in existing records.  In the designing of an update query, you write selection criteria to identify the target records, and you prove an expression that generates the replacement data.  Use an update query to change a group of records at once--when an area code changes, for example, or to make an across-the-board price increase.  The example shown shows the properties of an update query that include a parameter:  It adds 5% to the amount in the UnitPrice field for all records that match the supplier code you enter.
• A make-table query creates a new table object from the result of the query itself.  For instance, you might build a query that produces a list of all customers who have not ordered product from you in the past year and copy those records into an Inactive Customers table.  This type of query does not affect the underlying source data.  You can choose a table from the current database or from another database file.  If you enter the name of a table that does not currently exist, access creates it for you.
• An append query adds new records to an existing table from a source query.  This type of query is most commonly used when importing data from an external source.  Append queries enable you to take some or all of the imported information and move it into an existing set of tables in the correct format.  When you choose Query, Append Query, Access prompts you to enter a table name by using a dialog box that is identical to the one for a make-table query.
• A delete query removes records that match specified selection criteria for an existing table.  You might use a delete query to prune outdated records from a database.  When you create a delete query, Access adds a delete row to the query’s design grid.

 

        Tips.

Want to archive information from a database table?  Run a make-table query to copy records that meet specific criteria, such as customers who have not placed an order in more than a year.  Then, aver running that query, use the same selection criteria as part of a delete query to remove the same records from the original table.

 

 CAUTION.

Running an action query can absolutely and irrevocably scramble your data, and in most cases the effect of an action query cannot be undone.  If you inadvertently run an action query that doesn’t behave as you expected, you might find it difficult or impossible to restore your original data.  For this reason, you should proceed with great care when designing and running an action query.  At a minimum, make a copy of the original table under another name, so that you can restore the data if your action query has unintended consequences.

Following are two fail-safe mechanisms that can prevent a data disaster:

• Before you begin designing an action query, create a copy of the table you intend to modify.  In the database window, select the table icon, press Ctrl+C to copy it to the Clipboard, and then immediately press Ctrl+V .  In the Paste table as dialog box, enter a name such as Copy of Tablename, choose the structure and data option, and click OK to create the copy.  If the action query does not work properly, you can return to the original state by deleting the modified table and renaming the backup copy the same name as the original.
• Before running an action query, test its effects by designing a select query that uses the same selection criteria.  When you open this query in Datasheet view, inspect the results carefully; because these records will ultimately be the same ones that change as a result of the action query.  After inspecting the results, return to Design view and choose Query, followed by the menu choice for the type of query you want to create.  This will not guarantee success but it might point out a logic error.
•  If the selected table has referential integrity rules applied to it, some action queries will not work as a result of the desired action.  For instance, if you are trying to remove old records from the customer table that still have child records in the invoice table, Access may not allow for the deletion of a parent record until the child records have been deleted first.  The exception would be if the cascading delete option was turned on within the parent table referential integrity definition. 

CAUTION

You can save an action query for future use, or you can abandon it after a single use.  If the purpose of the query is to perform a one-time maintenance chore, get rid of the query when you no longer need it.  That way you do not risk accidentally corrupting your database.  The only reason to save a query is if you expect to reuse it as part of ongoing database management-for example, in a monthly database cleanup routine.

DEFINING QUERY PROPERTIES

In addition to the settings discussed so far, which are available in the design grid for a query, you can also adjust a host of settings that apply to the entire query.  Open a query and switch to Design view.  If necessary, click the Properties button to display the Properties dialog box, and then click anywhere in the background area of the query design window. 

• Top Values - Returns a specified number or percentage of records.  This setting is most often used in conjunction with a sort setting; to see the 10 most expensive products.
• Unique Values - Returns a query result in which no duplicate records exist.  Choose Yes if you want to extract a unique set of values from a database.  Access eliminated duplicates from the result set based on records visible in the query’s result, not on the contents of the underlying table or tables.
• Unique Records - Returns a query result after eliminating duplicate records in the data source.  Depending on the fields you choose to display, you might see duplicate values in the query results.
• Column Headings - This property, used only in Crosstab queries, lets you limit the columns to be displayed.  Separate entries with semicolons.  In a data source that contains a Regional Office, field, for example, you might specify East;West;Midwest in this property.  Access ignores all other values when performing the Crosstab query and displays these three columns in the specified order.
• Output All Fields - Specifies that you want the query to return all fields from all tables included in the query, regardless of whither the field name is on the design grid or the Show box is checked.  When you set this property, you need to add fields to the grid only to set Criteria and Sort properties.
• Link Child Fields, Link Master Fields - Used to set the relationship between a main form and a subform or other embedded object.  Normally, Access sets this property automatically based on relationships you define between the tables.

Saving a Query

1. Click on the File menu and select the Save or Save As option.

NOTE: Any new data that is later entered into a table and that meets the criteria will appear in the query.

Using all Fields of a Table for a Query

There are three ways of selecting all fields of a table for a query: 

• The first way -When a selected table appears in the top pane of the Query Design Window, the first field option is the asterisk (*) which represents all fields in the table. If this option is selected, only the table name will appear in the lower pane.
• The second way - Click in the table name header (blue area) and hold the mouse button down.  Now drag the table name to an empty grid column.  Release the mouse button and all fields within the table will be added to the grid.  If there is a field you do not want, click on the area just above the field name [column should turn black] and hit the delete key.
• The third way - Select the Output ALL fields from the query properties menu.  Remember this will select ALL fields for all tables defined in the query. They do not have to be displayed within the query grid.

Moving a Column in a Query

1. Click on the Datasheet View of the Query.

2. Select the column by clicking on the field name (column will be highlighted).

3. Click again on the field name holding the mouse button down.

4. Drag the line to the vertical grid line that will become the new left boundary of the moved column.

5. Release the mouse button to insert column in new location.

Renaming a Query

1. Click on the Query Tab

2. Highlight the name in the Query database window list

3. Click again to activate the edit mode

4. Type the new name

Printing a Query

A query can be printed in the same manner used to print other database files. The printout will include both a header, containing the query name and current date, and a footer containing the page number.

SECTION V

Building Great Forms and Reports

Access provides a design and development environment that gives you extensive control over the appearance and functionality of forms and reports.  The tools you use for either task are remarkably similar.  The differences typically reflect the different design goals of forms and reports, as explained here:

• A form is a formatted database object, generally used to display one record at a time in an onscreen window.  Forms are most commonly used to create convenient the until then, the blanks windows for entering or editing data symbol, in this case, you use a table or query as the data source for the form.
• Access reports typically organize data in a format suited for printing or publishing.  Although you can also using a form to view data onscreen, reports are better suited for this task and often represent the most important in product of a database

When designing a form or report, you start by specifying a data source--one arm one or a one or more tables or queries, or as stated written in SQL--and position controls on a design.  Although you can create a report, using a wizard is often a better starting point.  The wizard produces the best structure, and you then open the form or report in design view to make detailed changes to its content and appearance.

As with other database objects, Access provides several ways to create a form or report.  After selecting Forms or Reports from the database window, click to tilt the New Form or New Report dialog box.  The option shown here let you create a new form or in Fort by opening a blank form in design view or by choosing wizards and AutoForm/AutoReport options that enable you to create a default form or report with a single click.

To open an existing form or report in Design view, selected from the database window and click the Design button.  If the form or report is already opened, click the Design button to switch into Design view.

DESIGN 101:  WORKING WITH CONTROLS

The building blocks of any form or report are object objects called controls, which include text boxes, labels, option buttons, list, command buttons, toggles, and other familiar Windows interface elements.  Controls have their own property settings, as do individual sections of the form or report; by changing the settings of these properties, you can modify the appearance and content of the form or report.  Controls can take any of three forms.

• Some control some controls are directly tied to a field in a table or query.  In a peculiar jargon of Access, these are called bound controls.  When you enter data in a control that is bound to a particular field, Access adds the data to that field;  when you view data by using a form or report, Access checks the Control Source property for each control to see which data is should display.  

Figure 1 shows the Properties dialog box for a text box bound to the CatagoryName field in the underlying table.

Two additional dialog boxes are also shown:

• Product by Category field names
• Form/Report ToolBox controls dialog box.

• Some controls are unbound--that is, not tied to any data source.  For example, a line, box, clipart, picture, or freestanding text label is an unbound control.
• When you enter an expression in the Control Source property box, Access creates a calculated control.  The expression = [SupplierPrice]*2, for example, multiplies the contents of the SupplierPrice field by 2 and displays the result.

When you open a form or report in Design view, you can change the font, font size, color, borders, and other formatting properties of any control.  In Design view, Access lets you position controls in a grid for precise alignment.  You can also group and align controls.

When you work with a form or report in Design view, three interface elements are essential:

• Use the Toolbox to add new controls or change existing controls.  You can customize the Toolbox buttons just as you can any other Office toolbars;  to toggle it on or off while you work, click the Toolbox button or choose View, Toolbox.
• The Field List displays a list of all the fields in the source query or table, which you can use to add new controls.  To show or hide this list in Design view,  click the Field List button or choose View, Field List.
• To adjust the appearance or behavior of a control, section, or the form itself, open the Properties dialog box.  You can keep the Properties dialog box open while you work; as you select different objects, the properties displayed in this dialog box change to reflect the available choices. 

TIP:

Tabs in the Properties dialog box make it easier to find the exact function you are looking for.  All the tabs that affect the appearance of a control, for example, are on the Format tab.  Click the All tab to scroll through a list of all the perperties that apply to the selected object. 

ADDING A NEW CONTROL TO A FORM OR REPORT

If you drag a field name from the Field list onto a form or report, Access automatically creates a text box control bound to that filed.  If you click another Toolbox button first, and then drag a field onto the form, Access launches a wizard that create the control type you selected.  Figure 2, for example, show one step of the Option Group Wizard.  Follow the wizard’s instructions to define the data source and behavior of the control. 

If the Control Wizards button on the Toolbox is selected when you add a new control to the form or report, Access lets you fill in the control’s properties with a wizard

The Toolbox contains buttons for common controls you might want to add to a form or report.  By positioning the mouse pointer over a button in the Toolbox, you can view a ScreenTip that show the name of the control itself.  Some of the more common and useful controls include the following: 

·        Check boxes, which let users enter data in a Yes/No field or an option group.

·        Combo boxes, which let the users enter data from a drop down data list

·        List boxes, which let the users enter data from a data list.

·        Labels, which add descriptive text to a control or a form or report.

NOTE .

Labels are always unbound, and they don’t change as you move from one record to another.  Access automatically adds labels to new fields you place on a form or report; you might also use labels for titles and instructions.

POSITION CONTROL AND LABLES

When you use the Form Wizard to build a form, the default type for all controls is a text box with a label attached to its left.  In some cases, however, you’ll want the labels to appear above the text box, and you might want to change the position, alignment, size, or grouping of controls on the form.  After you learn the secrets of working with Access controls, you’ll find it easy to position controls precisely where you want them.  It does take some practice.

Access display handles -- small black rectangles -- around the outside of a selected control, as shown in Figure 3;  These handles are a visual indication that you’ve selected the control.  The eight small black squares are size handles -- you’ll find one on each corner and one in the middle of each side of the selected control.  When you position the mouse pointer over a size handle, it takes the shape of a double-headed arrow, at which point you can drag the handle in any direction to change the size and shape of the control itself.

Note that both the control and its label are selected.  The “open hand” pointer means that you can move both the control and the label at once.

The large black square at the upper-left corner of a selected control lets you move the object to a new position within the form.  When you drag this handle, the mouse pointer takes the shape of a hand with one finger pointing up; as you move the selected control, you see only its outline, making it easier to position on the form grid.  When you release the mouse button the control itself moves to the location you’ve selected.

For the most part, every control you add to a form or report actually consists of two controls; the bound or unbound control (text box or combo box, for example), and the matching label. If you know the techniques, it’s easy to position these controls correctly.

• Use the large square in the upper-left corner of the control or the label to move either one independently.  This technique is effective if you want to move a label from the left of a text box so that is sits above the control.
• To move both the control and its label at once, point to any border of the control or the label, until the mouse pointer takes the shape of an open hand.  Drag to position the control-label combination in its new location.

TIP .

Do you want to position an object precisely on a form?  If you plan to print out an Access form and use it as an invoice, you might want a graphic to appear in a fixed location at the top of the form.  To add the graphic, choose the Image button from the Toolbox.  Then click and drag to define a region on the form,  in the general location where you want it to appear.  Select the image file from the Open dialog box.  Finally, click the Format tab on the Properties dialog box for the image you embedded, and set the Left, Top, Width, and Height properties to define the exact size and location on the page.

Special Note:  The label associated with a control on a form or report will always be the FieldName from the underlying table or query unless you have defined a Caption name in the Field Properties section of the Field Name in the underlying table.

MAKING FORMS EASIER TO USE

A well-designed form makes data entry easier and more accurate, especially when you want other people to enter data into a database.  By limiting the data the user sees, carefully arranging input boxes and providing explanatory text, you can guide the user through the data-entry process. Often the use of a combo box or list box using a lookup table or predefine list will help maintain data integrity within a given field of data. 

A form can include as many as five sections.  The data itself typically appears in the Details section; in addition, each form can have up to two headers and two footers, which one header/footer combination for the form itself and another for individual pages.  Use the View menu to hide or show headers and footers.

Simple forms generally show the contents of one record at a time, but you can also design a form that include a subform, which displays information from a related table or query.  If you choose a table that includes a subdatasheet and then create an AutoForm, Access adds a subform automatically.  Using this type of form, you can scroll through groups of records, or search for information by using filters and other search tools.  Figure 4 shows a form and subform combination in which the main forms is bound to the Customers table and the subform is bound to the related Products table.  Use the navigation buttons to move through the list of orders. 
Home