MAA TECHNICAL COMPUTER TRAINING CENTER: Access

MAA TECHNICAL COMPUTER TRAINING CENTER: Access

Showing posts with label Access. Show all posts

Showing posts with label Access. Show all posts

Wednesday, November 27, 2013

Running and Printing Database Reports of Access 2003

saifur rahman November 27, 2013 0

Running and Printing Database Reports of Access 2003

Introduction

By the end of this lesson, learners should be able to:

Perform a Filter By Selection
Remove a Filter
Perform a Filter Excluding Selection
Perform a Filter By Form
Running Contact Management Reports
The Contact Management database contains two reports that you can use to print a complete list of contacts in the database (Alphabetical Contact Listing Report), as well as a call log to recap phone-call summaries made between any two dates (Weekly Call Summary Report).

To run the Alphabetical Contact Listing Report:
- On the Main Switchboard form, click once on the Preview Reports menu selection.
- On the Reports Switchboard, click once on the Preview the Alphabetical Contact Listing Report menu selection.
- The Alphabetical Contact Listing Report is displayed.
The Contact Management reports can also be run in Datasheet View by selecting the Reports tab from the Object palette of the database window. Then double-click on the Alphabetical Contact Listing report.
Running Contact Management Reports (continued)

To run the Weekly Call Summary Report:

On the Main Switchboard form, click once on the Preview Reports menu selection.

On the Reports Switchboard, click once on the Preview the Weekly Call Summary Report menu selection.

In the Weekly Call Summary dialog box, type the date range in the Begin Call Date and Ending Call Date fields. This lets you search the database for calls made between two defined dates.

The Weekly Call Summary Report is displayed

Creating a Report using AutoReport
The reports object in Access allows you to create a report to present your data in a meaningful and attractive printout. One way to create a report in Access is to use AutoReport. This report format quickly generates a columnar or tabular report format for records in a selected table.

To Create an AutoReport:

Open the database window and choose the Reports selection from the Objects palette.

Click the New button to open the New Reports dialog box.

Choose either the AutoReport: Columnar (prints one record in columnar format) or the AutoReport: Tabular options (prints all records in tabular format.)

Click the drop-down list and choose the table or query on which the report or query is based.

Click the OK button to create the report and open it in Print Preview. (The mouse pointer changes to a magnifying glass. Remember, you cannot edit data in Print Preview.>

Columnar Report Example:

Tabular Report Example:

After you have created a report, you will be asked to save the report when you close it or exit Access. When you save a report, only the structure of the report is saved and not the underlying data seen in print preview.

Creating a Report Using the Report Wizard
Another way to create reports in Access is to use the Report Wizard. The Report Wizard asks a series of questions that you must answer. Access uses your responses to create the report.

To Create a Report using the Report Wizard:

Open the database window and choose the Reports option from the Object palette.

Click the New button to open the New Reports dialog box.

Click on the Report Wizard selection.

Click the drop-down list and choose the table or query on which the report or query is based.

Click the OK button to begin the Report Wizard

Creating a Report Using the Report Wizard (continued)
In the Report Wizard's first dialog box,

Choose the table or query in which you would like to base the report.

Highlight the first field from the Available Fields that will be included in the report and click the right arrow to move the field to the Selected Fields box.

Repeat so that each field is included in the report, or the click the double arrow to move all the fields for the report.

When finished, click the Next button.

In the Report Wizard's second dialog box, you can select a field name for grouping purposes. For example, by selecting First Name, notice how First Name becomes the group header (blue text) in the right side of the picture. You do not have to select any grouping levels.

Highlight the field that you would like to use as a group level, and click the right arrow to move the field to the Selected Fields box.

When finished or to bypass this screen, click the Next button.

Creating a Report Using the Report Wizard (continued)
In the Report Wizard's third dialog box, you can specify how or if the reports are to be sorted on the report. For example, if you wanted to show names alphabetically and by state, you would first sort by State and then by Last Name.

In the first field (optional), select a field name from the drop-down box only if records in the report are to be sorted by that field. Then, click the button to define whether records are to be sorted in ascending or descending order.

If necessary, repeat for each of the remaining three sort fields.

When finished or to bypass this screen, click the Next button.

In the Report Wizard's fourth dialog box,

Select one of the three listed Layout options: Columnar, Tabular, or Justified.

Select an Orientation for the report, either Portrait or Landscape.

(Optional), select or deselect the Adjust the field width so all fields fit on a page field.

Click the Next button to continue.

Creating a Report Using the Report Wizard (continued)
In the Report Wizard's fifth dialog box,

Click through the different format options displayed on the screen -- Bold, Casual, Compact, etc., to display a picture of each report format on the left side of the wizard screen. Highlight the desired format you would like to use.

Click the Next button to continue.

In the Report Wizard's sixth dialog box,

Assign a name to the report by typing a file name in the What title do you want for your report? field.

Click the Finish button to complete the wizard and generate the report.

You can decide to include any or all of the Report Wizard's selections in your report.
Very Important! When working in tables, forms, queries, and reports, use the New Object button on the toolbar to create new database objects (tables, forms, queries, reports).

Using Print Preview
When your report opens in Print Preview, it is usually displayed at 100%. However, to get a better look at various report features, you may need to resize your window.

Viewing a Report using the Print Preview Toolbar:

In Print Preview, your mouse pointer is the Zoom tool (magnifying glass), which allows you to "zoom" in and out. Click on the document (or the Zoom button on the toolbar) to "zoom" in for a closer look. Notice Print Preview's drop-down menu reads "100%."

Click again on the document (or the Zoom button) to "fit" the document to the Print Preview window.

Use the Resize drop-down menu to further resize your document.

Use the display buttons to display one or more pages.

Click the Database window button to bring the database window to the front.

Click the Officelinks button to "Publish it with Word" or "Analyze it with Excel". Clicking either of these choices will allow you to print your document as a Word or Excel document.

For Help, click the question mark.

Click the Close button to close your report and return to the database window.

Printing a Report
Any report in the Contact Management database can be outputted to a printer of your choice.

To Print a Report from Print Preview:

Click the Print button on the Print Preview toolbar to print your document (the Print dialog box will not open).

To Print a Report using the Menubar or Toolbar:

Choose FilePrint from the menu bar to open the Print dialog box.

Make any necessary changes to the Print Range, Copies, or Zoom sections of the Print dialog box.

Click the OK button to print the report.

Print Preview and Print are fully explained in the Office 2002 XP course.

.

Running Database Queries of Access 2003

saifur rahman November 27, 2013 0

Running Database Queries of Access 2003

Introduction

By the end of this lesson, learners should be able to:

Run an existing query
Create a Single-table query
Create a Multiple-table query
Run an Existing Query
Like tables and forms, a query is another type of database object in Access 2002 XP. A query is a search for records that match the exact criteria you define. In this example, we will run a query against the Contacts table and list all records found by Last Name, First Name, and Work phone.

To Run an Existing Query:
- Open the Contact Management database.
- In the database window, choose the Queries tab from the Object palette.
- To open a query, double-click the query title, or click once on the query title and then click the Open button, or right-click the title and choose Open from the shortcut menu.
- The query searches the database and then displays the results on the screen.
  Creating a Single Table Query
  In this example, we will create a new query and run it against that very same Contacts table. We will type the following command in the table: Show me the mailing address of all records in the Contacts table. When we create the query, we need to select the following fields in the Contacts table: Last Name, First Name, Address, City, State/Province, and Postal Code.
  
  To Create a Simple Query:
  
  Open the Contacts Management database.
  
  In the database window, choose the Queries tab from the Object palette.
  
  Select the Create query by using wizard option and click the Open button .
  
  The Simple Query Wizard opens.
  
  From the Tables/Queries drop-down list, choose the table/query containing the fields you want to include in the query.
  
  Creating a Single Table Query (continued)
  
  The Available Fields text box displays all the fields contained in the table selected in the Tables/Queries field. You are to select the fields to be used in the new query. You can pick one or more, or even all fields in the query.
  
  Click to highlight the first field to be included in the query -- Last Name, for example -- and then click the right arrow button. Repeat until you have selected all fields to be used (First Name, Address, City, State/Province, and Postal Code).
  
  If the fields you selected include a number field, you are asked to select a summary or detail query. To see each record, choose Detail. To see sums, averages, etc., choose Summary and set the summary options. Click the Next button.
  
  Type a name for the query (e.g., Contacts Mailing Address) in the What title do you want for your query? field.
  
  (Leave the Open the query to view information radio button turned on).
  
  Click the Finish button to run the query.
  
  Creating a Multiple-table Query
  Queries are not confined to just a single table. You can create a query that runs against multiple fields in multiple tables. This query is created in an identical manner to the single-table query defined on the previous page. The only difference when creating a multiple-table query is that after selecting the fields in one table, as we saw in the last example, you then select the next table and choose additional fields.
  In this query, we will ask for the name, contact type, and phone number of all records in the Contacts table. When we create the query, we will select fields from two tables: Contacts table (Last Name, First Name, and Work Phone fields) and Contacts Type table (Contact Type field).
  
  To Create a Multiple-table Query:
  
  Open the Contacts Management database.
  
  In the database window, choose the Queries tab from the Object palette.
  
  Select the Create query by using wizard options and click the Open button .
  
  The Simple Query Wizard opens.
  
  From the Tables/Queries drop-down list, choose the first table where you would like to perform the query (e.g., Contacts).
  
  From the Available Fields, select the fields to be included from this table (e.g., Last Name, First Name, and Work Phone).
  
  Creating a Multiple-table Query (continued)
  
  Select the next tables or query from the Tables/Queries drop-down list and pick the fields in that table in which you would like to perform the query.
  
  Type a name for the query (e.g., Contacts by Contact Type) in the What title do you want for your query? field.
  
  Click the Finish button to run the query.

Filtering Records of Access 2003

saifur rahman November 27, 2013 0

Filtering Records of Access 2003

Introduction

By the end of this lesson, learners should be able to:

Perform a Filter By Selection
Remove a Filter
Perform a Filter Excluding Selection
Perform a Filter By Form
Performing a Filter by Selection
At times, you might want to view only those records that match a specific criterion. A filter is a technique that lets you view and work with a subset of data. Applying a filter to an Access table, form, or query temporarily hides records that don't meet your search criteria. For example, you may only want to work with data pertaining to a specific zip code.

To Filter By Selection:
- Click anywhere in the field that you want to filter the records in the table.
- Click the Filter by Selection button in the standard toolbar or choose RecordsFilterFilter By Selection from the menu bar to apply the filtering.
- The filter produces a display that shows only those records that match the filter's definition (e.g., North Carolina). The status area reflects only the filtered records.
  Removing a Filter
  
  To Remove a Filter:
  
  Click the Remove Filter button on the standard toolbar or choose RecordsRemove Filter/Sort from the menu bar.
  
  The records revert to their ordering before the sort was applied.
  
  Optional, if you wish to reapply the filter, click the Apply Filter button (This button acts like a toggle to turn the filter on and then turn the filter off).
  
  Saving a Filter
  Access defaults to displaying all records in a table. Filters are not applied to the table initially. Filtering table records actually change the table design. When you attempt to close a table after a filter, Access will prompt you to save the changes to the table design.
  
  To save a filter:
  
  Exit the table.
  
  Click the Yes button in response to the question, Do you want to save changes to the table?
  
  The filter order is saved.
  
  When you open the table or form later, all the records will be visible. Click the Apply Filter button to reapply the filter. However, Access saves only the last filter you create.
  You can apply filters to filtered data to narrow your search even further.
  
  To cancel a filter:
  
  Exit the table
  
  Click the No button in response to the question, Do you want to save changes to the table?
  
  The change is not saved; the table remains in its original design.
  
  Performing a Filter Excluding Selection
  The Filter Excluding Selection works in the opposite manner as the Filter by Selection. Instead of specifying the filter to be used to view records (e.g., everybody in North Carolina), Filter Excluding allows you to view data that does not include the specified criterion (e.g., everybody not in North Carolina).
  
  To Apply Filter Excluding Selection:
  
  Click anywhere in the field that is to be excluded from the filter.
  
  Choose RecordFilter Excluding Selection from the menu bar or right-click and choose Filter Excluding Selection from the shortcut menu.
  
  All records except the criterion you excluded are now visible.
  
  The status area shows only the filtered records displayed on the screen.
  
  Remove this filter by clicking the Remove/Apply Filter button.

Created By SoraTemplates | Distributed By Blogger Templates