Washington University of Virginia Management Information System Project

ACCESS TUTORIAL
LAB06 QUERIES THAT
SELECT RECORDS
SOURCE: ACCESS 2010: THE MISSING MANUAL, MATTHEW
MACDONALD, ISBN: 9781449382377
INSTRUCTOR: ABRAHAM KIM, 571-353-5944, aykim@wuv.edu
Queries That Select Records (Lab06)
Query Basics
Queries:
• Queries are readymade search routines that we store in our database, with various
sorting and filtering options.
• Queries can combine related tables – Allow search and display related data from
the related tables.
• Queries can perform calculations – Allow calculations from the data in the tables.
• Queries can perform summaries – Allow grouping big data into a summary.
• Queries can automatically apply changes – Apply the entire batch of changes in the
data in one step.
Queries That Select Records (Lab06)
Creating Queries
Creating a Query in Design View
1.
2.
3.
4.
5.
6.
7.
Open “Lab06 BoutiqueFudge.mdb” database from the lab directory.
Choose Create / Query Design. Select Orders table / Add / Close.
In the Fields at the bottom, select the fields such as ID, DatePlaced, and CustomerID to include in the
query.
Choose Sort in Descending order for DatePlaced.
Set a filtering Criteria such as >=#1/1/2010# And ”.
Click Next / Next, and provide a Query title, “Products Query”.
Click Finish and check the result of the created Query.
Queries That Select Records (Lab06)
Understanding the SQL View
Displaying SQL View
1.
2.
3.
With “Lab06 BoutiqueFudge.mdb” open, click the Dropdown menu on left file navigation pane, and select
“All Access Objects.” Open “TopProducts” Query.
Click Home / Views menu / SQL View, or click SQL at the bottom right corner. Review the SQL commands.
Change SQL WHERE command to (((Products.Price)>100))) and go to Datasheet View to check what has
been changed. Check this change in the Design View also.
Creating a Union Query
(Two table contents can be displayed together if both have the same data structure.)
1.
2.
3.
Choose Create / Queries Design, and add “Customers” and “Employees” tables. Go to SQL View by
clicking Home / Views menu / SQL View. Copy & Paste the following SQL commands over the given SQL
View. Union query merges the results from more than one table. Save the SQL to “Query1” as file name.
SELECT Customers.FirstName, Customers.LastName
FROM Customers
UNION
SELECT Employees.FirstName, Employees.LastName
FROM Employees
Check both Customers and Employees are in the Datasheet View.
Observe there is no Design View available for this case.
Queries That Select Records (Lab06)
Queries and Related Tables
Joining Tables in a Query
(A join is a query operation that pulls columns from two tables and fuses them together in one grid of results. Join
has inner join and outer join. Inner join shows only linked records that appear in both tables.)
1. Open “Lab06 BoutiqueFudge.mdb” database.
2. Choose Create / Query Design / Select “Customers” and “Orders” tables / Add / Close.
3. Check two tables are joined already from the established relationship between two tables.
4. Generate a Query, “CustomersOrders,” the same way as below and review it in the Datasheet View. You
will notice that the customers who didn’t make any order are not showing in the result.
SELECT Customers.FirstName, Customers.LastName,
Orders.ID, Orders.DatePlaced
FROM Customers INNER JOIN Orders ON Customers.ID =
Orders.CustomerID;
Queries That Select Records (Lab06)
Queries and Related Tables
Outer-Joining Tables in a Query
(Outer-join includes all the same inner-join results, plus the leftover unlinked records from one of the two tables.)
1. Go to Design View of the previous Query, “CustomersOrders”.
2. Right-click on the join between Customers and Orders tables and select Join Properties.
3. Select two other joining options and review Datasheet View for the results of Outer Join.
Left Join
Right Join
SELECT Customers.FirstName, Customers.LastName,
Orders.ID, Orders.DatePlaced
FROM Customers RIGHT JOIN Orders ON Customers.ID =
Orders.CustomerID;
Queries That Select Records (Lab06)
Lab06 Homework
Multiple Joins
Multiple joins are simply ways of bringing more related information into a query.
1. Open “Lab06 BoutiqueFudge.mdb” database.
2. Using tables OrderStatus, Orders, OrderDetails, and Products, create a new query, “Returned Products” as
shown below.
3. Save the working file and continue to next slide.
Queries That Select Records (Lab06)
Lab06 Homework
Multiple Joins
Multiple joins are simply ways of bringing more related information into a query.
4. On the “Lab06 BoutiqueFudge.mdb” database, create a query called, “OrderSuppliers” that dispays
OrderID, ProductName, SupplierName, and Location. (You need to find the related tables from
relationships diagram by clicking Database Tools / Relationships, and insert the tables into the query.)
5. Save the working file and send it to the instructor at aykim@wuv.edu.
ACCESS TUTORIAL
LAB07 ESSENTIAL
QUERY TRICKS.
SOURCE: ACCESS 2010: THE MISSING MANUAL, MATTHEW
MACDONALD, ISBN: 9781449382377
INSTRUCTOR: ABRAHAM KIM, 571-353-5944, aykim@wuv.edu
Essential Query Tricks (Lab07)
Calculated Fields
Simple Math with Numeric Fields
• +, -, *, ^ (Exponetiation, 2^3 = 8), /, Mod (Modulus, 5 Mod 2 = 1)
Date fields
• ExtendedDeadline: [DueDate] + 14
• ShippingLag: [ShipDate] – [OrderDate]
• LateDays: [DateSubmitted] – #03/20/2010#
Order of operations
1. Parentheses
2. Exponents
3. Division and multiplication
4. Addition and subtraction
• TotalValue: [UnitsInStock] + [UnitsOnOrder] * [Price]
• TotalValue: ([UnitsInStock] + [UnitsOnOrder]) *
[Price]
Expressions with Text
• FullName: [FirstName] & [LastName] join text
• FullName: [FirstName] & “ “ & [LastName]
• FullName: [LastName] & “, “ & [FirstName]
• Price: “The price is: ” & [Price]
Essential Query Tricks (Lab07)
Query Functions
Using a Function
• SalePrice: Round([Price] * 0.95, 2)
Nested functions
• Speed: Round(Abs([DistanceTravelled] / [TimeTaken]), 2)
• Speed: Abs(Round([DistanceTravelled] / [TimeTaken], 2))
Mathematical Functions
• Sqr() Gets the square root. Sqr(9)=3
• Abs() Gets the absolute value. Abs(–6)=6
• Round() Rounds a number. Round(8.89, 1)=8.9
• Fix() Gets the integer portion. Fix(8.89)=8
• Int() The same as Fix(), but negative numbers are
rounded down. Int(−8.89)=−9
• Rnd() Generates a fractional random number
between 0 and 1. Int ((6) * Rnd() + 1)=A random
integer from 1 to 6
• Val() Converts numeric data in a text field into a
number. Val(“315 Crossland St”)=315
• Format() Turns a number into a formatted string.
Format(243.6, “Currency”)=$243.60
Essential Query Tricks (Lab07)
Query Functions
Text Functions
• UCase() Capitalizes text. UCase(“Hi There”)=HI THERE
• LCase() Puts text in lowercase. LCase(“Hi There”)=hi there
• Left() Takes the number of characters from the left side. Left(“Hi There”, 2)=Hi
• Right() Takes the number of characters from the right side. Right(“Hi There”, 5)=There
• Mid() Takes a portion of the string starting at the position you indicate, and with the
length you indicate. Mid(“Hi There”, 4, 2)=Th
• Trim() Removes blank spaces from either side (or use LTrim() and RTrim() to trim spaces
off just the left or right side). Trim(” Hi There “)=Hi There
• Len() Counts the number of characters in a text value. Len(“Hi There”)=8
Dealing with Blank Values (Nulls)
• Is Null
• Nz() Produces 0 if Null. OrderItemCost: Nz([Quantity]) * Nz([Price])
Essential Query Tricks (Lab07)
Query Functions
Functions for Dates
• Date() Gets the current date. Date()=1/20/2010
• Now() Gets the current date and time. Now()=1/20/2010 10:16:26 PM
• DatePart() Extracts a part of a date (like the year, month, or day number). DatePart(#1/20/2010#, “d”)=20
• DateSerial() Converts a year, month, and day into an Access date value. DateSerial(2010, 5, 4)=5/4/2010
• DateAdd() Offsets a date by a given interval. DateAdd (“yyyy”, 2, #22/11/2008#) =22/11/2010
• DateDiff() Measures an interval between two dates. DateDiff(“w”, #10/15/2006#, #1/11/2007#)=12
• MonthName() Gets the name that corresponds to a month number (from 1 to 12). MonthName(1)=January
• WeekdayName() Gets the name that corresponds to a weekday number (from 1 to 7). WeekdayName(1)=Sunday
• Format() Converts a date into formatted text. Format (#27/04/2010#, “Long Date”)=April 27, 2010
Essential Query Tricks (Lab07)
Summarizing Data
Summarizing Data
Totals query is used in a summary calculation (Avg, counting, etc), used for grouping (sum), and used for filtering
(WHERE).
1. Open “Lab07 BoutiqueFudge.mdb” database, and create a new query, “PriceTotals” by using table
Products as shown below.
2. Choose Design / Totals to add Total row in the Design View.
3. Create a query, “Prices in Currency,” as shown below. You need to type the most right column to express
the averaged price in currency.
Summarizing Data
•
Group By Subgroups records based on the
values in this field.
•
Sum Adds together the values in this field.
•
Avg Averages the values in this field.
•
Min Retains the smallest value in this field.
•
Max Retains the largest value in this field.
•
Count Counts the number of records (no
matter which field you use).
•
First Retains the first value in this field.
•
Last Retains the last value in this field.
Essential Query Tricks (Lab07)
Summarizing Data
Joins in a Totals Query
Summary queries are useful to get related information from linked tables.
1. Using “Lab07 BoutiqueFudge.mdb” database, create a new query, “OrderTotals” as shown below.
2. Create a new query, “OrdersTotal,” as shown below. Choose Design / Totals to add Total row in the Design
View.
3. Check the query result in the Datasheet View.
Essential Query Tricks (Lab07)
Lab07 Homework
Defining a Calculated Field
1.
2.
Open “Lab07 BoutiqueFudge.mdb”, and create a new Query, “12% Discounted Price” that gives 12%
discount from the regular product price as shown below. Express the discounted price in Currency format.
Also sort the two columns, ProductCategoryID and ProductName in ascending order.
Go to Datasheet View and you see the DiscountedPrice data are not in currency format. To convert them
in currency format, use Format command such as, DiscountedPrice: Format([ProductPrice]*0.88,
“Currency”).
Essential Query Tricks (Lab07)
Lab07 Homework
Joins in a Totals Query
3.
4.
5.
6.
7.
Create a new query, “OrderTotalByCategory” as shown below, by performing the following steps.
Select Create/ Query Design menu, and add tables Products and OrderDetails onto the Design View.
Drag and drop ProductCategoryID to the first column and click Design / Totals menu in the Design View to
add up the order total by product category.
For the second column in the Design View, type “OrderTotal: [SalePrice]*[OrderQuantity]” and select
“Sum” for Total: dropdown menu.
Save the working file and send it to the instructor at aykim@wuv.edu.
ACCESS TUTORIAL
LAB09 ANALYZING DATA
WITH CROSSTAB
QUERIES
SOURCE: ACCESS 2010: THE MISSING MANUAL, MATTHEW
MACDONALD, ISBN: 9781449382377
INSTRUCTOR: ABRAHAM KIM, 571-353-5944, aykim@wuv.edu
Analyzing Data with Crosstab Queries and Pivot
Tables (Lab09) Crosstab Queries
Understanding Crosstab Queries
A crosstab query is a summary tool like Totals. While Totals summarize for one group, crosstab queries two levels of
grouping (like country and product category. Crosstab queries show in two dimensional table view while Totals query
on a separate row for the two groupings (like country and product categories).
Analyzing Data with Crosstab Queries and Pivot
Tables (Lab09) Crosstab Queries
Creating a Crosstab Query with the Wizard
1.
2.
3.
4.
5.
Using “Lab09 AdventureWorks.mdb” database, create a new query, “OrderedItems_Crosstab” as shown
below.
Choose Create / Queries / Query Wizard / Crosstab Query Wizard and click OK.
Select Queries / Query: OrderedItems / Next.
For row heading, select StateProvince / > . For column headings, select ProductCategory / Next.
For calculation, select OrderQty / Sum / Next. Ensure “Yes, include row sums” checked. Then, click Next /
Finish.
Analyzing Data with Crosstab Queries and Pivot
Tables (Lab09) Crosstab Queries
Creating a Crosstab Query from Scratch
1.
2.
3.
4.
5.
Using “Lab09 AdventureWorks.mdb” database, create a new query,
“SalesByCategoryAndCountry_Crosstab” as shown below.
Choose Create / Queries / Query Design. Add a query by selecting Queries / OrderedItems / Close.
Choose Query Tools / Design / Query Type / Crosstab.
Choose fields as shown in the Design View below and check the Datasheet View.
Try to add one more field, “StateProvince” for row heading in the Design View. Check its result as below.
Analyzing Data with Crosstab Queries and Pivot
Tables (Lab09) Lab09 Homework
Creating a Crosstab Query with the Wizard
1.
2.
3.
4.
Using the OrderedItems_Crosstab Query in the “Lab09 AdventureWorks.mdb” database, create a new
query, “Sales_Crosstab” by duplicating the OrderedItems_Crosstab. Display All Access Objects in the left
pane, click OrderedItems_Crosstab, ^C, ^V, and name the new query file as “Sales_Crosstab.”
Open the Sales_Crosstab and go to the Design View of the new query. You may need to replace
[OrderQty] expression in the Query Design View with the following expression: Sales:
[OrderQty]*[UnitPrice]. Do similar operation for “Total of Sales.”
You may rename the OrderedItems_Crosstab Query into Sales_Crosstab, by right-clicking the Query
OrderedItems_Crosstab.
Save the updated database file by naming it as: “Lab09 AdventureWorks.mdb (Your Name)” and send it to
the instructor at aykim@wuv.edu.
ACCESS TUTORIAL
LAB12 CREATING
FORMS
SOURCE: ACCESS 2010: THE MISSING MANUAL, MATTHEW
MACDONALD, ISBN: 9781449382377
INSTRUCTOR: ABRAHAM KIM, 571-353-5944, aykim@wuv.edu
Creating Simple Forms (Lab12)
Creating Forms
Building a Simple Form
Forms are specialized database objects that make it easier for anyone to review and edit the information in a table.
1. Download and open “Lab12 BoutiqueFudge.mdb” database from ABHE.
2. In the navigation pane, select Products table. Choose Create / Forms / Form.
3. [Field Arrangement] Select both Price and its contents, and drag them between ProductName and
Description.
4. [Column Width] Try shrink column width by clicking a contents field and move/drag the cursor at the
corner.
5. [Header Name] Double-click a field header to edit its text.
6. [Formatting] Try change fonts by selecting a contents field and change fonts in the Text Formatting menu.
Creating Simple Forms (Lab12)
Using Forms
Using Forms
1.
2.
3.
4.
5.
6.
7.
8.
9.
Use the forms previously created and go to Form view by right-clicking on the tab.
[Searching] Search the record that contains “magical”, by clicking Home / Find / Find command.
[Adding] Click “▶*” button at the bottom of the Form View.
[Deleting] Delete the new record by choosing Home / Records / Delete, or by clicking the left margin of the
Form and pressing Delete key.
[Sorting] Choose a field for sorting, right-click, and choose a sorting option.
[Filtering by condition] Right-click a field and look for a submenu with filtering options.
[Advanced filters] Choose Home / Sort & Filter / Advanced / Advanced Filter/Sort. To apply this filter,
Choose Home / Sort & Filter / Advanced / Apply Filter/Sort.
[Filter by Form] Choose Home / Sort & Filter / Advanced / Filter By Form. Select Price field and enter
“>=15” in the field. Add another filter on Category field by selecting “Beverages” on the drop-down menu.
To apply this filter, Choose Home / Sort & Filter / Advanced / Apply Filter/Sort and check the filter result.
[Form Wizard] Choose Create / Forms / Form Wizard. From the drop-down list, choose the table to use.
Add the fields to include as below. To adjust Column widths, go to Design View to adjust them.
Short Cuts
•
•
Ctrl+Z – Undo the previous typing
[Removing new record] Click Esc key
twice

Purchase answer to see full
attachment