Initially this may take some time to download as it contains many graphic files.

Introduction to Microsoft Access 

(ac01.html)

 

 Read the questions and follow the links to find the correct answers. Do one question at-a-time; repeat it until you get it right!  Check each answer with the 'Test' button, then press the 'Submit Data' button when you have completed the quiz.  There is no penalty for retaking this quiz:  Continue taking it until you have earned the maximum of 400 points!  

Topics covered:  Microsoft Access Database Fundamentals. 

Please fill out the Name, and email boxes.  Section No and Date are filled out automatically.

Section No  Last Name.. First Name
yy.mm.dd.  email addr       
  Use "test" buttons to check individual answers before
pressing the "Submit Data" button at bottom of form.

This lesson is about verifying you have learned the basics of Access.  This is a self-paced lesson; a 'non-penalty' quiz -- that is -- you may retake this as many times as needed in order to learn the concepts presented!  Your progress will be 'tracked'.  Don't know an answer?  Skip it.  Come back later!  No penalty for skipped answers. Only for 'guessing'.  You are expected to be able to modify and run queries!  To do this lessonz, you must actually open Microsoft Access, and build databases, tables and queries!   When you do the work, your answers will show a 'consistency'.  Incorrect and inconsistent answers will hurt your score!    

1  

Do you understand the concept of a Database?

 
 

 

This database contains (2) records for Employees ABLE and BAKER.  Each record contains (7) fields (count them).  Records appear like rows, and Fields are columns.  Each Field is made up of letters and/or numbers (characters).    From small to large:  Characters make up Fields, which make up Records which make up a Database.   With a DataBase Manager Program, such as Access, you can Add, Modify, and Delete Fields and Records.  You can also Sort, Extract, Perform Calculations, and Generate Reports.

Experiment with the Payroll Calculator. 

Enter 50 hours @ $10/hr for ABLE.   Check that his GROSS is $550 (400 reg + 150 ot).

Enter 40 hours @ $15/hr for BAKER.  Check that his GROSS is $600.

Click http://auciello.tripod.com/payroll-js.html  to access the Payroll Calculator.

 
   Enter 50 hours for BAKER @ $10/hr.  What is his Netpay?  Type in the number only, no $ sign into the box.  Press the "Test"  button to check answer.

2    

 

 

Creating a desktop shortcut to Access.

 

On my XP system, I clicked "Start" (left bottom) then clicked "All Programs" (above it), then I spotted Microsoft Access on the menu (partially covered in the center) and  "right-clicked" on it, then clicked  "Send To", then "Desktop"

and created a shortcut to Access on My Desktop.

Let me restate this in Programmer's Notation:  Click "Start" - "All Programs" - Right-Click  "Microsoft Access" - Click "Send To" - "Desktop"  (assuming your mouse is configured with the Left Button as the Primary Button). 
Again: Start-All Programs-RC Microsoft Access-Send To-Desktop.

In English: Click Start.  Click All Programs.  Right-click Microsoft Access. Click Send To.  Click Desktop.  This  puts the Microsoft Access icon on your desktop.

Minimize this window by clicking Minimize (the middle button on the top right corner) to get to the Desktop (shown on the left).

On the Desktop, click Start-All Programs-Right-click Access.

 

 

 

 

 

This menu opens up.  What option is at the top?

 

 

 

  
   After you Right-click "Microsoft Access", a selection list comes up.  What is the  top word on this list?  Type the word into the box at the left.  Press the "Test"  button to check answer.

3    

 

Can you Start Access?

Minimize this lesson by clicking Minimize (the middle button on the top right corner) then clicking the icon for Access that you just created on the desktop!

This invokes (brings up)  Microsoft Access with its Standard toolbar (top) and Database toolbar (partially hidden). 

By pointing at (hovering) on each word -- File, Edit, View, etc -- a  pull-down menu appears.  Clicking "File" caused its Pull-Down menu (New, Open, etc) to appear.

 

 

 

Point your mouse at Edit.  The first entry is "Undo" which is not highlighted or "active".  What is the first activated entry on this list? (below "Copy")

  
   This is a two-word answer. Type the two words into the box at the left. Press the "Test"  button to check answer.

4 Creating a Database  (by first creating a Table):

I clicked the Shortcut Key ...

 

 

then I clicked File-New

 

    

 

  

 

 

Under "New",

I clicked Blank Database

Repeat:  I clicked File-New-Blank Database.

Again: I Started Access, Clicked "File".  Clicked "New".  Clicked "Blank Database" and brought up the "File New Database" screen below: 
 

I created an empty database in the My Documents folder. (check it if you like).

 

 In the File name box, I typed PAYROLL-MDB .. then I pressed the "Create" button.

(I just named my new database "PAYROLL-MDB")

(MDB = Microsoft Data Base)

 

Immediately after pressing the "Create"  button, this screen appears. The database REQUIRES A TABLE, thus we will create one using "Create  table in Design view", that is, we will use "Design View" to create the structure of the Table... ("Design view" means we will look at the structure of the Table.)

 
 

(A Database is Table where each row is a Record which is made up of Fields, in this case 5, (EMP-NUMBER .. DEDUCTIONS).   There are 5 records each with, of course, 5 fields.  Exactly like an Excel Spreadsheet format and the  Payroll Calculator  which you already used.

 First, let's take a look at what we will create WHEN WE ARE DONE!  There are (5) Fields -  

EMP-NUMBER, EMP-NAME,  PAYRATE, HOURS, and DEDUCTIONS.
 

 As soon as you hit "Create" this blank Design Form appears.  You must name each field, and specify the type of data that it will contain (Text, Number ..)


Go ahead and fill out (populate) the empty form with the information below. When entering "Data Type", a Scroll Down Menu appears giving the options of Number, Text and other data types.

 

Field Name    Data Type
EMP-NUMBER    Number
EMP-NAME      Text
PAYRATE       Number
HOURS         Number
DEDUCTIONS    Number

You are effectively declaring the name of each field and the type of data it will contain.  Numbers are best classified or "typed" as Numbers, and Names as Text.  Finish declaring each of the 5 fields.

When you have finished, you have defined the structure of the Table. Then close by pressing the Exit key in the right corner. (This is the way to Exit Access Windows. Press the Exit key in the local window, not the Access Window!)

Next, name your Table PAYROLL-TABLE.

 

 

 

After you enter "EMP-NUMB" under Field Name, click the scroll-down-arrow (to the left) in the "Data Type" box.  The options "Text" and "Memo" appear.  What is the option just below "Memo"?
      Type the answer into box.

 
5   Do you know the difference between Design and Datasheet view?

Design view:

"Design View" shows the structure, that is, the format of the Table.  It has to be done first. It creates the skeleton, the format, of the table.

 

 

Datasheet view:

"Datasheet View"  shows the data in a Table format after it has been entered.

 

 

Note that "PAYROLL-TABLE" is defined below.   Under "Objects", you can select the type of Object by  pointing and clicking.  The object that you created (PAYROLL-TABLE) exists in several forms such as in Table, Query, and Form formats! 

Double-clicking PAYROLL-TABLE shows the table in Datasheet view (above).

 

 

 

 

Clicking the Design icon   displays the Table in Design view (of course).

 

 

 

Design view, as you should recall, CREATES THE STRUCTURE of the table.

 

 

Datasheet View displays the information in a Table Datasheet, which is like a spreadsheet, format.  Go ahead and change EASY's PAYRATE to 15.15 by typing it in.

 

 

 

 

 

 Another way to switch views is -- Double-click PAYROLL-TABLE to access it, then Click "View" on the top standard toolbar (giving menu on left) from which you can switch views.

 

 

 

What View -- Design or Datasheet -- will allow you to Sort your data by clicking the Sort Descending icon on the top toolbar?

   

 

   (Type answer into box)


6   Creating a query in Design View.   "Queries" select records from one or more tables in a database so they can be viewed, analyzed, and sorted, as well as creating new fields and performing calculations on existing fields.

This is what the query will look like when we are finished!

Note  Total =  PAYRATE * HOURS.   Check the totals.

 

One way to create totals is through the Query option.   The steps are:

1.  Click "Tables" under Objects.  Double-click "PAYROLL-TABLE". (return to Question #4, to create if you do not have it).

2.  Next, with PAYROLL-TABLE highlighted, click "Queries".

 

 

 

3.  Click "Create query in Design view".

 

 

 

4. Click "Add" to make PAYROLL-TABLE part of the Query form. (The design of the form is in the background.).  The  Exit key is how you finish a step or a procedure. 

 

 

 

 

 

 

Click on the Field box for each column, and using the scroll-down menu, complete this step to design the query for the 1st 4 fields. 

5.  Your first 4 fields should look like this.  The 5th field, the "Total" field, is an "expression", (formula) where  PAYRATE is multiplied by HOURS to create the "Total" field.  Write it as --  Total:[PAYRATE]*[HOURS] -- "Total" (a user-defined name)  followed by a colon (:), followed by  PAYRATE and HOURS in brackets with a multiply operator (*) between them!   Copy what I have done above for your 5th field.  Then  Exit  .  

 

6.  Upon hitting the Exit key, this screen appears.  You must save it,

 

 

 

7.  Change the Query Name to BASIC-PAYROLL-QUERY.   Exit.

 

 

  

   Type Total:[PAYRATE]*[HOURS] into the Box.   With no spaces. Use the bracket keys [  ] next to "P", the  asterisk  *  above the "8" key, and the colon :  above the semi-colon ; key . Type answer into box.


7  

   Running a Query / Modifying a Query

1. Select "Queries", double-click "BASIC-PAYROLL-QUERY"

(note from author:  There are at least (2) ways to learn:  One is by Rote, following orders literally, explicitly; the 2nd is "experimenting-to-learn",  trying, pushing the 'edge' all the time.

 

I am using Rote to get you started, to help you "get" the structure.  Then , experimenting, making sure that you can re-trace your steps.  Experimenting will create Success which builds Confidence which gives Empowerment which gives Mastery.  Do you understand?  Don't be afraid to experiment.)

2.  Double-clicking "BASIC PAYROLL QUERY" will generate this screen! 

 

 

3.  Now we experiment a bit.  The theory is "If the Query is based on the Table, then changing the Table should produce new results with the Query."  Let's test it:   Access the table "PAYROLL-TABLE" in Datasheet view, change one of the PAYRATE amounts to 100, exit, access Query, and run BASIC PAYROLL QUERY to test your hypothesis.   Did it work?  If not, carefully retrace your steps.

4.  Now we will expand the Query to account for Overtime Hours, which, as you know, are paid at time-and-a-half.  Review Payroll Calculator  to ensure you understand how Overtime calculations work. Up to the 1st 40 hours are at Regular Rate, and over 40 are 1.5 times Regular Rate.  Let's build on our BASIC PAYROLL QUERY query to include Overtime Pay.

5. First, open PAYROLL-TABLE, and change the HOURS and PAYRATE fields as shown.  (To make it easier to see the OT calculations.)

 

 

 

6.  We want to pay employees their Reg Pay for their 1st 40 hours, right?

 

 

 

 

Calculating Regular Pay:

If someone works < 41 hours, his Gross is his number of Hours * Payrate.   If someone works > 40 hours, his Gross is his Payrate * 40 (for the 1st 40 hours that he worked!)

Next,  open up BASIC-PAYROLL-QUERY in design view, and put this expression into the 6th column: 

 

 

Type the statement - REGPAY:IIF(HOURS>40,PAYRATE*40,PAYRATE*HOURS)  carefully into the form, noting that IIF (Immediate IF) is capitalized, with ( , ) commas between the conditions!  Explained below:

REGPAY:IIf ( HOURS > 40, 40 * PAYRATE, HOURS * PAYRATE )
  !     !        !            !             ! - - - -   No. Result if not true
  !     !        !            ! - - - - - - - - - - -   YES. Result if true
  !     !        ! - - - - -  - - - - - - - - - - - -   Condition Tested.
   !     !                                               (Is "HOURS" > 40?) 
  !     ! - - - - - - - - - - - - - - - - - - - - - -   Immediate IF statement
  ! - - - - - - - - - - - - - - - - - - - - - - - - -   User-defined Field Name

Restating:  REGPAY is a field name that you create; you know what IIF stands for, HOURS>40 is the condition you are testing for, and

if True (     over 40 hours), REGPAY = 40      * PAYRATE  and
if False (not over 40 hours), REGPAY = HOURS * PAYRATE  

(this is the same as the =IF statement in EXCEL)

Calculating Overtime Pay

Write the expression to generate OTPAY.  (Note that CHARLIE received 15 for 1 hour of Overtime.)

 

Entering this IIF statement for OTPAY

OTPAY: IIf([HOURS]>40,([HOURS]-40)*1.5*[PAYRATE],0)

Generates OTPAY shown above.

(Access will accept either iIF  or  IFF, and the brackets can be omitted.)

Calculating Gross Pay

Step-by-step:  Open the query which is called OT-PAYROLL-QUERY in design view.  In the next available column,  type  GROSSPAY: REGPAY+OTPAY .  Note EASY with 43 hours earns 40 hours @ $10 and 3 hours @ $15 for a total of $445.  Exit, save, then Run the Query by double-clicking it... Verify that your results equal the ones above.

Calculating NET PAY

Go to the Field row (top row) of OT-PAY-QUERY.. and the next column.  Using NETPAY as the result field, write the expression to calculate it. No spaces, all capital letters, don't use brackets around field names.
 

  
  Type expression into box. 

8  

Using the Help Feature:

This Help Screen comes up by pressing <F1> or the Help tab. The Access help feature generates 'empowerment', that is, if you have a some basic understanding, and an 'experiment to learn' (ETL)  attitude, and can get around in Help, you can strengthen your  skills and find the answers to questions that you have.  I also use Web search engines like Google to find answers.  ETL will accelerate your learning curve, build critical thinking skills, and help you solve problems.  Knowing how to find the answer is better in many ways than "having the answer" because in the Information Society that we live in, it is imperative to be able to solve new problems.  So spend time using Help, become "friendly" with it, learn its vocabulary, test its examples, and you will master this subject!

Select "Answer Wizard", the center of the 3 tabs on the top.  To find out about calculations in a query,  type "calculations" in the search box.  Press "Search".  A list of sub-topics comes up under the heading "Select topic to display".  

 

 

 

 

 

 

The first 2 topics on this list are: "About calculations in a query", and "Calculate a total or other aggregate values" -- What is the 3rd topic?

  
  This is a two word answer.  Type expression into box. 

 

9 

Question #9: Required.  Answer the following questions in the box below:

1. How do you create a desktop shortcut?

2. Run the Payroll Calculator, give ABLE 50 HOURS @  $20/HR, what is his NetPay?

3. What is the 2nd entry on the Insert Tab of the "File" Standard Toolbar?

  4. This button is on the Database Toolbar.  Hover over it. What word comes up?

 

5.  Name 3 data types that can go into the Data Type field?

6. This lesson used the following Field names,  EMPNO, EMPNAME, HOURS, PAYRATE, GROSSPAY, OTPAY, DEDUCTIONS, AND NETPAY.  Name the ones that ARE NOT part of the Database Table (ones that the user created).

7. Write the expression to calculate Overtime Pay if over 35 hours were Overtime.

8. What is the table used for?  What is the Query used for?

9. There are (3) parts of the Immediate IF statement, what is the 3rd part?

10. Write a short paragraph describing what you have learned from this lesson.Did it

If you answered these questions, you earned an an additional 200 points at 20 points each.  Enter xxxaccqu01 for your quiz (where xxx is your score), and  yyyacc-text for your answers (points for text answers). Max possible total is 400 points.  After you hit the the 'submit' button below, enter your total points on the A/P form -- http://auciello.tripod.com/deform7830.html 

  

  

After answering questions, Press the 'Submit' button to grade and post your work automatically.   


This is an example of a VIVA lesson (Visual-Interactive-Virtual Application) software developed and  (c) Copyrighted 2003, 2004, 2005 by Joseph Auciello.  For more information,  contact  Prof@auciello.net