Id
Id Department Code
A database is a collection of related information, or data. Some examples are: phone books, medical records, personnel records.
Access XP is an electronic relational database-management system for Windows. It enables you to electronically store, organize, and manipulate collections of information.
Sort: You can rearrange information in different ways (alphabetic or numeric).
Extract: You can extract specific information and view it, work with it, or print it. For example, an employee name and birthday list.
Summarize: You can manipulate and summarize information. For example, you could list employee hours and rates, grouped by department, with totals and averages for each department and for the entire company.
The way the information is organized in a database makes it possible to extract and manipulate data with ease and flexibility. Data-storage terminology common to relational database-management systems includes the following:
Data value: one item of data. For example, a phone number.
Record: A single set of related data values. For example, first name, last name, address and phone number for one person in an address book.
Field: A category of information within a set of records. For example, the last names, first names, addresses, or phone numbers of the address book.
Table: A group of records collected and stored in rows and columns.
Database: One or more tables of data related to a particular purpose, and the tools for using the data. For example, a company might have a sales database and a personnel database that hold all the data relating to either sales or personnel. Access XP is a relational database-management system; with it, you can store data in separate tables, yet display related data together in one table.
In Access, databases consist not only of data, but also of objects by which you work with the data. An Access database can consist of such objects as tables, queries, forms, reports, pages, macros, and modules.
A Table object enables you to store a collection of related information in a rectangular arrangement of rows and columns. Tables must be designed properly and created first because they are the foundation of all the other objects in the database – forms, reports, and queries, for example.
A Form object enables you to enter and view information in a table or query. Forms are custom designed – they can display all or just some of the fields of a record. A form displays data from the table it is based on. Because forms are often used for entering data, they enable you to focus on the record you want to enter or edit without the distraction of seeing other records. Forms can be simple (just to get the data into the database) or customized and enhanced to make working with the data easier for you (using a database to answer customer service requests).
A Query object enables you to ask questions of, analyze, and locate data stored in tables. With a query, you can locate certain pieces of information contained in a particular field, a set of records, or both. You can also analyze trends by sorting, calculation, and summarizing data obtained with queries.
A Report object enables you to analyze and print data in a specific format. Although you can print a table or form, a report gives you greater control over the format of the printed output. You can create a report from a table or a query. Within a report, you can print some or all of a table’s (or query’s) fields and records, as well as summary information.
A Page object enables you to build Access forms and reports that run in your Web browser.
To Start the Access program, double click on the Access icon on the desktop.
Be sure the Task Pane is visible. Click View, Toolbars, then click on Task Pane if it does not already have a check mark beside it.
In the Task Pane, under “New” select “Blank Database”
The “File New Database” window will appear.
Type in the File name to call your database.
Be sure you have the correct information in the “Save in” field.
Then click on “Create”
The title bar (at top – has 3 buttons on far right: minimize, resize, close)
The menu bar (below the title bar)
The toolbar (below the menu bar) – includes buttons with icons (small pictures representing options) that you can use to performs actions quickly. To display the name of a button, place the mouse pointer directly over the button and pause. Access displays the name in a Screen Tip under the button.
The status bar is the area at the bottom of the window.
The Database window is the primary workspace of the Access environment. It contains the objects that together make up an Access database.
The Object Bar – the vertical bar in the left pane of the Database window. It enables you to view and switch between the different objects of a database.
Database window toolbar –
Open, Design, and New buttons - These buttons are used to open or modify existing objects and to create new ones.
Delete (X) – to delete an object, such as a table, query or report.
The following buttons allow you to change the way you view the items in the window:
Large icons
Small icons
List
Details
The 1st 3 choices in the Database window –
Create table in Design view
Create table by using wizard
Create table by entering data
- are the equivalent of using the New toolbar button. The choices are used to create new objects, using one of several methods.
Planning a database
Planning is the most important step in creating a database. It is critical that you plan ahead before creating the files in which you will store data. If you thoroughly evaluate your needs, in terms of what information should go into your database and what information you want to get out of it, you are less likely to discover that you are missing vital information or that your data is less then optimally organized. Although you can always make changes to a database later on, doing so can be time consuming.
Each database you create should be set up for a specific purpose. When planning a database, always ask yourself the following question: “What kind of information should the database store and what should the database do?” Consider the following issues from the standpoint of you (the designer) and others (the users):
· What questions will be asked of the data?
· What reports will need to be produced?
· What forms will be useful?
· How will the data be sorted and grouped?
Each field should contain the smallest meaningful value possible. Fields are usually determined by the data itself. For example, a database of personnel information would include fields for name, address, pay rate, and so on. Putting the 1st and last names into a single field makes it difficult to sort the database by last name. You need to consider the types of data that you plan to put in each field. Some of the most common field data types are Text, Integer, Currency, and Date/Time.
Because Access is a relational database-management system, you can view the information from all of the tables at the same time by joining, or relating, the tables to one another. After you define relationships between the tables, you have the flexibility to bring the data from multiple tables together in a query, form, or report.
An example of 3 tables in a relational database:
Personal Data Human Resources Data Department Names and Codes
Id
Id Department Code
First Name Hours Department Name
Last Name Pay Rate
Address Parking Lot Code
City Department Code
State Health Care
Zip Comments
Phone
Hire Date
When creating relationships between your tables, you always work with 2 tables at a time. To join 2 tables, each table must contain a common field. These common fields must contain identical information for each record.
There are 3 types of relationships that can exist between 2 tables:
1-to-1, one-to many, and many-to-many. Only those tables with a 1-to-1 or a one-to-many relationship should be joined. To determine the type of relationship between 2 tables, examine the data in the common field.
A 1-to-1 relationship exists when one of each data value on the common field of the first tabel matches one of these values in the second table. For example, Personal Data and Human Resources Data have a one-to-one relationship; each record in the first table has a corresponding record in the related table.
An example of a one-to-one relationship:
Personal Data Human Resources Data
Id
Id
First Name Hours
Last Name Pay Rate
Address Parking Lot Code
City Department Code
State Health Care
Zip Comments
Phone
Hire Date
The Table Wizard assists you in creating a table by offering sample tables for different applications. Each sample table contains a set of sample fields specific to the application. You select the sample table and fields that you want; Access builds the table.
Verify that the list of table objects is displayed. (In the Object bar, select Tables).
Select Create table by using wizard.
A window appears titled “Table Wizard”
Notice that there are 2 major categories to choose from: Business and Personal.
Observe the Sample Tables and Sample Fields.
The 4 arrow buttons perform the following:
> add one field
>> add all fields
< remove one field
<< remove all fields
For each field that you want, click on it and click on > to select it.
When done selecting fields, click on Next >.
You’ll see the next Table Wizard dialog box.
In the “What do you want to name your table?” textbox, type the table name you want to use.
Verify that “Yes, set a primary key for me” is checked.
When you create a table, you need to decide the field to use as a primary key. When you use the Table Wizard, you can choose the primary key after you finish designing the table, or you can let Access choose the primary key for you.
If you let Access choose it, the primary key field will be an AutoNumber field. An AutoNumber field is a field that automatically increments and enters a unique integer for each new record.
Click on Next >.
Verify that “Enter data directly into the table” is selected.
Click on Finish.
The table appears in Datasheet View. (notice “Datasheet View” in lower left corner)
Click on the View button (1st one on toolbar, looks like blue triangle) to switch to Design View. (if desired)
Notice this button changed (looks like a chart). You can toggle back and forth between datasheet view and design view as needed.
You can use Datasheet View to enter data. As you enter data, you can use the enter key, or the arrow keys or the tab key to move to the next field.
As you enter the information for one record, notice a pencil appears to the left of the record. That means that the record is being edited and is not yet saved.
As soon as you hit enter at the end of a record, the record is automatically saved, the pencil disappears and the cursor is at the beginning of the next record.
(same as how you do it in Excel!)
You can adjust the column widths to make it easier to view your data. Place the cursor on the right edge of a column heading (field name) so that the cursor becomes 2 arrows.
Left click and hold, then move left or right as needed to adjust the column width. When you release the mouse, that’s where the column stays.
-Or-
Double click and the column automatically adjusts to accommodate the widest entry in the column.
Notice that if your record does not extend beyond the right edge of the screen, the horizontal scroll bar is no longer available (because you don’t need it!). Likewise, with the vertical scroll bar – if the records don’t fill up the screen, the vertical scroll bar is not there.
Click once anywhere in the column you want to sort.
Click on the Sort Ascending button (AZ down arrow) on the toolbar.
Formatting the Datasheet
Select Format, Font to change the font style and size, for example.
Select Format, Datasheet to change the attributes of the datasheet, such as the background color.
By opening a table, you bring a copy of its data into computer memory and display the information in Datasheet View. Datasheet View provides a tabular view of your data – each column is a field and each row is a record. In Datasheet View, you can add, edit, or view the data in a table. You can also check spelling, print the table data, filter or sort records, change how the datasheet is displayed, and change the table structure by adding or deleting columns.
The toolbar buttons displayed in Datasheet View are different from those displayed in other Windows and Views.
The status bar (lower left of window) verifies that you are working in Datasheet View.
Record indicator: The dark triangle in the far left column. It points to the active record.
The status area: located in the lower left corner of the window. It indicates the active record and the total number of records.
You can use your mouse to navigate within the table. Scroll bars frame the right and lower borders of the window. You can use the scroll bars to change the portion of the table displayed in the window. Navigation buttons are located in the lower left corner of the window. You can use them to make a record current.
|
Using the scroll bar |
To move |
|
Click on the down (or up) scroll arrow several times |
Down (or up) 1 record per click. |
|
Drag the vertical scroll box to the bottom (or top) of the scroll bar. |
To the end (or top) of the table rapidly. |
|
Click on the right scroll arrow several times. |
To view the last field in the table. |
|
Drag the horizontal scroll box to the left |
To the 1st field in the table quickly. |
Navigation button
> Next Record
< Previous Record
>| Last Record
|< First Record
You can also double-click in the Specific Record Number box, type in the record number and hit enter to move to that record.
Click on the New Record button >* to add an empty record at the end of the table.
Click on the First Record button |< to move to the top of the table.
Although the Table Wizard creates a table that is close to what you want, you will probably need to modify it. You can revise the table in Design View. You can change field names, data types, and any of the field properties.
Access can process smaller field sizes quicker. Changing the maximum number of characters you can enter in a field also helps to reduce errors. For example: setting the field size of “State” to 2 characters long.
You can view and modify the design of a table in Design View. A table’s design includes the definition of field names, the order in which fields are displayed in the table, the type of data that each field contains, and the size and format of the fields. A table can contain up to 255 fields for each record. A table’s design can also include field descriptions and a primary key. If a primary key is defined, then the records in the table will be sorted by the field(s) in the key; moreover, no duplicate information will be allowed in the key field(s).
The View button is the 1st one on the toolbar. It contains an icon of design tools. When you click once on the View button, the view changes to Design View. This is a convenient way to change between views.
Click on the View button to view the design of the table.
Now notice that the view button has changed to reflect the default view you can change or toggle to (Datasheet View).
The characteristics of the data you plan to store in a table help you to determine the data type you need. Data type determines the kind of data that can be entered in a field and what kinds of operations Access can perform on it. The following table describes the different data types and their uses:
Text Text and numbers that are not involved in calculations or that contain leading zeros (for example, phone numbers and zip codes).
Number Numeric data for calculations (except calculations involving money).
Currency Values representing money.
Date/Time Dates and times; a variety of display formats are available.
Yes/No Fields that contain only 1of 2 values (for example, Yes/No, True/False, or On/Off).
Memo Lengthy text and numbers, such as comments or explanations.
AutoNumber Sequential numbers assigned by Access.
OLE object An Object Linking And Embedding object created in another application.
Primary key
A primary key is a field (or combination of fields) that uniquely identifies each record in a table. Fields that can serve as primary keys include employee Ids, customer account numbers, purchase order numbers, and part numbers. Every table includes a primary key so that many operations can be performed faster.
Access automatically sorts the records in the table by the values in the primary-key field. Access will not permit you to enter records that contain duplicate values in the primary-key field, nor will Access permit you to leave these fields blank.
Field properties
Each field has a set of properties that you can use to customize how a field’s data is stored, handled, or displayed. The properties that are available for each field are determined by the type you select for the field.
When you select a data type or set a field as a primary-key field, certain properties are automatically set for it. For example, number and currency fields have a default value of 0; a text field allows 50 characters, a primary-key field is set to be indexed. By using the Field Properties pane, you can control the attributes of the field by changing the assigned properties or by affecting other properties.
Modify the table design
Insert fields
Delete fields
Rearrange fields
Change field properties
Find and edit records
Use the find feature to locate a record
Spell check
Add records
Move/copy data
Filter records
Remove filter
Sort records
Sort with multiple fields
Create a query
Use criteria to select records
Run the query
Sort records in a query
Save a query
Using operators in query criteria
Use multiple criteria in a query
Use Null values in a query
Modifying the table design
Click on the row selector to select the location for the new field.
Click on the Insert Rows button.
--- or ---
Click on the row selector for the field to be removed.
Click on the Delete Rows button.
Click on Yes to delete the field and the data contained in it. An alert box asks you to confirm the deletion.
Click on the row selector to select the field to be moved.
Press and hold the mouse button on the row selector.
Drag the mouse pointer (up or down) until a dark line appears between the fields where you want to insert your field.
Release the mouse button.
Field properties are a set of characteristics that define a given field. You can change a field’s properties to provide additional control over the kind of information you can enter in a field. You can customize how a field’s data is stored, handled, or displayed. For example, you can control the maximum number of characters that can be entered into a Number field by setting its Field Size property. (Common settings are Double and Long Integer).
The Integer and Long Integer settings do not include decimals.
The Single setting can display up to 7 decimal places.
The Double setting can display up to 15 decimal places.
A common database operation is finding and updating specific records. For example, if you manage a database of employees, you must update your table with the new address if one of them moves. Access offers several ways to locate specific records.
If you need to find a record in a table that has only a small number of records, it is usually easiest to scroll through the table by using your mouse or keyboard.
Here are some shortcuts:
End Last (rightmost) field of the record.
Home First field of the record.
Ctrl+End Last field of the last record in the table.
Ctrl+Home First field of the first record in the table.
When you deal with many records, you can use the Find feature to search for a key word or phrase in any field. You can locate a record by searching for a specific value, such as last name or part number. You can also find the 1st or next occurrence of a value.
Click anywhere in the column where you want to search.
Click on the Find button (looks like a pair of goggles).
The Find and Replace dialog box is displayed.
In the Find What text box, type the word to find. (Text is not case-sensitive by default).
Click on Find Next (or press enter).
Observe the datasheet. The item you’re looking for is displayed.
In the Find and Replace dialog box, click on Cancel.
To change the item you found, Press F2. The highlight changes to an insertion point.
You can press F2 again to select the whole item. Type the new item.
Before hitting enter, you can reverse changes to the current field by clicking on the Undo button or by pressing the Escape key (Esc).
If you do not know which field contains the information you need, you can search all fields (the entire table) for specific data.
Press Ctrl+Home to move to the 1st record in the table.
Click on the Find button to display the Find And Replace dialog box.
Type the item you’re searching for.
Display the Look In drop-down list.
Select “table” to perform the search in all fields in the table.
Click on Find Next.
Observe the datasheet.
Click on Find Next again as many times as needed.
Click on Cancel.
The spelling check feature searches for spelling errors in a table, form, or query. Within a table, you can check the spelling of records, fields, or text within a field. To select just 1 field, for example, place the mouse pointer over the field label and click once. When selected, the field is highlighted.
Click on the Spelling button ABC ü to display the Spelling dialog box.
Access checks the data until it finds a word that is not in the dictionary file.
The unrecognized word is displayed in the “Not In Dictionary” text box.
You can manually correct the word, replace it (Change) from the list of suggested spellings, ignore it, (Ignore) or add the word to the custom dictionary (Add).
Ignore All and Change All affect every occurrence of the word in question no matter how many times it appears in the table.
Before you can delete values or records, you must select them. Selecting data means highlighting it so that Access knows which data you are dealing with.
To select: Do this:
A value Click & drag over the value
A field Place the insertion point in the field and press F2 or
Place the mouse pointer on the left edge of the field so that it’s a fat plus sign and then click.
A record Move to the record and click on its record selector.
Multiple records Click on the record selector of the 1st record and drag to the last record in a contiguous series.
After you have selected values or records, you can delete them by clicking on the Delete Record button 4X or by pressing the Delete Key.
An alert box appears to confirm whether or not you really want to delete the record.
Click Yes to delete it.
Adding records
In Access, new records are added at the end of the datasheet. A blank record is always available and is indicated by an asterisk (*) in the record selector.
Click on the New Record button 4* to add a new, blank record at the end of the datasheet.
Moving or Copying data
You can place data from one location in a table to another location by moving or copying it. To do this, use the Cut, Copy, and Paste buttons on the toolbar.
For example, select the value you want to copy, click on the Copy button, click in the field where you want to copy to and click on the Paste button.
Copying values
When the insertion point is in a field, to copy the value from the previous record,
press Ctrl+’ (Ctrl & apostrophe key).
To insert the system date (today’s date): press Ctrl+; (Ctrl & semicolon key).
Filtering records
To view only those records that match a specific criterion. A filter is a technique that enables you to view and work with a subset of data. For example, you can specify a filter to view only records that contain “Wexford” in the City field.
To apply a filter:
In the field you want to search, click anywhere in the value (such as “Wexford”) to define the criterion for the filter.
Click on the Filter By Selection button (looks like a funnel & lightning bolt!) to apply the filter.
Observe the field you searched in. Notice that Access displays only the records that meet the criterion.
Observe the toolbar. Notice that the Remove Filter button is highlighted.
To remove a filter:
Click on the Remove Filter button (looks like a funnel) to remove the filter and view all the records again.
Notice that this button is a toggle – Remove Filter / Apply Filter.
It “remembers” the most recent filter.
Sorting records
Sometimes information in a table might not be in the order you want. You can sort (rearrange) the data based on any field or combination of fields in the table. You can sort records in alphabetic, numeric, or date order. You can sort data in a table or a form.
Place the insertion point anywhere in the field (column) to be sorted.
Click on the Sort Ascending button AZ¯ or Sort Descending button ZA¯.
To put the records back in their original order, choose Records, Remove Filter/Sort.
Multiple field sorting
The Sort Ascending/Descending buttons work on only 1 field at a time. To perform more complex sorts, such as sorting by 2 or more fields or in ascending order by 1 field and descending order by another field, you must use the Advanced Filter/Sort window. This window is split into 2 sections: a table and field list in the upper pane, and the design grid in the lower pane. Select the fields you want to sort by and drag them to the design grid. In the Sort cell, select either ascending or descending, depending on the type of sort you want to perform. Click on the Apply Filter button to perform the sort.
Choose Records, Filter, Advanced Filter/Sort.
Notice the field list is in the upper pane. The filter/sort design grid is in the lower pane. There may be fields already included in the grid due to recent filters and sorts.
Click on Edit, Clear Grid to delete the fields from the lower pane.
In the field list, select and drag the field to the first empty field cell in the design grid. (Primary sort).
Select the next field and drag it to the next empty field cell. (Secondary sort).
In the sort cell for each field, display the drop-down list and select Ascending, for example.
Click on the Filter, Apply Filter/Sort to perform the sort.
Observe the newly sorted records.
To display the records in their original order: Choose Records, Remove Filter/Sort.
Using select Queries
Many times, you do not need to see all the data in your table. You might want to see only certain fields, records, or both. Queries are the objects that you use to locate information stored in a table. To view only the data you want, use select queries; they enable you to select specific fields and records from a table. When you use a query, you must specify how you want the results to be displayed. By choosing the fields you want, and setting criteria for those fields, you can extract the precise information you want. For example, you might want to display only the names and phone numbers for people who work at the High School.
Creating a Query
You create a query in the query Design View window. Select queries have 3 components:
1. The source of the data (such as a table, multiple tables, or another query).
2. The question (the query design). The design of your query tells Access what data to retrieve.
3. The answer (the temporary result called the query Datasheet View). In Access, the result of a select query is placed in a temporary datasheet.
With a table open and displayed in Datasheet View, click on the New Object button’s drop-down list (far right on toolbar).
Select Query (to create a new query).
The “New Query” dialog box appears.
Verify that Design View is selected.
Click on OK.
Observe the query Design View window.
The upper pane contains the field list of the table (or tables) on which the query is based.
You can:
view all the table’s fields by scrolling through the field list.
move the table for easier viewing: click on its gray bar at the top and drag it around.
resize it for easier viewing: place the mouse pointer on any edge or corner, then click and drag.
You can also resize the panes by clicking on the solid line between the panes and dragging it up or down.
The lower pane contains the design grid. Each column is a field that will be included in the query Datasheet View.
To rearrange or delete fields in the design grid:
First, you must select the field. To do this: place the mouse pointer at the top of the column so that it becomes a solid down arrow, then click once to select that field only (or click & drag to the right or left to select multiple fields).
Press the delete key to delete any selected fields.
Once a field is selected, click on the very top of the column and drag it to move it.
Selecting fields to include in the query
For a field to be displayed in the query results, you must drag the field from the field list to a column of the design grid. The fields will be displayed in the query result in the same order as they are placed in the design grid.
From the field list, select the field you want and drag it to the 1st empty Field cell in the design grid (or just double-click).
Observe the Field cell. It contains a button with a 6.
Click in the next empty Field cell.
Display the drop-down list. The drop-down list contains the field list.
Select whatever field you want.
Observe the “Show” check box b . By default, the Show check box is selected. This means that the field will be displayed in the query Datasheet View. If you don’t want a field to appear when the query is run, simply click on the check box to remove the checkmark.
An example where you wouldn’t show a field in a query:
You have a table of employee names and phone numbers. You want to know what employees have 724 as their area code (& therefore need to use the phone number in the query) but don’t want to display the actual whole phone numbers.
Run the query
Once you have your query set up the way you want, you can run it.
Click on the Run button ! (or View button) to run the query.
Observe the results.
Query results
Records are in the order in which they are displayed in the table.
The results of a select query are placed in a temporary datasheet. You can navigate within the temporary datasheet as you would within any table.
You can even edit the records and add new ones.
Any changes you make are incorporated in the table on which the query is based.
Printing the results of a query
To print the query results, display the query in Datasheet View. Click on the Print button on the toolbar. You can also use the Print Preview button.
Sorting records in a query
You can sort records in a query the same way that you sort records in a table. Sorting records can make data easier to review and edit. In a query, you can sort up to 10 fields.
From Design View:
In the design grid, in the column(s) to sort, click in the Sort cell.
In the design grid, each field column contains Sort cell; clicking on that cell displays a6that you can click on to display the drop-down list.
Run the query and view the results. (Click on the Run button or the View button).
Using criteria to select records
You can select specific records to be displayed by entering criteria for one or more fields in the design grid. Criteria are conditions that identify the records you want to view.
Each field (column) includes a Criteria cell.
From Design View:
In the design grid, in the appropriate column (such as “City”), click on the Criteria cell.
Type the criteria (such as “Pittsburgh”).
The Criteria cell is not case sensitive.
Press Enter.
Notice the Criteria cell.
Run the query and view the results.
Saving a Query
You can save the design of a query by choosing File, Save As or by clicking on the Save button.
Also, if you close the query window without saving, you’ll have the option to save it.
The Save As dialog box appears.
Type the name for the query.
Click on OK.
Each time you run the query, the query Datasheet View will include current data from the table.
Refining the results of a query
If the query does not give you the results you want the 1st time you run it, you can modify it by adding or deleting fields, by changing the field order, or by changing the criteria.
You can repeatedly use the View button (far left on toolbar) to switch back & forth between the query design view and the query results (datasheet view).
You may add consecutive and/or non-consecutive fields to the design grid of an existing query.
Use the Shift key to select consecutive fields from the field list.
Click on a field.
Press Shift and click on another field that’s not right next to it.
Use the Ctrl key to select non-consecutive fields from the field list.
Click on a field.
Press Ctrl and click on any other fields in the list.
Place the mouse pointer on any one of the selected fields and drag to the next empty Field cell in the design grid.
Using operators in query criteria
Besides using exact match criteria, you can also select records using an expression that includes an operator. Operators are often used to define a range of values for the criteria. A common Access operator is the wildcard (*). The asterisk substitutes for a group of characters.
In Design View of a Query:
Click in the Criteria cell (for “City” for example).
Type P* to select records for cities that begin with the letter P.
Text searches are not case sensitive.
Press Enter.
Observe the criteria cell.
The like operator and double quotes are entered automatically when you use a wildcard.
Run the query and observe the results.
Comparison operators
Comparison operators are useful when you want to compare the values in a field with a predefined value.
Comparison
Operators Meaning
< Less than
> Greater than
= Equal to
<= Less than or equal to
>= Greater than or equal to
<> Not equal to
In the column for the field to perform the comparison on, click in the Criteria cell.
Type the comparison operator and value (such as >34).
Run the query and view the results.
Multiple criteria
You can use the logic operators, such as AND and OR, to establish multiple criteria within the same field.
AND conditions
An AND condition requires that 2 or more criteria must be true for the records to be included in the result. In the design grid, you can create 2 basic types of AND conditions:
AND conditions that contain criteria in multiple fields, and
AND conditions that contain multiple criteria in 1 field.
To create an AND condition for more than 1 field, enter criteria on the same row of the grid.
To create an AND condition within 1 field, type the word AND in its Criteria cell between the criteria. For example, >10 and <20.
When you type the word “and” it doesn’t matter if its caps or lowercase.
OR conditions
An OR condition requires that at least 1 condition must be true for the records to be included in the result. In the design grid, the OR row is directly under the Criteria row. Entering criteria in this row creates an OR condition.
As with AND conditions, you can use OR conditions in more than 1 field. (different rows)
Null values
When a record does not have a value in a field, Access treats this value as null. Null values are not included in calculations, nor are they allowed in primary key fields.
You can use the IS operator to determine if the value in a field meets a certain condition. For example, “Is Null” determines if a field is empty, and “Is Not Null” determines if a field contains any value.
In the appropriate column, in the Criteria cell, type is null.
Press Enter.
Run the query and view the results.
Using queries to perform calculations
To create a calculated field, you enter an expression in an empty Field cell. An expression is any combination of functions, field names, numbers, text, and operators that calculates a result. The expression describes the calculation you want to perform. If the expression includes field names, you must enter square brackets around each field name. The expression can include the following arithmetic operators:
Operator Arithmetic
Symbol function
* Multiplication
+ Addition
- Subtraction
/ Division
^ Exponentiation
For example, to multiply intHours by curPayRate, enter the expression [intHours]*[curPayRate].
When you enter an expression in a Field cell, Access supplies a default column name such as Expr1. You can replace this name with a more meaningful name.
Click in the Field cell.
Type in the expression. You must enclose field names in square brackets.
Press Enter.
Optional but helpful: At the division line to the right of the calculated field column, place the insertion point on the gray bar and double-click. (This makes the entire expression visible).
Run the query and view the results.
Change to Design View.
In the column for the calculated field, double-click on the field name (such as Expr1) to select it and type a new field name.
Adjust the column size to fit the entire expression.
Run the query and view the results.
Joining tables in a query
Pulling data from more than 1 table at a time enables you to get the most out of your database. Whenever you construct a query involving 2 or more tables, you must tell Access how to join (or link) the information in the tables.
Joining tables automatically
Access automatically joins 2 tables if they each contain a field with the same name and data type, and if this field is the primary key in at least one of the tables.
The join line is displayed in the upper pane of the query Design View window.
Create a new query in Design View.
Click on the Show Table button.
Add a table(s) to the query.
Click on Close to close the Show Table dialog box.
Observe the join line between the tables.
Select fields from any of the tables for the design grid.
Notice that the Table row tells you what table each field is from.
Joining tables manually
If the tables in your query do not have an established relationship, or a common field with the same name and data type, you must create the link by dragging a field from 1 table to the matching field in the other table. This type of join applies only to the query and does not establish a permanent relationship between the tables.
Creating a join in query Design View
With a query open in Design View:
Click on the Show Table button to display the Show Table dialog box.
Add the appropriate table.
Close the Show Table dialog box.
In the field list of 1 table, place the mouse pointer on a field and drag it to the field list in the other table.
Two tables can be joined by fields of the same data type which contain common values.
Add fields to the design grid.
Run the query and view the results.
Removing joins in queries
If you need to delete the join between tables in a query, right-click on the join line between the tables and choose Delete. Or, in query Design View, select the join line and then press the Delete key. Deleting a join line in a query affects only that query.
Properly organized and well-designed forms make a tremendous difference in the productivity of the end user. Forms are often built to match a source document (such as an employment application) to facilitate fast and accurate data entry. Form design considerations, such as clearly labeled fields and appropriate formatting, are important.
Determine the overall purpose of the form.
Have a good understanding of what information you need to gather through the form.
Determine the underlying record source.
The record source is either a table or query object, and contains the fields and records that the form will display.
Gather the source documents used to design your form, or sketch the form by hand if a paper form does not exist.
Determine the best type of control to use for each element on the form.
“Controls” are all the different items that can be used on a form, such as labels, text boxes, list boxes, check boxes.
Open an existing file.
Click Forms on the Objects bar.
Double click on Create form by using wizard.
Click the select all fields button (>>). Next >
Select the layout. Next >
Select the style. Next >
Change the name of the form if desired. Finish
That’s it!
The form opens in Form View. Notice “Form View” in lower left corner of the screen.
Descriptive labels are displayed and text boxes that display data from the underlying records.
You can enter, edit, find, sort, and filter records using the form.
Click on the text box to sort on, click on Sort Ascending (AZ) button.
Notice the Record Navigation buttons in the lower left corner of the screen.
Record: First Record, Previous Record Current Record Next Record Last Record New Record of Total Number of Records.
Click in the text box to filter on.
Click on Filter by Selection button (funnel & lightning bolt).
Notice in lower left corner of screen “Record # of # (Filtered)”
To view all records again, click on the Remove Filter button (Funnel).
After you create a form, you can modify the size, location, and appearance of existing controls in Form Design view. Form Design view also allows you to add or delete controls.
While in Form View, click on the View button (1st one on toolbar).
Now you’re in Design View. Notice “Design View” in lower left corner of screen.
The toolbox toolbar contains buttons that allow you to add controls to the form.
The field list contains the fields in the underlying object.
You can toggle both of these screen elements on and off as needed.
The Field List and Toolbox (hammer + wrench) buttons are right next to each other on the toolbar.
Place the pointer on the right edge of the form (+) and drag left or right to resize.
Click on an item such as a label or text box. Squares, called sizing handles, appear in the corners and on the edges of the selected control.
You can click & drag with these sizing handles to change the size of the items.
Place the mouse pointer over a selected control.
Pay close attention to the shape of the mouse pointer
Whole hand – moves the label & text box both.
Pointing hand – moves only the one item selected.
Click and drag the control to move it to another location.
Moving and resizing controls requires great concentration and mouse control. Precision and accuracy are naturally developed with practice. Don’t forget the undo button!
Click the Form View button on the toolbar to switch from Design View to Form View to really see the results of your work.
Single click on a label to select it, then single click again to edit it.
- or –
display the control’s property sheet in 1 of 2 ways:
double click on the label.
- or -
click on the Properties button (looks like chart & hand) on the Form Design toolbar.
With the property sheet displayed, select either the Format or All tab, change the caption, then close the window.
Caution: be sure to change only the label – not the text box. The contents of the Text box must reference the exact field name in order to display the data within that field.
Click on a label, then click on any of the formatting buttons such as Font, Font Size, Bold, Italic, Underline, Align Left, Center, Align Right, Fill Color, Font Color.
You can also format more than 1 label at once:
Place the mouse pointer on the ruler (over a group of labels) so that it becomes a black down arrow.
Click to select multiple labels.
Now apply whatever formatting you want to the selected labels.
Hint: you can use this feature to move many labels at once, too.
This is especially helpful if you want them to maintain their alignment with each other.
Other ways of selecting more than 1 control:
- click a control, then press and hold Shift while clicking other controls; each one will be selected
- click and drag a selection box around the items to be selected. Be sure the mouse pointer starts off as an arrow before you click.
- Drag through either the horizontal or vertical ruler.
Text boxes are generally used to display data from underlying fields and are therefore “bound” to that field. You can also use a text box as a calculated control which is not directly bound to a field but rather uses information from a field to calculate an answer.
From Design View:
Click the Text Box button (ab|) on the Toolbox toolbar.
Click in the form where you want the new text box to go.
If needed, move the new text box where you want it.
Notice you automatically get a new label with a new text box.
Click “Unbound” in the new text box, type in the expression, then press Enter.
The expression might be, for example: =quantity*unitprice
Click the Text# label to select it, then click it again to edit it and type in a new caption and press Enter.
Click on the Calculated text box, click the Properties button (paper + hand) on the Form Design toolbar.
Click the Format tab.
Click the down arrow for “Format” (it’s the 1st one in the list).
Click “Currency.”
Scroll down the format list till you see Text Align, click in the white area to its right. Click the down arrow and select “Right” for example.
Close the properties window.
If you change any of the fields used in a calculated text box, the calculated field automatically updates.
“Tab Order” is simply the path that the cursor follows when you “tab” or “enter” through the fields in Form View. You can change this order to be whatever you want.
From Design View:
Click View on the menu bar, then click Tab Order.
Position the mouse pointer to the left of a field (on the row selector) so that it becomes a black arrow. Click to select that row. (or click and drag to select more than 1 row).
Click in the row selector (not the field name) and drag it to the desired location.
Notice the black line between rows – that’s the insertion point.
Release the mouse button to drop whatever was selected into position.
Click OK when done.
Switch views and tab through the fields to see if it works the way you intended.
From Design View:
Place the mouse pointer on the bottom edge of the Form Header, then drag the bottom of the Form Header section to wherever you want.
You may do the same with the Form Footer.
Click the Label button (Aa) on the Toolbox toolbar.
Click in the Form Header section, Detail section, or Form Footer section, and type whatever you like, then press Enter.
With the new label selected, you can change its formatting such as Font, Font Size, etc.
If you make a change and the whole label is not displayed you can resize or just double-click one of the sizing handles and the label will automatically adjust to display the entire caption.
Click File on the menu bar, click Print, click the Selected Record(s) option button in the Print Range section, then click OK.
You can also use Print Preview.
From Design View, click the Command Button (looks like a gray rectangle) on the Toolbox toolbar.
Click and drag in the form an area where you want the button to appear. You can make it any size.
When you release the mouse button, a dialog box will appear called “Command Button Wizard.”
Each category has different Actions associated with it.
Select a Category, select an Action, click Next >
At the next window, you will select whether you want text or a picture to appear on the button. If you choose text, you can type in what you want the text to be.
If you choose picture, you can click on the different pictures and preview them.
When done, click Next >
At the next window you’ll type in a name for the button. When done, click Finish
Switch views and test out your new creation!
You can also move your button around, resize it if necessary, and add more buttons.
A combo box is a combination of the list box and text box controls. It offers 2 options: a list of values to choose from and the option to just type in the value.
From Design View:
Be sure the field list and toolbox toolbar are available. If necessary, click their buttons on the toolbar. One looks like a chart and the other one looks like a hammer & wrench.
Click the Combo Box on the toolbox toolbar, click the field in the field list, then drag the field to the space where you want this combo box to go.
When you release the mouse button, the “Combo Box Wizard” window appears.
Click “I will type in the values that I want”
Click Next >
Type in the values that you want for your combo box. Tab from one to the next. If you hit Enter by accident, click the “Back” option to resume.
Click Next >
Select “Store that value in this field” By default, the field you selected is displayed.
Click Next >
Type in the name for the combo box.
Click Finish >
Switch to Form View to test it out. Notice the drop down arrow. You can either type in the entry in the blank field or select an entry from the drop down list.
You can go back to design view and move or resize the new combo box.
Information entered from a combo box updates the underlying table.
You can easily change a list box to a combo box and vice versa:
From design view, right click on the box, select Change to, select List Box or Combo Box.
The list box is a control that provides the user with a list of values to choose from.
You cannot type in your own entry with this control.
To create one, simply follow same procedure for combo box, except select the List Box button (instead of the Combo Box button) from the Toolbox toolbar.
Add Option Groups
An option group is a special type of bound control that is often used when a limited number of values are available for a field. The option group control uses option button controls (sometimes called radio buttons) to determine the value that is placed in the field. One option button exists for each possible entry. When the user clicks an option button, the numeric value associated with that option button is entered into the field bound to the option group. Option buttons within an option group are mutually exclusive, which means that only one can be chosen at a time.
From Design View:
Click the Option Group button (xyz) on the Toolbox, click the field in the field list, then, drag the field onto the form.
The Option Group Wizard appears.
The first question asks about label names for the option buttons.
Type those in, tabbing from one to the next.
Click Next >.
Choose “No, I don’t’ want a default”
Click Next >.
The next question prompts you for the actual values associated with each option button.
Type those in, tabbing from one to the next.
Click Next >.
Click Next > to accept to accept the field that the value is stored in.
Select the type of control you want to use (notice the preview area to the left):
Option buttons
Check boxes
Toggle buttons
Select the style you would like to use (notice the preview area to the left):
Etched, Flat, Raised, Shadowed, or Sunken.
Click Next >.
Type the caption in.
Click Finish.