K201 Micrsoft Access Lab Notes (Midterm) PDF

Preview

Summary

All information necessary to study for K201 Midterm...

Description

BUS-K201 LAB NOTES (MS ACCESS) Computers in Business Indiana University, Bloomington Fall’2016

Chapter 3: Importing Data, Keys and Relationships To Import Data 1. Click “Import….” under the EXTERNAL DATA tab 2. Choose what type of file to import >>> Excel, Access or Text File 3. Browse for the file to import 4. Choose either: -

“Import the source data into a new table in current database” if there are no data in current table OR

-

“Append a copy of the records on the table” to keep current data while adding more data to the current table

5. Choose the appropriate delimiter>>> Tab, Semicolon, Comma, Space, Others (usually tab for excel and semicolon for text file) 6. If first row contains the field name (usually), TICK the “First row contains column heading” box 7. ALWAYS choose “No primary key” because we will do it by ourselves at the end 8. Go and set the “Primary Key” for each table

To Create Primary Key 1. Be in the table you want to create the primary key for/in 2. Click on DESIGN tab then click “View” then “Design View” 3. Right click on the field that you want to set as the primary key, click “Primary Key” 4. You should see a key symbol on the left side of the field you set as a primary key.

To Create Relationship 1. Click “Relationships” under the DATABASE TOOLS tab 2. Click and drag a primary key to match the foreign key 3. When the “Edit Relationship” box appear, make sure…  Enforce Referential Integrity  Cascade Updated Related Fields Cascade Delete Records (not check) 4. Click Create

Chapter 4: Data Types and Basic Field Settings Data Types (in Design View) Data Type

Usage

Short Text

Alphanumeric Data (names, phone number, zip code, etc.)

Long Text

Large amounts of alphanumeric data (sentences & paragraphs)

Number

Numeric Data (usually only use if data may be used for calculation)

Date/Time

Date and times

Currency

Money

Auto Number

Automatically list down number according to the number of data

Yes/No

Create a check box to choose “Yes” or “No”

OLE Object

-

Hyperlink

-

Attachment

To allow any attachment (image, file, etc.)

Calculated

-

Lookup Wizard…

Create a drop down box for user to chooses from the list we provide

Field Properties (in Design View, bottom of the page) Field Size: Try to minimize the field size e.g. if zip code is 47401-1234 then the number of characters is 10 characters (including the -) therefore, if all the data in the field is zip code and it will never exceed 10 characters then set the field size as 10 to minimize the file size and save up space. Format: This is where you set how you want your data to be shown e.g. all uppercase, date in the DD-MMMYY format or etc. Some of the formatting symbols are: SYM.

PROPERTY

INPUT

>

Force all following characters to be uppercase

example

>:

EXAMPLE

<

Force all following characters to be lowercase

EXAMPLE

LLL000

EXAMPLE >L = require 1 character, uppercase < L = require 1 character, lowercase

= =

0 “M” or “F” Between 0 and 10 IS NOT NULL NOT > 10

PROPERTY Less than Greater than Equal to Less than or equal to Greater than or equal to Not equal to Not equal to 0 Input may only be M or F. Input may only be numbers between 0 and 10. Forces user to enter a value. Any number greater than 10

VALIDATION TEXT Value must be less than Value must be greater than Value must be equal to Value must be less than or equal to Value has to be greater than or equal to Value cannot be… Enter a nonzero value. Please enter only “M” or “F” Please enter values between 0 and 10. Please enter a value/data. Please enter a value higher than 10.

Required & Allow Zero Length: These 2 properties go together. If one of the field is “YES” then the other has to be “NO”. If the REQUIRED is “YES”, it forces user to input a value. If the field is required, then we should not allow zero length.

Chapter 5: Data Entry, Lookup Fields, and Fields Properties How to Use the “Lookup Wizard”? The “Lookup Wizard” is one of the available data type where we can create a drop down box for users to select data from the lists we provided. To create the drop down box and list: 1. Choose “Lookup Wizard…” as the field type 2. Choose either: “I want the lookup field to get the values from another table or query” if you want to pull the list

-

from other tables or query. (SKIP TO STEP 6) OR “I will type in the values that I want” if you want to create the list of choices by yourself.

-

3. If you choose to type the values by yourself, after you click next, a spreadsheet will appear for you to enter your data. Enter your data and click next. 4. If you want users to only choose from the list you have created, tick “Limit to list.” 5. If you want users to only choose one of the options from the list, don’t tick “Allow multiple value.” BUT If you choose to get values from another table or query and use it for your list of options…. 6. Choose which table you want to pull the data from, click next. 7. Choose which fields you want to pull the data from, click next. 8. Choose how you want the list to appear e.g. Only list out employee’s last names. 9. At the last step, if you only want users to only choose one of the options from the list, don’t tick “Allow multiple value.” 10. ALWAYS tick “Enable Data Integrity”

Lookup Fields This function is used to search for terms/data you want. There are “Wildcards” which are search characters that we can enter into the search field to make it easier for us to find data. To use the lookup/search function, click the HOME tab then click the “Find” button. THIS IS ALSO IMPORTANT FOR WRITING QUERIES!

WILDCARDS CHARACTERS

REPRESENTS

INPUT

OUTPUT

*

Any number of characters, including none

Wh*

Who, what, when, where, why

?

Any one character

B?LL

Ball, bell, bill, bull

#

Any one number

20##

2011, 2000, 2006, 2016, 2020

Chapter 6: Basic Select Query Design, Criteria and Total Row What is a “Query”? A query is a function that creates a datasheet of specified records from one or more tables depending on the field, table, sort, show and criteria we set for each query.

Guidelines for Writing Queries (Ch. 6 Page 3) 1. Read the problem thoroughly and determine what the problem is asking us to identify, sort, display or calculate. 2. Open the RELATIONSHIP window, look at the table and locate the data we need for the query. For instance, if we need a data in both table A and C however, table A has a relationship with B, B has a relationship with C but A and C doesn’t have a relationship, we need to pull in all the 3 tables into our query design as table B is the connecting table between A and C. 3. Create the query in Design View, add only necessary tables on the top half of the screen. Don’t add unnecessary tables. 4. Add fields to the design grid. 5. Add 1 criteria at a time, run the query to check. 6. Create calculated fields if needed. 7. Turn on the “Total Row” if needed. 8. Sort the data according to the problem request. 9. Show only fields that the problem asked us to show. **BEFORE CLOSING A QUERY, DO THE 3s: SORT, SHOW, SAVE**

How to Create a Query? 1. In the CREATE tab, click “Query Design” 2. Select the tables needed to create a query. 3. Double click on the fields in the table to add them to the query design grid (the grid at the bottom half of the screen). OR drag the field from the table and drop it into the design grid to add it. 4. Constantly click the “Run!” button on the top left of the screen under the DESIGN tab to check the results of each steps we do and check for mistakes. 5. Set the field, table, sort, show and criteria as necessary.

6 Basic Components in Query Design Grid

1. Field The “Field” refers to the field in the table we choose to pull the data from for the query. Should automatically show up after we add the field onto the design grid To change how the field name appear in the Datasheet View without changing the original name, we can use colon: to do that. E.g. DATASHEET VIEW NAME: ORIGINAL FIELD NAME

2. Table The “Table” refers to the table we choose to pull the data from for the query. Should automatically show up after we add the field onto the design grid.

3. Total (hidden unless we choose to use it) The Total function is used to eliminate duplicated records. -

To show the “Total” row in the design grid, click the ∑ Totals icon in the DESIGN tab on the top of the screen.

-

There are a few choices for the total row: Group by, Sum, Avg, Min, Max, Count, StDev, Var, First, Last, Expression, Where.

Group by (default)

The function groups all the same data together, eliminating duplicated data.

Sum Avg, Min, Max Count

Total of the field values for selected records (how much) Find average, lowest, highest field in the record accordingly. Number of records (how many) **WE CAN ONLY COUNT PRIMARY KEYS** This function would hide the field when you go back to Datasheet View

Where

E.g. You use the employee address as one of the field to write up a query however, you don’t want to show it in the Datasheet View; you choose the “where” function.

4. Sort Queries sort and apply the things we typed into the query grid from left to right. We also need to sort how we want our data to show in the Datasheet View. Select which field we want to use to sort out information e.g. sort ascending by Employee ID

5. Show When the “Show” box is ticked, the field will be shown in the Datasheet View. If you don’t want to show it, don’t tick the box. This function works the same way as the “Where” function in the Total Row however, if we did not use the Total row in a specific query, simply untick the box to hide a specific field. 6. Criteria

Criteria are conditions we want our fields to show.

EXAMPLE 1: We want to find out how many CURRENT EMPLOYEES we have, we need to write a query in which…. TABLE FIELD CRITERIA

xxxEmployee End Date Is Null

The criteria “IS NULL” means the field needs to be empty. If an employee is current, they should not have an end date.

EXAMPLE 2: We want PART TIME EMPLOYEE and we only have the FULL TIME? field which is a yes/no datatype, we can…. TABLE FIELD

xxxEmployee Full Time? (Check box format)

CRITERIA

NO

OR

FALSE

The “NO” or “FALSE” criteria means the box would be unchecked. The “YES” or “TRUE” criteria means the box would be checked.

EXAMPLE 3: We want Customers who LIVE IN BLOOMINGTON, IN AND CHICAGO, IL. TABLE FIELD

xxxCustomers City

xxxCustomers State

CRITERIA OR

“Bloomington” “Chicago”

“IN” IL

EXAMPLE 4: We want only Customers who live in the area with the zip code that starts with 47.

TABLE FIELD

xxxCustomers Zip Code

CRITERI

“47*”

A The asterisks (*) represents any number of characters/numbers that may follow 47. Therefore, our results may show 47401, 47405, or 47597.

EXAMPLE 5: We want customers who made transaction within this year (Hint: Beginning of the year until today. TABLE FIELD

xxxTranscation TransactionDate

CRITERIA

BETWEEN “01/01/2016” AND “DATE()” The “Date()” represents today’s date.

BETWEEN and AND is not in quotation because they are operators.

Q: What if the instruction asks us to show the data as Last Name, First Name, Employee ID, State but it ask us to sort Employee ID by ascending then Last Name by ascending? Does it make any difference putting Employee ID field after the Last Name field and sort both of them as ascending? A: Yes, it does make a different if you put the Employee ID after the Last Name and sort both of them as ascending. Since Access process our queries from left to right, if the instruction asks us to sort Employee ID before Last name but want the Last Name to appear before Employee ID…we need to:

FIELD SORT SHOW

Employee ID Ascending No Show (untick box)

Last Name Ascending Show

First Name

Employee ID

State

Show

Show

Show

Chapter 7: Concatenation, Calculated Fields, and Aggregate Functions Instead of using only 1 field for our query design, we can write EXPRESSIONS to calculate/combine data from different tables.

Each expression is a combination of some or all of the following: 1. Operators: + - < > / * = < 2. Constants: numbers and/or letters 3. Functions: sum() avg() date() 4. Identifiers: Name of the fields; type it in brackets, [FIELD NAME] to indicate that it is a field’s name.

To use expressions instead of fields, we can right click the “FIELD” in the query design grid then type the expression as necessary. To have a better view of the FIELD, right click in the field box and click ZOOM to have an enlarged version of the box; this would help us minimize any typo.

Example 1: We want to create a field name “AGE” which could be today’s date minus (-) by the individual’s date of birth, then divided by 365 days.

AGE: (Date() – [DateOfBirth])\365

Example 2: We want to create a field name “NAME” which includes an employee’s LAST NAME then FIRST NAME, separated by a comma (,).

NAME: [First] & “ , “& [Last]

Example 3: We want to create a field name “5% BONUS” that calculates how much bonus an employee would receive. The bonus would be 5% of their salary.

5% BONUS: [Salary]*0.05

Example 4: We want to create a field name “TOTAL REVENUE” that calculates how much revenue we have received after subtracting out the discount.

TOTAL REVENUE: [Quantity]*[CatalogPrice]*(1-[Discount%]

**Make sure to type the field name correctly if not, there will be an error when you try to run the query**

Chapter 8: Parameter Query and Query on Queries What is “Parameter Query”? A parameter query prompts user for criteria each time the query is run.

EXAMPLE: Write a query that ask for a member’s ID number and then return the selected Member ID along with the member’s first and last name in a single field named “NAME” where the first and last name is separated by a space (similar to Exploration in Chapter 8 P.3)

FIELD CRITERIA

MemberID [Enter Member ID:]

Name: [First]&” “&[Last]

Take note that in the NAME field, there is a space between the 2 semicolon, representing the divider we want to have between our first and last name. # 1 2 3 4

INPUT [First]&” “&[Last] [First]&”,”&[Last] [First]&”, “&[Last] [First]&”/”&[Last]

OUTPUT First Last First,Last First, Last First/Last

Notice that the 2nd and 3rd input are different. The 2nd one has no space after the comma, the 3rd input has a space after the comma; YES-a single space does change the output.

Using Multiple Queries to Solve Problems Example from Chapter 8 Exploration Section Page 4 (slight change in names). EXAMPLE: We want a query that will calculate the percent of current employees from each state. The query should list the STATE field and a field name “%OfEmployees”. Format the “%OfEmployees” field as percent then sort the query descending by this field. First, we need to create 3 queries: the numerator, denominator and the query itself.

Step 1: Create a query that will return the number of current employees per state. Name this query, “Numerator”. Table Needed: tblEmployee FIELD TABLE TOTAL SHOW CRITERIA

State tblEmploye e Group By Show

#ofEmployees: EmployeeID

End Date

tblEmployee

tblEmployee

Count Show

Where No Show Is Null

We only count primary keys. Turn on TOTALS row to eliminate duplicating data/set aggregate function such as COUNT. Current Employee = No End Date

Step 2: Create a query that will return the total number of current employees. Name this query, “Denominator”. Table Needed: tblEmployee FIELD TABLE TOTAL SHOW CRITERIA

TotalEmployees: EmployeeID tblEmployee Count Show

End Date tblEmployee Where No Show Is Null

Step 3: Create a query that will return the percent of employees from each state using the “Numerator” and “Denominator” queries. Queries Needed: Denominator & Numerator FIELD TABLE SHOW

State Numerato r Show

%OfEmployees: [#ofEmployees]/[TotalEmployees]

Show

Don’t forget to format %OfEmployees field to percent (%). Right click on the field, click PROPERTIES

Under the FORMAT field, select “PERCENT”

Chapter 9: Query Wizard and Action Queries What is a “Query Wizard”? Query wizards are tools that would help us create queries. To use a query wizard, click on the CREATE tab then QUERY WIZARD (beside query design).

There are 4 types of query wizard: - Simple Query Wizard - Crosstab Query Wizard - Find Duplicates Query Wizard - Find Unmatched Query Wizard 1. Simple Query Wizard This wizard is just simply the normal query we make by clicking CREATE QUERY. 2. Crosstab Query Wizard This wizard creates a query that looks like a spreadsheet, having a field from a table as a row headings and another field as the column headings. Follow and answer the questions the wizard ask as they appear, look at the preview in the wizard to check if it is what we want. 3. Find Duplicates Query Wizard This wizard creates a query that would find records with duplicate field values in a single table or query. 4. Find Unmatched Query Wizard This wizard creates a query that finds records (rows) in one table that have no related records in another table. EXAMPLE: Find employees who have never made any sale/transaction. If an employee has never make any transaction/sale, then their EmployeeID would not be in the transaction table. This wizard could find that for you. Click “Find Unmatched Query Wizard” then click “tblEmployee” then “tblTransaction”; these 2 tables are the tables that contain the data we want to compare with one another. The wizard would then ask us to choose which field we want to match, in this case, we will match “EmployeeID” from the tblEmployee table to the “SoldOrPackagedBy” in the tblTransaction table. We then can choose which fields we want our result to show/have. Click “Finish”.

NOTE: Remember that after you create a query using a query wizard, you can still go into the DESIGN VIEW to make any changes or add any criteria if necessary.

What are “Action Queries”? An action query is a query that makes changes to or moves many records permanently in just one run. These action queries can be found under the DESIGN tab in the QUERY TYPE section. There are 4 types of action queries: - Select - Make Table - Append - Update - Delete Note: Before doing any action queries, make a backup of the table by simply copying and pasting in the “ALL ACCESS OBJECTS” tab (the left column of your screen with the lists of tables/queries/etc.). It is always better to have a backup in case something goes wrong. 1. Select This action query is the one w...