Easy Excel Learning

Juan Hinojosa, Technologist and Math Instructor

Category: Structured Query Language (SQL)

  • Advanced Access 12-7-2013

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

    Advanced Access Vacation List 12-7-2013

    It has been provided so you can review the action queries: Make Table, Append, Delete query, and Update query. You can also use it review the Crosstab query, Union query, SQL manipulation, and the Expression Builder.

    Have a Happy Holiday and a Happy New Year!

  • Advanced Access Vacation List 12-7-2013

    Advanced Access Vacation List

    To access the database, download the zip file and then right click downloaded zip file to select extract.

  • Advanced Microsoft Access 2010 (5-4-2013)

    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.

  • Advanced Access 12-1-2012 (Day 2 of 2 “Vacation List database”)

    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)