Day 18: MICROSOFT ACCESS – CHAPTER 3 CONTD. - PowerPoint PPT Presentation

Day 18 microsoft access chapter 3 contd
1 / 44

  • Uploaded on
  • Presentation posted in: General

Day 18: MICROSOFT ACCESS – CHAPTER 3 CONTD. Akhila Kondai October 21, 2013. Exercise. Go to the lecture notes page and Download Music_extended.accdb

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Day 18 microsoft access chapter 3 contd



October 21, 2013



  • Go to the lecture notes page and Download Music_extended.accdb

  • Note the added records to complete the English band Def Leppard and also the addition of the Canadian band Rush…



  • Create a new query in design view

  • Add 3 tables: Bands, Countries, & Members…

Specify an exact match condition

Specify an Exact Match Condition

  • An exact match condition only shows records that match your criteria in the “Criteria” row of the query design grid

  • The field values of the records for the field the criteria is specified in must match the condition exactly to be shown in the query results



  • Add the following fields from these tables:

    • [Bands] BandName

    • [Member] FirstName, LastName

    • [Countries] Country

  • Run the Query to see the unrestricted results of all the 16 records

Adding criteria

Adding Criteria

  • Return to Design View

  • In the criteria row for Country, type England to have it show us a Dynasetwith only band members from England

  • Rerun the query

  • Change England to Americaand rerun

  • Try Portugal to see that nothing returns!

Using comparison operators and or

Using Comparison Operators… “And” / “Or”

  • When using multiple conditions for a query, you can use the logical operators to combine conditions

    • The “And” logical operator specifies that both conditions must be met

    • The “Or” logical operator specifies that one or the other of the conditions must be met

Using and and or

Using “And” and “Or”

  • When you enter two conditions in the same row of the query design grid, an “And” condition is created

  • If you enter two conditions that are on separate rows, an “Or” condition is created

Illustration of and or logic

Illustration of “And” & “Or” Logic

Creating and condition in the design grid

Creating “And” Conditionin the Design Grid…

Let s try an and

Let’s Try an “And”

  • Clear any existing criteria

  • Add the field [Members] Living

  • Type England in the Country row and create the AND condition by placing Yes in the Living row to return all living band members from England

  • Run it

  • Go back and change Yes to No and rerun

Let s do an or

Let’s do an “Or”

  • Remove the old criteria

  • We will set the criteria to show all members from RushOR anyone from any band that is living at all from the list of members…

  • First lets put “Rush” in the [Bands] Name field and run to see the results

Create the or

Create the “OR”

  • We wish to not only see the members of Rush, but also in addition to them, anyone that might be alive in the members table.

  • In Design view, type “Yes” in the OR row for [Members] Living

  • You should now see a list of 10 additional records (13 total)

Sorting results by data type

Sorting Results by Data Type…

Sort exercise

Sort Exercise

  • Open the RockTrivia Query in Design View

  • In the Sort row for the BandName field, have it sort the records in Ascending Order

  • Run the Query

Two level sort

Two-Level Sort

  • Leave the existing sort in place and move the last name field so that it is before the first name field in the Design view…

  • Click once to select it… let go and then click and drag to move it over

  • Add a secondary sort by having it do Last Name in Ascending order

  • Run it. Last names are now show alphabetically inside the band names

We can also use wildcards

We can also use “Wildcards”

Wildcard example

Wildcard Example

We want to see all members whose last names begin with “E”…

  • Remove all existing sort options

  • In the last name field, type the letter E in Criteria and run the query.

  • (No results show as no one has just the letter E for a last name)

  • Make it read E*and rerun to get any other characters…

Wildcards continued

Wildcards continued

  • Change the criteria to show people whose last names end in “N”

    • *N

  • Run it and we should see 7 records.

  • Remove all criteria.

Using fields without showing

Using Fields without Showing

  • It is possible to use a field in searching but not show that field in the query results

  • Let’s say we want to see all band members whose bands begin and end in “d”, but not see the band name

  • Remove the “Show” row checkmark for BandName

  • Create the criteria and run !



Note: This technique is similar to what part of a query asks for in HW4 !

Date field entries for birthday

Date Field Entries for Birthday

  • Note Born field in Members table is completed

  • Close Members table

Criteria operators exercise

Criteria & Operators Exercise

  • Go to Design view for RockTrivia, add the Born field, and clear criteria, re-show BandName

  • In the criteria row for born, add: >1/1/1960

  • Note we get 3 records

  • Try making it: >=1/1/1950 AND <=1/1/1960

  • We get 9 Records

  • Change it to: <1/1/1960

  • We get 13 records

Calculations in a query

Calculations in a Query…

  • Mathematical calculation can be done as part of the query..

  • We often do this for things that we do not need to store and take up space with

  • Expressions can be entered into the query design grid as if they were regular fields

  • Done on data type fields treated as numbers

Calculations in a query1

Calculations in a Query…

  • To perform a calculation in a query, you must created a calculated field in query design.

  • Make certain that you are following the rules of precedence !(Please Excuse My Dear Aunt Sally)

  • Two options for entering expressions:

    • Enter the expression directly into the field text box

    • Enter the expression in the Expression Builder

Creating a calculated field

Creating a Calculated Field

  • Remove the existing criteria

  • Click in the next available blank field where the name would be, and then click the magic wand “Builder” icon above

  • The expression builder shows up…

The expression builder

The Expression Builder…

Let s make a query to try this

Let’s make a Query to try this…

  • We want to figure out how many years they are alive. It would be inefficient to store this as we would have to update it all the time.

  • Build or type the following:

    • Age: (Date()-[Members]![Born])/365

      Note: If you see <<expr>> be sure to remove it ! ! !

  • Run it !

  • Expand the width of the column if you see ####

Changing decimals precision

Changing Decimals Precision

  • Note: Similar to what is needed for to set decimal places on query results in homeworks!!


  • In design view, right click Age field

  • Click Properties

  • Select Fixed for Format and 0 for Decimal Places

  • Re-run it to see it without the decimal places

Total queries

Total Queries

  • “Totals” in a query can group field values and also perform basic computational functions on them

  • To make the Total row appear, you need to click the Sigma symbol “Totals” button in query design view !



We are running an investment firm and have some consultants working for us who have clients for which they manage investments.

We want a query that will show us how many clients each consultant has and the total amount of investments each consultant is handling…

  • Save locally and open Total_example.accdb

Structure of tables relationship

Structure of Tables & Relationship

This Database has not yet had a relationship created. We will do it in the query for this one. Sometimes under special circumstances you will create them here instead of in the relationships window. Generally it is done in the relationships window though…

Building the total query

Building the Total Query

  • Create ribbon > Other group > Query Design

  • Add both tables

  • Create relationship inside query by dragging ConsultantID fields together

Building the total query1

Building the Total Query

  • Add the field [Consultants]LastName

  • Add the field [Clients]LastName

  • Add the field [Clients]Assets

  • Run it without Total options > back to design view

  • Click the button on the Query Tools / Design ribbon to insert a “Total:” row

  • Note that the default for each is “Group By”

Building the total query2

Building the Total Query

  • We will Group By the last name of the consultants to show each of them singularly

  • We will Count the last names of the clients as they are tied to the consultants to show how many clients each has !

  • We will Sum the assets of the clients to show how much all clients of each consultant have together

Setting up the total row

Setting up the Total Row

  • Have [Consultants] LastName as Group By

  • Have [Clients] LastName as Count

  • Have Assets as Sum

  • Run the Query !

Aliasing the field names

Aliasing the Field Names

  • Sometimes for a Field, a Calculated field, or a Total field, we like to make the display name more specific in the query results

  • To do this, simply change the field name in design view by adding a new nameand an “:”

  • An example for the clients LastName field:

    # of Clients: LastName

Alias the 3 fields in design view

Alias the 3 fields in Design View

  • Consultant:LastName

  • # Clients:LastName

  • Total Assets:Assets

  • Run the Query and note the field names in the dynaset !

Doing multiple things to 1 field

Doing Multiple Things to 1 Field

  • Sometimes you may need to do multiple total query-type actions to a single field.

  • Download and open the file CSDemo.accdb from the site

Background for total query

Background for Total Query

  • Look at the contents of the Sales table

    We want to display our:

  • Number of sales

  • Total dollar amount of sales

  • Average dollar amount sale

Creating the total query

Creating the Total Query

  • Create a new query in design view

  • Add the Sales Table

  • Add the Amount field 3 Times ! ! !

  • Click button to show the Total row

  • Set them to Count, Sum, and Average

  • Run it !

  • Close Access

Importing xml data to tables

Importing XML Data to Tables

  • Download and save Bands.xml file on the desktop

  • External Data Ribbon > Import group > XML File

  • Browse for the xml file

  • Select Open& OK

Importing xml files

Importing XML FILES

  • Verify Structure and Data is checked

    • This brings in the table & its records

    • Later in the course we will use other options

  • Click OK and Close

  • Open the table in the database !

Day 18 microsoft access chapter 3 contd

Questions ?

  • Login