{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Lesson_2_OUTLINE_-_queries

# Lesson_2_OUTLINE_-_queries - WRONG WRONG MICROSOFT ACCESS...

This preview shows pages 1–5. Sign up to view the full content.

Data sources Data manipulation WRONG! WRONG! MICROSOFT ACCESS Lesson 2 – Queries LESSON OBJECTIVES 1. SQL and QBE 2. Filtering basics and mathematical operations 3. The five rules of query design in Access OBJECTIVE 1 - SQL versus QBE SQL o There are three basic commands: o However, some vendors may modify the base SQL language to fit their own software QBE 1

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
OBJECTIVE 2 – Filtering basics and mathematical operations There are two basic parts to the QBE: the data sources area, and the data manipulation area: Filtering data Filtering refers to selecting records or joining records from one or multiple datasets to create a new dataset. Relational databases are built on predicate logic. We can therefore use predicate algebra (or set theory) to filter records. Two central concepts are unions and intersections. Unions – 2
A B C Intersections – Where is filtering performed? Field – Table – Sort – The following syntax and logic functions are commonly used when filtering: Syntax Description Example # # Specifies date #10/21/09# * Wildcard Like “C*” [ ] Specifies attribute >[BeginningDate] ^ Exponent (1 + [Rate])^[Periods] >= Greater than or equal to >=#2/15/2008# <= Less than or equal to <=90 <> Not equal to <>83 Logical Function Description Example Like Used to specify a text string; not needed with numbers Like “Car” Between Specifies numeric range; endpoints included Between 55 And 60 Or Connector which creates a union Like “Iowa” Or “Missouri” Or “California” And Connector which creates an intersection Like “C*” And “*a” If Access recognizes the logical function, it will automatically capitalize the first letter of each word in logical function in the criteria section of the QBE. For practice, consider the following table called tReportInfo : 3

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
ReportKey FirmName InspecEnd City1 2000 T. E. Lott and Company 5/2/2007 Columbus 2001 Tait, Weller & Baker LLP 11/2/2006 Philadelphia 2004 Tamas B. Revai, CPA 10/7/2004 New York 2005 Tanner LC 9/27/2006 Salt Lake City 2006 Tardino Tocci & Goldstein LLP 6/3/2005 New York 2007 Tauber & Balser, P.C. 9/6/2006 Atlanta 2008 Tedder, James, Worden & Associates, P.A. 9/1/2006 Orlando 2009 Telford Sadovnick, P.L.L.C. 8/18/2005 Bellingham 2010 Thigpen, Jones, Seaton & Co., P.C. 7/14/2005 Dublin Field: ReportKey FirmName InspecEnd City1 Table: tReportInfo tReportInfo tReportInfo tReportInfo Total: Group By Group By GroupBy GroupBy Sort: Show: Criteria: 2001 or: Field: ReportKey FirmName InspecEnd City1 Table: tReportInfo tReportInfo tReportInfo tReportInfo Total: Group By Group By GroupBy GroupBy Sort: Ascending Show: Criteria: >#9/1/2006# or: Field: ReportKey FirmName InspecEnd City1 Table: tReportInfo tReportInfo tReportInfo tReportInfo Total: Group By Group By GroupBy GroupBy Sort: Show: Criteria: Between "Tan*  and "Tau* " or: Field: ReportKey FirmName InspecEnd City1 Table: tReportInfo tReportInfo tReportInfo tReportInfo Total: Group By Group By GroupBy GroupBy Sort: Show: Criteria: Like "New York" Or "Atlanta" or: What would the output of the following queries be?
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 18

Lesson_2_OUTLINE_-_queries - WRONG WRONG MICROSOFT ACCESS...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online