1 / 20

Introduction to Queries

Introduction to Queries. William Klingelsmith. Reminders. MyITLab Lesson C due 10/22 Homework #4 (Nielson TV Problem) due 10/26 Last day to drop is also 10/26 Midterm grades will be computed on HWK1-3 and Excel Exam. Database Updated.

mervyn
Télécharger la présentation

Introduction to Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Queries William Klingelsmith

  2. Reminders • MyITLab Lesson C due 10/22 • Homework #4 (Nielson TV Problem) due 10/26 • Last day to drop is also 10/26 • Midterm grades will be computed on HWK1-3 and Excel Exam

  3. Database Updated • The Music database we worked on last class has been augmented with several records • Two new tables (Genres and Sales) need to be added to this database • These tables and their records are stored in external files

  4. Importing Data • To import data from external sources, you will utilize the External Data tab in Access. • There are many different import/export options, but today we will focus on XML file imports. • “XML File” in the Import & Link subgroup on the External Data Tab

  5. Importing Data • After you choose your file, you will get this dialog. • Most of the time for this class, the default ‘Structure and Data’ is appropriate.

  6. Establish Relationships • Now that we have all of our tables, we must link them together using relationships • Albums -> Songs • Albums -> Sales • Use Referential Integrity • What about the link between Genre and Albums?

  7. Lookup Fields • Lookup fields are special Data Types in tables. • Possible choices for a field in one table are determined by existing entries in another table. • They let you make a dropdown type box for a field which can aide in data entry efficiency. • They will build a relationship between two tables automatically.

  8. Lookup Fields • Move to the design view of the Albums table • Change the data type for the Genre field to Lookup Wizard • Follow the wizard’s instructions to create the lookup field. • When finished, the wizard should create a relationship between the Genre and Albums tables.

  9. Queries • Queries are used to view a subset of the data contained in your database • These subsets are most commonly broken down by a limiting criteria • “Return only the songs that are on albums by a particular artist” • “List all albums of a certain genre” • Think of QUEries as QUEstions!

  10. Constructing a Query • Let’s start our query lesson with a simple example • We wish to list every Artist and Song name • Where is this information stored?

  11. Constructing a Query • Move to the Create tab and choose Query Design • The Show Table dialog lets you add relevant tables to your query • Choose only Songs for this query

  12. Constructing a Query • Field: Name of the field • Table: Table the field belongs to • Sort: Sorting the results in Ascending/Descending order • Show: Hide/Display the result in the query • Criteria and Or: Allows you to narrow filter results of the query on some criteria

  13. Running a Query • Queries can be executed in two ways • The Run button in the design view of a query • Switching to the Datasheet view of a query

  14. Adding Criteria • Save the last query as Query A • Duplicate it in the object pane • Call the copy Query B • Open the design view of Query B • For this query, we wish to only return the songs performed by the artist ‘Venetian Snares’ • In the criteria row under the Artist field type “Venetian Snares” • Run the query

  15. Criteria Notes • Criteria across multiple fields on the same line are all used at once (AND) • Multiple criteria under the same field are all acceptable choices (OR) • Conditional operators (<, >, <=, >=, etc) can be used in criteria • Access has two wildcard operators (? And *)

  16. Criteria Examples (OR) • Duplicate Query B and call it Query C • On the row below where “Venetian Snares” currently resides, type “Fleet Foxes” • Notice that songs from both artists are now returned in the result • Criteria is OR’d • It is saying that results can be either from Venetian Snares OR Fleet Foxes

  17. Criteria Examples (AND) • Duplicate the last query and call it Query D • Add the Favorite field from the Songs table to your query design • Criteria should be as follows: • Artist: Venetian Snares • Favorite: Yes • We have fewer results than before • Criteria across multiple fields on the same row are AND’d • “Songs must both be by Venetian Snares and a favorite to be included in the query”

  18. Criteria Examples (Conditional Operators) • Let’s try to get a list of all albums released before the year 2000 • Create a new query and add the Songs and Albums tables • Populate the query with the fields: Artist, SongName, AlbumName, Genre, ReleaseDate • In the criteria under the release date enter: “<1/1/2000”

  19. Criteria Examples (Wildcards) • Wildcards are operators that can be substituted for a set of characters • * Operator allows any set of characters of any length • Let’s say we had a field that held first names. By using S* in the criteria, we could get these results from our query • Sara • Stephen • Sue • ? Operator allows any set of characters of length 1 • By using ?ill we could get results such as: • Will • Bill • Jill

  20. Criteria Examples (Wildcards) • Return only Albums that begin with the letter T

More Related