Microsoft office access 2003
Download
1 / 31

Microsoft Office Access 2003 - PowerPoint PPT Presentation


  • 124 Views
  • Uploaded on

Microsoft Office Access 2003. Tutorial 5 – Enhancing a Table’s Design and Creating Advanced Queries. Create a Lookup Wizard field in a table. To make a form easier to use, you will often add a Lookup Wizard to the form.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Microsoft Office Access 2003' - blithe


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Microsoft office access 2003

Microsoft Office Access 2003

Tutorial 5 – Enhancing a Table’s Design and Creating Advanced Queries

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Create a lookup wizard field in a table
Create a Lookup Wizard field in a table

  • To make a form easier to use, you will often add a Lookup Wizard to the form.

  • Instead of remembering a particular value, you can pick the value from a list.

  • This approach insures that you cannot enter an invalid value.

  • When you create the Lookup Wizard, you will provide a list of acceptable values from which other users will make a selection.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Start the lookup wizard process
Start the Lookup Wizard process

  • To start the Lookup Wizard process:

    • Start Access and open the database to be modified

    • Open the desired table in Design view

    • Find the field you want to modify as a Lookup Wizard

    • Click on the right side of the Data Type text box for the field, and then click the Lookup Wizard option

    • The first Lookup Wizard dialog box will appear

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Lookup wizard dialog box 1
Lookup Wizard dialog box 1

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Lookup wizard dialog box 2
Lookup Wizard dialog box 2

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Lookup wizard dialog box 3
Lookup Wizard dialog box 3

New Perspectives on Microsoft Office Access 2003 Tutorial 5


A lookup field in datasheet view
A lookup field in Datasheet view

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Display related table records in a subdatasheet
Display related table records in a subdatasheet

  • You can form a relationship between two tables based on a common field.

  • You can display the data from the related table as a subdatasheet in the primary table's datasheet.

  • To see the subdatasheet, simply click the expand indicator in the row for which you'd like to see the subdatasheet.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Illustration of a subdatasheet
Illustration of a subdatasheet

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Create an input mask for a table field
Create an input mask for a table field

  • Some fields require special characters to make them more readable. A phone number, for example, often includes parentheses and/or dashes. You can create an input mask that will display these characters as a predefined format.

  • This predefined format can be used to enter or display data in a field.

  • You can set up literal display characters that will automatically appear in a field. When you provide literal display characters, the user will not need to enter those characters.

  • The Input Mask Wizard makes it easy for you to create an input mask.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


An input mask for a phone number field
An input mask for a phone number field

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Input mask character descriptions
Input Mask character descriptions

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Define data validation criteria
Define data validation criteria

  • You may want to view only records that match a particular criteria. You can do this by creating a query that uses a pattern match.

  • You can use the like comparison operator using the asterisk (*), the question mark (?), or the number symbol(#). These wildcard characters allow you to create much more flexible patterns.

  • You can also create a List of values match by creating a list of valid values and then using the In comparison operator to define a condition with two or more values. The non-matching values select records that do not match the criteria specified.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


A design grid with a pattern match field
A design grid with a pattern match field

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Use a list of values to expand the selection possibilities
Use a list of values to expand the selection possibilities

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Use both the and and or logical operators in the same query
Use both the And and Or logical operators in the same query

  • There will be circumstances where you will want to use both the And and Or logical operators in the same query.

  • The In operator naturally creates an Or condition. You can also use the key word Or in the criteria row to create an Or condition.

  • Placing two conditions on the same row of the Criteria in the Query design grid creates an And condition.

  • Placing two conditions on separate rows in the Query design grid creates an Or condition.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Sample design grid with and and or conditions in the same query
Sample design grid with And and Or conditions in the same query

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The query result for the combined use of and and or
The query result for the combined queryuse of And and Or

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Create a parameter query
Create a parameter query query

  • Sometimes when you create a query, you don't know exactly which records the user might want to see.

  • To allow flexibility in the query, you can create a parameter query.

  • The parameter query will prompt the user to enter the value they want to use to select records.

  • Once the user has supplied this information, those records that match the value will be displayed in the query datasheet.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Defining a parameter query
Defining a parameter query query

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Creating a crosstab query
Creating a Crosstab Query query

  • A crosstab query performs aggregate function calculations on the values of one database field and displays the results in a spreadsheet format.

  • An aggregate function performs an arithmetic operation on selected records in a database.

  • To access the crosstab query wizard, create a new query and select the Crosstab Query Wizard option from the New Query dialog box.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


Aggregate functions
Aggregate Functions query

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The crosstab query wizard dialog box 1
The Crosstab Query Wizard querydialog box 1

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The crosstab query wizard dialog box 2
The Crosstab Query Wizard querydialog box 2

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The crosstab query recordset
The Crosstab query recordset query

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The find duplicates query
The Find Duplicates Query query

  • The find duplicates query finds duplicate records in a table or query.

  • Locating duplicates helps avert potential problems (assigning two different product numbers to the same product).

  • To create a Find Duplicates Query, select the Find Duplicates Query Wizard in the New Query dialog box when you create a new query.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The find duplicates query1
The Find Duplicates Query query

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The find unmatched query
The Find Unmatched Query query

  • A find unmatched query is a select query that finds all records in a table or query that have no related records in a second table or query.

  • You can, for example, find all customers who have not placed an order.

  • To create a Find Unmatched Query, select the Find Unmatched Query Wizard in the New Query dialog box when you create a new query.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The find unmatched query dialog box
The Find Unmatched Query querydialog box

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The top values query
The Top Values Query query

  • This query allows you to limit the number of records that display as a result of a query.

    • Top Values property

  • Create a query in design view with the necessary fields and sorting. Enter the number of records (or percentage of records) you want selected in the Top Values text box on the Query Design toolbar and then click the Run button.

New Perspectives on Microsoft Office Access 2003 Tutorial 5


The top values query1
The Top Values Query query

New Perspectives on Microsoft Office Access 2003 Tutorial 5