Lesson_2_OUTLINE_-_queries

Lesson_2_OUTLINE_-_queries - WRONG! WRONG! MICROSOFT ACCESS...

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

View Full Document Right Arrow Icon
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
Background image of page 1

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

View Full DocumentRight Arrow Icon
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
Background image of page 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
Background image of page 3

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

View Full DocumentRight Arrow Icon
ReportKey FirmName InspecEnd City1 2000 T. E. Lott and Company 5/2/2007 Columbus 11/2/2006 Philadelphia 2004 Tamas B. Revai, CPA 10/7/2004 New York 2005 Tanner LC 9/27/2006 Salt Lake City 6/3/2005 New York 9/6/2006 Atlanta 9/1/2006 Orlando 2009 Telford Sadovnick, P.L.L.C. 8/18/2005 Bellingham 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
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/28/2011 for the course ACCT 307 taught by Professor Scrowley during the Spring '08 term at S.F. State.

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 Right Arrow Icon
Ask a homework question - tutors are online