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 <<Expr>> field because we
already typed customer, so you can delete it. Next we need the IsNull function, click on
<<expression>> 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 <<expression>> and type Company. Click on <<falsepart>> and type Company again. Click on <<truepart>> 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.
