Week 4: July 21-25

TUES   THURS   TO DO

WEEK'S OBJECTIVE

MS Access 2013 Queries. Book: Lesson 3. On-Your-Own Tasks: Queries 1, Queries 2.

MIC ASSIGN 4, due Sunday July 27.

Tuesday

GAMEPLAN

  • Queries
  • Query Wizards
  • Query Creation
  • One-table queries
  • 2-table queries

DISCUSSION

BEFORE Thursday, DO: Microsoft Lessons

Queries

Database queries are just questions to the database. It is used as the way of searching for and retriving information from the database  You use a query to ask the database to provide specific information from one or more tables. Queries come out looking just like Tables - they are simply new tables based on specific information you have culled from the database. Queries can be used to:

  • Filter columns from a table.
  • Combine information from one or more columns from more than one table.
  • Filter specific records.
  • Update data.
  • Add data.
  • Delete data.
  • Make calculations.
  • You will see the Query results in a NEW Table, listed under the Queries Object area in the Navigation Pane.
Query Wizards

You can create Queries from scratch, but Access has a Query Wizard that lets you create several basic query types.

  • Simple: A Simple/ Select Query asks a question about the data in a database and returns the results in a new Datasheet Table - without in any way changing the existing data.
  • Crosstab: This query type is another kind of Select Query, but the resulting Datasheet Table looks different. It groups the data both horizontally and vertically so that the datasheet can be more compact and easier to read than a 'flat' Select Query result. A crosstab query calculates a sum, average, or other aggregate function, and then groups the results by two sets of values— one down the side of the datasheet and the other across the top.
  • Find Duplicates: This query type allows you to find and hide or eliminate duplicate data in a database. It CAN change data in your database. Before you take action to identify and delete duplicate records, remember that you need to rely on your knowledge of the data. 
  • Find Unmatched: This allows you to compare two tables and identify records in one of the tables that have no corresponding records in the other table. Why do this? To find customers who have never placed an order, so you can create an archived customers table.
Query Design

If you want to create a query from scratch, you could use the Query Design in the CREATE Tab.

  • You would use this to create less common queries. In this class we are sticking with the wizards for Query Creation.
  • You can also use this to edit existing queries.
Query Preparation

Queries offer the ability to retrieve and filter data, calculate summaries (totals), and update, move and delete records in bulk.

  • You need to understand exactly WHAT you want to get from a Query in order to ask the database for the right information from the right tables.
  • Consider: What do you want the database to tell you, and why?
  • Consider: Does the database have the information in the first place?
  • Consider: Does the database have the information in tables that have a relationship with each other?
  • Consider: HOW do you ask for the correct columns in the related tables so that you see what you need after running the query?
  • Multiple Table/Rentaional Queries really only work when you HAVE and CHOOSE columns with a viable relationship and Primary/Foreign keys between them.
What's the point?
  • Customers and Employees: Which customer was assisted by what employee?
    • In one table, easy. But that table would also have the order info, the product(s), the product info, all the orders from the same customer, all the products by each employee, all the prices, all the taxes, shipping, receiving, invoicing, returns, comments, etc.
  • That table would also list same customer but address variations, name variations, misspellings, changed logins and passwords, changed credit card info, etc.
  • UGH!
What to do?

You need customer, employee, banking info, product info, order info, etc.

  • HOW is a single customer related to a single employee in the Gardening database, for example?
    • Through the order.
  • HOW does the order table link them?
    • By the order. The order, the customer, the employee.
  • But the customer, employee, and order are separate tables.
    • Build the tables so that something in the customer table, and something in the employee table, are used/referred to in the order table.
  • Then, ask the question that pulls that info from all three tables and displays in one small table. Presto!!
Query Problems

You can get all sorts of useless information from queries when you do NOT know what you need the database to provide with you. This can happen in several ways:

  • Lack of Primary/Foreign keys between tables
  • Incorrect relationships between the needed tables
  • Incorrect relationship types between tables (one-to-one, one-to-many) etc.
  • Asking for information from the wrong tables.
  • Asking for the wrong informations from the right tables.
Query Logic

As a database user, you might simply run basic pre-created queries. However, to understand and create complex queries in Access and other database languages (SQL. MySQL, Orable, etc.), you need to understand basic Query Logic and then apply it to asking the database the right questions. This is the Jump between standard end user and advanced user/database creator. You need to know:

  • What to ask for.
  • How you want the information to appear.
  • Which tables to choose from that have the information.
  • Ensure that the tables have a defined relationship in the Access Relationships window.
  • If the tables do NOT have a defined relationship, you need to create one.
Query Creation: CREATE tab/Query Wizard
  • Have your tables opened and in Datasheet View.
  • Choose Create/Query Wizard.
  • Choose type of query, such as Simple
  • Follow the wizard steps.
  • To restrict the records that are returned in the query results, you can specify one or more criteria.
  • Your results will open into a new combined info table.
  • Save/Name the query table to revisit later.
One-table queries

You can filter out specific data in a single table easily with a Simple Query. Sometimes you just want to see a group of customers in a date range and a geographical region, and basic Filtering won't do the trick.

  • Use CREATE Tab/Query Wizard/Simple Query.
  • Choose the table.
  • Choose the columns to "keep".
  • Name the query.
  • Choose Open query to view info
  • See the NEW query table, and sort/filter it further as desired.
Two-table / Multiple-table queries

To work with records from more than one table, you need to create a query that joins the tables. The query works by matching the values in the primary key field of the first table with a foreign key field in the second table.

  • Check the Tables you think you need in DATABASE TOOLS Tab/Relationships. See the relationships, and consider the columns you will need.
  • Use CREATE Tab/Query Wizard/Simple Query.
  • Choose the table to start with.
  • Choose the needed columns.
  • Choose the second needed table.
  • Choose the needed columns.
  • Name the query.
  • Choose Open query to view info
  • See the NEW query table, and sort/filter it further as desired.
Crosstab Query

This query type is another kind of Select Query, but the resulting Datasheet Table can look different. It groups the data both horizontally and vertically so that the datasheet can be more compact to read than a 'flat' Select Query result.

  • Use the Query Wizard, but choose Crosstab Query.
  • This requires only ONE to be referenced.
  • If you want to use a Crosstab for more than one table, you need to create a Simple query from those table first, then run a Crosstab on the resulting Simple Query.
  • Here is a link to an example.

DEMO

Let's play with a couple of simple queries.
  • Open GardenCompanyQueries.accdb database.
  • Single Table Query: How do we see a list of customers and the cities they are from?
    • Use CREATE Tab, Query Wizard, SIMPLE Query Wizard
    • Choose Table: Customers
    • Choose FirstName, LastName, and City.
    • Choose Open query to view info
  • NOW, let's Modify in DESIGN View
    • On HOME tab, choose Query Design
    • In [City] Customers/Criteria, type "Seattle" then run Query by clicking Red Exclamation Point icon.

Two Table Query: How do we find out what the Order Details items are that were actually ordered?

  • Check relationships. There IS a Products ONE to Order Details MANY.
  • Now, what do we want to know? Order ID, and what item was actually ordered - in a way we can understand it.
  • Use CREATE Tab, Query Wizard, SIMPLE Query Wizard
  • Choose Table: Order Details, Order ID
  • Choose Table: Products, ProductName
  • Choose Open query to view info
  • Now, let's see it in DESIGN View.

How about a Three-Table Query? While the 2-table query is cool, how about WHO ordered what product, what is the order number, and what is the product called? Hmmm.

  • Relationships: Order Details, Products, Customers. Hmmm. Nope, because there is NO reference to Customers in the other two tables. Also need to look at Orders table to verify there can be some relationship.
  • Use CREATE Tab, Query Wizard, SIMPLE Query Wizard
  • Choose Table: Orders, Customer ID
  • Choose Table: Orders, OrderID
  • Choose Table: Order Details, Product ID
  • Choose Open query to view info

WAIT, that doesn't look useful. . . Let's try this instead. . .

  • Choose Table: Customers, LastName = (Customers) LastName
  • Choose Table: Order Details, OrderID, Products = (Order Details) OrderID
  • Choose Table: Products, Product Name = (Products) Product Name
  • Choose Open query to view info
  • Now, let's see it in DESIGN View.

Crosstab Query: These can be tricky and are really useful if you need to see a calculation.

  • Use CREATE Tab, Query Wizard, CROSSTAB Query Wizard
  • Choose Table: Orders, Next
  • For Row heading, Choose Order ID, Next
  • For Column heading, Choose Shipped Date, Next
  • For Interval (dates only), Choose Month, Next
  • For Number calculating, Choose Freight, Sum, AND make sure there is a Check in the "Yes, Include Row Sums" box, Next
  • Name the Crosstab Query: Freight by Month CTQuery
  • Choose View the Query, Finish to open the query results and view info
BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Thursday

GAMEPLAN

  • Queries, complex
  • Find Duplicates
  • Calculate
  • Append
  • Delete

DISCUSSION

Queries, Complex

We have already seen how you can just look up info on Queries. You can actually do more:

  • Find duplicate records
  • Calculate information
  • Append data from one table to the end of another table
  • Delete data from the database.
  • Crosstab Queries (View, different layout)
  • In this class, we are surveying the types of basic queries; you will create and use more complex types in the workplace and if you become a database programmer.
Query Design

Some complex queries can be done through the Query Wizard, even if you need to create one query, then use the Wizard in a different way on that first Query result. However, other queries need to be hand built, using the Query Design tool.

  • The Design tool is drag-and-drop.
  • You can change the order of your query columns in the Design View.
  • You can Run the query from the Design View with the Red Exclamation Point Icon.
Find Duplicate Records

This query type allows you to find duplicate data in a database.

  • This query is designed to find duplicates in ONLY one table.
  • You do not need to choose all columns in the table to find duplicate records.
  • You may get no results at all, if there are no duplicates.
  • If you get duplicate results, you can then use the resulting query to help you pinpoint them and decide how to handle the duplication (or verify the original data truly IS a duplication.)
  • Steps, using the Customers Table in GardenCompanyQueries.accdb database.
    • Open Customers table
    • Let's accidentally ADD a duplicate row
    • Use CREATE Tab, Query Wizard
    • Choose Find Duplicates Query Wizard
    • Choose Table: Customers, all fields
    • Choose Finish
    • You will see an empty table with no duplicates. This is because you told access to choose ALL the fields. Close query without saving
    • Use CREATE Tab, Query Wizard
    • Choose Find Duplicates Query Wizard
    • Choose Table: Customers, SupplierName, Address, and PostalCode
    • Choose Finish
    • Now you can see the suppliers that have duplicate rows.
Calculate Information - Expression Builder (from 2010 version, still useful)

In a database, you should never store information that can be calculated from existing data, such as subtotals or average quantities listed in a table. Instead, you want the core data available to make the calculations, and you can use a Query/Expression Builder to create a new result when needed.

  • This kind of query does NOT have a Query Wizard starting point.
  • You must use the Query Design view.
  • You would add a new Query Results table column in order to show the calculated result.
  • In this new Query Results column, you need to Build an Expression - i.e. a calculation of the fields in the original tables. This requires several steps and is more advanced - study on your own.
  • You also may need to specify in your calculation which table the field comes from - if that field is in more than one table.
Summarize Data Query

You can do a basic calculation with a simple query to summarize data:

  • Steps, using the OrdersDetail table in GardenCompanyQueries.accdb database.
    • Open OrderDetails table to see it
    • Use CREATE Tab, Query Designer
    • Add OrderDetails table, then choose OrderID and UnitPrice, Next
    • Choose Summary, then Choose Summary Options Button
    • Choose the Sum checkbox, Okay, Next
    • Choose Open Query to view information, Finish.
    • Note that the resulting table shows each OrderID only once, with the sum total UnitPrice for EACH OrderID.
    • If you open and look at the original OrdersDetail table, you will still see each UnitPrice for the OrderIDs broken down by ProductID.
Action Queries

Action Queries are queries that actually change the information in your database. Before you plan and create an action query, remember that you need to rely on your knowledge of the data, and that deleted data cannot be recovered. Action Queries include:

  • Append: These add records from one or more tables to the end of one or more
    other tables.
  • Delete: These delete records from one or more tables. You CANNOT recover deleted records.
  • Make-table: These create a new table from all or part of the data in one or more
    tables.
  • Update: These make changes to records in one or more tables.
  • You can’t create an action query directly; you must first create a select query and then convert it.
  • You can do this through choosing the icon for the type of Action Query you want in the Design View context ribbon Query Type.
Append (Updated Steps)

An Append action query adds records from one or more tables to the end of one or more other tables. Steps, using the Suppliers table in GardenCompanyQueries.accdb database where we'll Append the rows from the SuppliersToAdd table:

  • Open Suppliers table to see it. NOTE that it has a Numbered SupplierID column, with 21 suppliers.
  • Open SuppliersToAdd table to see it. NOTE that it has a Numbered SupplierID column, with suppliers numbered 22-25.
  • Look at both tables in Design view to verify that the data format for the SupplierID columns are the same.
  • Close both tables.
  • Use CREATE Tab, Query Designer
  • Add the SuppliersToAdd table, then double-click the Star to select all fields.
  • Use the Query Tools DESIGN contextual tab, and choose the Append button
  • Choose the Suppliers table to append to.
  • In the Query Tools DESIGN contextual tab, Click the Red Exclamation Point icon to run the query.
  • Close and Save the query as Suppliers to Add Query.
  • Open the Suppliers table in Datasheet View and view to see what was added.
  • Note: If you left the Suppliers table open when you created and ran the Append Query, you will likely have to close the Suppliers table, then re-open it, to see the changes.
Delete (Updated Steps)

A Delete action query deletes records from one or more tables. You CANNOT recover deleted records. Steps, using the Suppliers table in GardenCompanyQueries.accdb database to delete a few Customers in Idaho:

  • Open Customers table to see it
  • Use CREATE Tab, Query Designer
  • Add Customers table, then choose CustomerID and State
  • Use DESIGN contextual tab, choose Delete button
  • Under State, in the Criteria, type "ID"
  • In the Query Tools DESIGN contextual tab, Click the Red Exclamation Point icon to run the query.
  • Choose Yes at the warning
  • Click Datasheet View to see the Customers table, and see if there are any Customers from ID in it.
  • Guess what - you WILL! This table will not allow you to DELETE records, because it has relationships with more other tables. Deleting customers causes conflicts in the database, and would affect the integrity of any orders that had been made for those customers. You cannot do so.
    • DO NOT try to delete table relationships to make this work - you will also affect the integrity of related tables and any existing queries.
    • Also, DO NOT try to go into your tables in Relationship View and try to change the Relationships between them to allow for Cascade Delete, since you will received conflict messages.
  • You may see "Deleted" in various fields - this is because the Query is still open. If you close that, then these records disappear altogether.

So, how DO we get a working Delete Query? First, you need to have a table that does not have relationships with other tables that those tables rely upon. In THIS database, that would be the SuppliesToAdd table.

  • Open the SuppliesToAdd table to see it. Leave it open.
  • Use CREATE Tab, Query Designer
  • Add SuppliesToAdd table, then choose SupplierID and PostalCode
  • Use DESIGN contextual tab, choose Delete button
  • Under PostalCode, in the Criteria, type "80019"
  • In the Query Tools DESIGN contextual tab, Click the Red Exclamation Point icon to run the query.
  • Choose Yes at the warning
  • Click Datasheet View to see the SuppliesToAdd table, and see if there are any Suppliers with the zip code 80019 in it.
  • You might not see any change - try to close the SuppliesToAdd table.
  • When you do, you will get an error message, plus you may see the row with the 80019 suddenly fill up with the word Delete. Click YES at the error message to cloase the table.
  • Re-open the table, and you will see that the supplier with the zip code 80019 was deleted.

Update (Updated Steps)

An Update action query makes changes to records in one or more tables. Steps, using the Orders table in GardenCompanyQueries.accdb database, where we'll change the Freight prices by $2. First, we create a simple query to get just the Freight prices, then we will run the Update query on that Freight Prices query results table.

  • Use CREATE Tab, Query Wizard
  • Choose Simple Query Wizard
  • Choose Table: Orders, Freight
  • Choose Finish. You will get a 1-column results table called Orders Query listing only the Freight charges.
  • Use the HOME Tab, and choose Query Design.
  • In the Query Tools DESIGN contextual tab, Click the Update icon.
  • In [Freight], click Update To and type [Freight]+2.00
  • In the Query Tools DESIGN contextual tab, Click the Red Exclamation Point icon to run the query.
  • Choose Yes at the message noting that you will be updating records.
  • Look at the Orders Query table, where the Freight will have added $2.00 to each record. Close this table.
  • Look at the original Orders table, where the Freight will also show the added $2.00 to each record. Close this table.

DEMO

Let's play with several queries (see above).
BACK TO TOP

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

TO DO

Resources
BACK TO TOP