Access QBE Query by Example 1 Query Window
Access QBE Query by Example 1
Query Window l In Access 2007 – look for the “create” menu, then choose query design from the choices. l The Access 2007 window changes depending on what features you use most…you might have to hunt a little. 2
Microsoft Access Queries Choose the table(s) 3
Choose the fields l l l The top of the QBE area shows the tables and relationships The bottom grid is where you choose fields Each field goes into a separate column in the design grid, or select * as a shortcut meaning “all columns” Add tables using this icon Tables are shown at the top of the QBE designer. This is the design grid Fields are “what column(s)” 4
Specify order of columns l l You’ll recall from theory lecture that the order of columns is immaterial to a DB If you use select *, it will be difficult to predict the order in which the columns will return l l l (Some DBMS return columns in the order in which they were created – but not all) To specify columns in a certain order, instead of using select *, choose each column individually and place it in the design grid in the order you’d like to see the results (drag and drop columns from the table on top to the grid on the bottom, or double click a column name in the table). Each column name goes into a separate column in the design grid. 5
Alias l l Alias: rename or label columns You can create a temporary label for the column l In Access QBE, you put the alias first, followed by a colon, then the actual field name l So if you wanted show the column header as “patient” instead of “name” you’d do this 6
Alias l l The alias displays as the column name when you run the query Patient Nakamori, Akina This will become very useful Brown, Mike when we start creating Hall, Arsenio Sam Smith calculations and using Wade, Gerry aggregate functions Priest, Maxi nancy, nurse 7
Virtual Columns Virtual columns do not exist in the table, but can be made to exist in the query For example, In QBE grid: Choose demog table First column in design grid type “The name is” 2 nd column, pull the name attribute from the demog table View the results 8
Virtual columns Expr 1 NAME The name is Nakamori, Akina The name is Brown, Mike The name is Hall, Arsenio The name is Sam Smith The name is Wade, Gerry The name is Priest, Maxi Access automatically creates an alias (“Expr 1”) for our virtual column. 9
Concatonation l l “Paste” results together into a single field (instead of separate fields) Specific symbol varies l In Access, use “&” (most of the time) In ONE column on the grid, SELECT Name & “, ” & zip from demog 10
What if we only want certain rows? 11
Criteria l l Use the criteria area to specify what rows we want In SQL this will be the WHERE clause This interprets as …where name = “Hall, Arsenio” 12
QBE Criteria: AND versus OR l l You can have multiple criteria IN THE QBE DESIGN GRID: l If criteria for different columns are in the same criteria row, they’ll be joined using AND l l BOTH criteria must be met If the criteria for different columns are on different criteria rows, they’ll be joined using OR l EITHER criterion can be met 13
QBE Criteria: AND versus OR Cities are joined with “OR” (Glasgow or Aberdeen). Glasgow and the rent range (350 -450) are joined with AND The rent range only applies to Glasgow 14
Suppose I want data from two tables? 15
Join in pairs l l l Demog is joined to blood Demog is joined to vitals Do NOT need to join blood and vitals Demog. pt_id = vitals. pt_ID AND Demog. pt_id = blood. pt_ID 16
Microsoft Access QBE l l l Drag and drop between tables to join the keys If you’ve created and saved relationships (in the relationships window), Access will assume this is what you want for the join in your query In your query you can delete the default join, and create a different relationship. This does not change the relationships window—the join only lasts for the duration of this query. 17
Microsoft Access Queries Access has many query types – we’ll just use select queries for now and will talk about the other types later. RUN The query Query types Add tables to the query Use aggregate functions (sum, min/max, etc. ) 18
Click the design tab 19
Microsoft Access Query Types 20
- Slides: 20