Tip:
Highlight text to annotate it
X
Hello; this video will walk you through the Tutorial five project of the New Perspectives
Microsoft Access 2010 textbook. This video is provided as a supplement to the assigned
reading for Mt. San Jacinto College's CAPP 123: Using Microsoft Access course facilitated
by Anna Stirling.
It is important to note that not every section of the tutorial is included in this video.
It is important that you read all sections of the tutorial from the textbook to ensure
you fully understand how to complete all of the tasks for this tutorial.
To start this project we will be working from a new data file, not the tutorial four file.
To open the Panorama database, navigate to where you have your data files stored, double-click
on the Access Tutorials 5-8 folder, then double-click the Access2 folder, next open the Tutorial
folder, and finally open the Panorama database.
I recommend saving the database with a different file name at this point so you will have a
"clean" database if you need to start over for any reason.
To save the database, click on the file tab and choose Save Database As. Navigate to where
you want to save the file and click Save. Now you can work in the "new" database without
worrying about the original file.
Using a Pattern Match in a Query
We already know how to create queries that use a range of values or an exact match to
select and view records, but what if we want information that isn't that easy to locate?
No problem, Access can do that too. To start we are going to look for a pattern in our
data and return all the records that have that pattern; specifically the 616 area code.
Click on the create tab, then choose Query Design from the Queries group.
From the Show Table dialog box, add the tblCustomer table and then close the dialog box.
Double-click the title of the table box to select all of the fields, and then drag them
to the Field box in the design grid. When you let go of the mouse button all of the
fields are included in the query.
Click the Criteria box under the Phone field and type the letter L. Ignore the formula
AutoComplete menu and finish typing ike. Add 616 start with quotation marks around it.
The star and quotes are important, don't omit them.
Save the query as qry616AreaCode, and run the query. You should see 24 records. If not,
make sure you included the star and the quotes, then run your query again.
Before we move on, make sure you change First and Last to your first and last name in record
11004. Save and close the query.
Using a List-of-Values Match in a Query
Next we are going to create a query using the In comparison operator. This allows you
to define a condition with a list of two or more values for a field. If a record's value
matches any value from the list, the record is displayed in your results. For example,
in this query we want to see customers who live in Holland, Rockford, or Saugatuck.
To begin create a copy of the 616AreaCode query. You can do this from the backstage
view or by right-clicking and choosing copy. You can do it whichever way you prefer.
When you paste it, change the name to qryHollandRockfordSaugatuckCustomers, I know, the longest name ever for a query.
Open the query and change to design view.
Delete the existing area code criteria; we don't need that for this query.
Right-click the criteria box under City, and choose Zoom from the shortcut menu. When the
Zoom dialog box opens, type: In ("Holland","Rockford","Saugatuck") and press OK. Save and run the query.
Your query should return 13 records. If not, go back and check your typing in the Zoom
box. Remember, everything must be exact - a typo here will cause it not to work.
Using the Not Logical Operator in a Query
This type of query returns values that do not meet your criteria. In this query we are
going to return customers who do not live in Holland, Rockford, or Saugatuck.
Create a copy of the HollandRockfordSaugatuck query and rename it NonHollandRockfordSaugatuck.
Open the query and switch to design view.
Open the Zoom box and add the word Not before In. Click OK. Save and run the query. 29 records
are returned, and none of them list Holland, Rockford, or Saugatuck as the city. It really
is that simple.
Assigning a Conditional Value to a Calculated Field
Make sure you read the information about the ampersand operator, the IIF function and the
IsNull function in the textbook on pages AC228 and AC229. Understanding how they work will
be critical for you to understand how to create the necessary expression in the case problem.
To create the query, click the create tab and choose Query Design. Add the Customer
table and close the Show Table dialog box.
We are going to add the expression in the first column, but we also want to include
the fields from the Customer table, so double click the title of the table box to select
all of the fields and drag them to the second field box in the design grid.
Right-click the first field box and choose Build. This opens the Expression Builder dialog
box. The name of the calculated field is going to be Customer, so type Customer colon space.
Double-click Functions in the Expression Elements column, then choose Built-In Functions. In
the Expressions Categories column, scroll until you find Program Flow and select it.
Now, double-click on IIF in the Expression Values column. This inserts the placeholders
for the IIF function.
We don't need the field because we already typed customer, so you can delete
it. Next we need the IsNull function, click on then click on Inspection
from the Expression Categories column. Next, double-click IsNull in the Expression Values
column to insert it into the expression.
Click on and type Company. Click on and type Company again.
Click on and type LastName space ampersand space quotation mark comma space
quotation mark space ampersand space FirstName. Make sure you type it exactly or your expression
will not work! Click OK to close the Expression Builder.
The expression now appears in the field box. If I expand the column size you can see the
whole expression. Sort the results by this column in ascending order.
Save the query as qryCustomersByName. Run the query. Resize the column so you can read
the values in the new column. Save and close the query.
Creating a Parameter Query
A parameter query displays a dialog box that prompts the user to enter one or more criteria
values to run the query. For this query, the database user will enter the city parameter.
To begin, create a copy of the CustomersByName query and rename it qryCustomersByNameParameter.
I know, another long query name.
Open the query and switch to design view. In the City Criteria box type [Type the city:]
make sure you include the square brackets, this tells Access this is a parameter request.
This will be the information the user will see when they attempt to run the query. Save
and run the query.
When the Enter Parameter Value dialog box opens, type Holland in the box and click OK.
Seven records, all with Holland as the city, display.
If you do not enter anything in the parameter box, Access will return no results. Read pages
AC235 through AC 237 to learn more about this and how to modify your query if you want all
the records to display if no value is entered in the parameter box.
Creating a Crosstab Query
Crosstab queries use aggregate functions to perform arithmetic operations on selected
records. To see a list of aggregate functions see figure 5-17 on page AC240.
We are going to use the Crosstab Query Wizard to build our query. So, go to the Create tab
and click on Query Wizard from the Queries group. From the New Query dialog box choose
Crosstab Query Wizard and click OK.
From the next screen choose Queries from the View options and then choose Query: qryCustomersAndInvoice
from the list and click Next.
In the Available fields box, click City and add it to the selected fields list using the
arrow. Click Next.
Choose InvoicePaid from the available field values and click Next.
Choose InvoiceAmt from the Fields list, and then choose Sum from the Functions list. Make
sure the box next to Yes, include row sums is checked and then click next.
Delete the underscore from the query name to follow the Belmont naming convention, and
click Finish.
Each city's records now display, but the column headings don't make any sense as negative
1 and zero. So, we're going to change the headings so they are easy to read.
Switch to design view. Right click on the [InvoicePaid] field box and choose Zoom. Delete
the contents of the zoom box and type: IIf (InvoicePaid,"Paid","Unpaid") and then click
OK.
Now, when you run the query, the headings read Paid and Unpaid. Close and save the query.
This process of renaming the headings is going to be important for you case problem, so make
sure you know how to complete it!
Creating a Find Duplicates Query
This query, as its name eludes to, finds duplicate values for a specified field.
To create this query click on Query Wizard from the Queries group on the Create tab.
Choose Find Duplicates Query Wizard from the dialog box and click OK.
Choose table: TblContract from the list and click Next.
Choose StartDate from the Available fields list and move it to the Duplicate-value fields
list and then click Next.
From the next window add all of the available fields to the Additional query fields list
and then click Next.
Change the name of the query to qryDuplicateContractStartDate and click Finish.
Creating a Lookup Field
A lookup field lets the user select a value from a list of possible values to reduce data
entry errors. In this example we will change the CustomerID field in the tblContract table
to a lookup field.
To begin open the tblContract table in design view. Click the left side of the CustomerID
Data Type and choose Lookup Wizard from the drop-down menu.
Oh no! We can't change this data type because it is part of a relationship. Click OK in
the Warning box, close the table, and then click no when asked to save the changes.
We have to delete the existing relationship so we can change the data type. Go to the
Database Tools tab and click on Relationships. Right-click on the relationship line between
the tblCustomers and tblContract tables and select delete. Click Yes to delete the relationship.
Close the relationship window.
Re-open the tblContract table in design view. Click the left side of the CustomerID data
type box; are you having a little deja vu? Me too. Choose Lookup Wizard. This time, however,
the wizard opens.
Ensure that the "I want the lookup field to get the values from another table or query"
option is selected and click Next.
Choose Queries from the view options, select Query: qryCustomerByName and then click Next.
Add Customer and CustomerID to the Selected Fields box and click Next.
Click the arrow in the one box and choose Customer then click next.
Resize the fields to best fit, and then click Next.
Click CustomerID and then click Next. Click Finish.
Now, when you click on the CustomerID field for any record, there is an option arrow on
the left. Click on it to see the available options.
Using the Input Mask Wizard
An input mask allows you to set the way data will appear in a field, no matter how it is
entered. For this tutorial we will add an input mask to the phone number field to display
all the phone numbers with hyphens.
To begin, open the tblCustomer table in design view. Click the Phone field box. Click in
the Input Mask box in the Field Properties area and then click on the ... from the right
side to open the Input Mask Wizard.
The first input mask is the Phone Number format, this is what we want, so make sure it is selected
and then click Next. Click Finish to accept the remaining defaults of the wizard.
Notice that Access adds backslash characters to the input mask. Review the list of Input
Mask Characters on page AC260 to understand why these characters may or may not be important.
Change the input mask to read 999\-000\0000;;_ which will remove the parentheses, and press
tab. Notice the Property Update Options button appears, the little lightning bolt. Click
on it and choose, Update Input Mask everywhere Phone is used. When the Update Properties
box opens click Yes.
Save the table and switch to Datasheet view to see the input mask applied to the phone
field.
Defining Data Validation Rules
To prevent a user from entering an unacceptable value to a field you can apply a validation
rule. For this tutorial we will apply a validation rule to ensure that only an amount over ten
dollars is entered into the Invoice amount field.
To begin, open the tblInvoice table in design view. Click the InvoiceAmt field, and in the
Field Properties area click in the Validation Rule box. Type >10, and press tab. In the
Validation Text box type, Invoice amounts must be greater than 10. Save the table, click
yes in the warning dialog box, and switch to datasheet view.
To test the validation rule, change the Invoice Amt of the first record to 5 and press enter.
A dialog box appears with our warning message: Invoice amounts must be greater than 10. Click
OK, and change the value back to 1500.
Remember, not every section of the tutorial is included in this video. It is important
that you read all sections of the tutorial from the textbook to ensure you fully understand
how to complete all of the tasks for this tutorial.