Category: Advanced Access

Below is the link to the Access database file developed in class:

Advanced Access 5-4-2013

It has been provided so you can practice the exercises with setting up relationships, designing a database, using SQL to create a union query, creating subforms, and to review the report tool.

Objective:  Show all columns and rows from a table.

SQL Example 1: Start with the basic example below.

Select *
From T01_VacationList

Objective:  Filter rows and show all columns.

SQL Example 2: Filter and show all columns.  Also use “V” to write SQL code more compactly.

Select *
From T01_VacationList as V
Where V.Category = ‘Equipment’

Note:  
            Access 2010 uses single quote ‘  ‘ in SQL code.
            Access 2007 uses double quote ”  ” in SQL code.

The filter condition is defined by “Where”
Where V.Category = ‘Equipment’

• Select ‘*’ means that all the attributes will be displayed.
• “From” indicates the table which table is used.
• “As” indicates the table is given the shorthand name b
• To execute the query, click the RUN symbol “!” from SQL view.

Objective:  Filter rows and show selected columns.

SQL Example 3: Filter and show selected columns.

Select Description, [Associated Price]
From T01_VacationList as V
Where V.Category = ‘Equipment’

Objective:  Invert your filter using the “Not” operator.

SQL Example 4: Using “Not” operator.

Select *
From T01_VacationList as V
Where Not( V.Category = ‘Equipment’)

Objective: Use mathematical formulas to add a math driven column.

SQL Example 5: Find the Subtotal.

Select V.*, [Associated Price] * quantity as Subtotal
From T01_VacationList as V

Objective:  Use mathematical formulas to add a math driven column AND the sum.

SQL Example 6: Find the Subtotal.

Select sum([Associated Price] * quantity) as Subtotal
From T01_VacationList as V

Try using OR, AND, and ” Like ‘a*’ ” with the “Where” parameter.

 Objective:  Using the UNION query to consolidate similar tables or queries.

First you will need to create and save two queries named q1 and q1.

SQL Union Example: Create a union of q1 and q2.

Select [ID], [Description], [Category], [Associated Price]
From q1

Union Select [ID], [Description], [Category], [Associated Price]
From q2;

Decipher the following query:

SELECT [First Name1] AS [First Name],[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 01]

UNION SELECT [First Name1] AS [First Name],[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 02]

UNION SELECT [First Name1] AS [First Name],[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 03]

UNION SELECT [First Name1] AS [First Name] ,[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 04];

Below are the files for the Day 2 learning activities:

Advanced Access 12-1-2012 (vacation list_start point)

Advanced Access 12-1-2012 (vacation list_end point)