Computer Business Applications



Computer Business Applications Instructor: Greg Shaw

CGS 2100

Microsoft Access - Tutorial 5, Session 5.1

“Creating Advanced Queries and Enhancing Table Design”

❑ Object and Field Naming Standards

• The names of database objects traditionally begin with a prefix that makes it easy to identify – at a glance – the type of the object

• Table names begin with prefix tbl

← E.g. the Contract table from the Belmont database has been renamed tblContract in the Panorama database

• Query names begin with qry, form names with frm, and report names with rpt

• Although Access allows spaces in object and field names, some other database management systems – such as Oracle and SQL Server – do not

• In the interest of compatibility, then, spaces should not be used in object and field names

← E.g. the Contract Num field of the Contract table from the Belmont database has been renamed ContractNum in the Panorama database

❑ Setting the Field Caption Property (Table Design View)

• The caption property for a field determines what will appear in column headings in tables and queries and in forms and reports

• E.g. for the field ContractNum, we would set the caption property to Contract Num so that the latter would appear in column headings

• If the caption property is not set, the field name appears

❑ Using Zoom

The Zoom dialog box is just a jumbo-sized, easy-to-see input box where we enter query conditions, validation rules and text, actually anything that we would otherwise enter into one of those little, cramped text boxes in Design View

1. Right-click in the text box for the property you wish to change, and choose Zoom...

2. Optionally, click the Font... button and choose a larger font size to improve visibility

3. Type and edit in the Zoom window and click OK

4. Your entry will appear in the text box for that property

← The following query examples from Tutorial 5 are based on the related tblCustomer and tblContract tables of the Panorama database

❑ Using a Pattern Match in a Query

• We use the Like operator and wildcard operators to create a pattern

• E.g. Like “616*”

When applied to the Phone field, this will select all customers whose phone number begins with 616 (i.e. is in area code 616)

← Recall that there are 2 “wild card” operators in Access (and Excel). The asterisk (“*”) stands for any number of characters, including none, and the question mark (“?”) stands for any single character

❑ Using a List-of-Values Match in a Query

• We use the In operator with a list of values in parentheses and separated by commas

• E.g. In (“Holland”,“Rockford”,“Saugatuck”)

When applied to the City field, this will select all customers located in any one of those 3 cities

❑ Using the Not Operator in a Query

• The Not operator is a logical operator (aka: “boolean” operator), like And and Or

• Logical operators “operate on” conditions, forming more complex conditions

• When a condition is preceded by the Not operator, the opposite condition is created. In other words, only those records that do not meet the condition will be selected

• E.g Not In (“Holland”,“Rockford”,“Saugatuck”)

When applied to the City field, this will select all customers located in any city other than one of those three

← Using AutoFilter to Filter Data (same as in Excel)

Example: What is the customer and contract information for contracts that are less than $10,000 or were signed during the winter and are located in Grand Rapids or East Grand Rapids?

1. Create a query with ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download