ACCT 5342 Partnership Practice Assignments for Week 02

ACCT 5342 Accounting Information Systems

Last Revised on August 18, 2005

Partner Assignments for Week 2

Partners may study together on the assignments below.  Partners must also submit Attest Forms regarding the M&G Chapter 1 Quiz.

1.  You should check your email daily in case there are revisions of this assignment!

2.  Your partnership must meet with the ACCT 5342 teaching assistant.

• I recommend that each partnership import the document you are reading now into Microsoft FrontPage or Word and then added answers to this document.  This assignment file need not be turned in during class. 

• Prior to the next class, your partnership is required to have your assignment examined by the ACCT 5342 Teaching Assistant. Your files need not be fully complete, and the TA is not going to assign a grade to your work.  The TA will, however, try to help you in places where you are having difficulties. Try to meet with the TA during scheduled lab hours.  Details for contacting the TA are given in the syllabus.

• You are to report on your meeting with the TA in the ACCT 5342 partnership attestation form.  Each partnership must print and submit an attest.htm form before class begins.

3.  In some, but not all, classes a written or oral quiz will be given that relates to the assigned topics in this document.

Quizzes, examinations, and class discussions provide incentives to understand the answers to all questions.  Do not rely upon a partner's answer that you do not fully understand.  The question may come back to haunt you.

Table of Contents

Possible Quiz Questions for Week 02

Seen J:\videos

Other Microsoft Access Helpers and Links

Microsoft Access Helpers (in a new browser window)

• Some validation rules that use the LIKE comparison operator
  (in a new browser window)
• Input mask definition characters used to create an input mask
  (in a new browser window)
• The keyboard shortcuts for moving in fields and records
  (in a new browser window)
• The keyboard shortcuts for actions in a list box or a combo box (in a new browser window)

Web Links of Interest (in a new browser window)

Possible Quiz Questions for Week 02

This document contains the possible quiz questions for Week 02. Not listed here are possible questions from this week's assigned chapters from the Murthy and Groomer textbook and the Perry & Schneider textbook. In addition, you are responsible for the prior lectures on Access by Professor Jensen.

For practicing on these quiz question, I suggest that you create a JUNK directory in your Drive C hard drive. Then copy the Northwind Database file into that directory. The Northwind Database is on the TUCC network drive path J:\courses\acct5342\access\samples\nw2000\northwind.mdb . You may add and delete tables, queries, forms, etc. to this practicing database that you copy into JUNK. If you get a message that the Northwind Database needs to be "converted, " simply click the option to convert and save the database in the computer's Drive C JUNK directory. Further directions along for converting MS Access databases have been added to the section entitled Preliminary Course Activities in the ACCT 5312 Syllabus.

You can simply double click on the northwind.mdb file to make it run in MS Access.

Unfortunately, the file is too large (2.48 Mb) to put on a floppy disk and take to your home computer. I think there are some lab machines with CD recording drives. You can burn the Northwind database onto a CD-R or CD-RW disk for your home computer via a laptop. One problem, however, is that some of you will not have MS Access on your home computers.

If you want to save any of your work, you can save files to your assigned space on the student server on Drive Y.  Smaller files may be saved on floppy discs.

For the Perry and Schneider (P&S) textbook MS Access databases, you can use your CD that came with the text or you can convert the P&S Access files on the path J:\courses\Acct5342\Access\psbook\. Please note how to use either the Drive J version or the CD-ROM version of the database files by clicking on the following instructions:

-------> http://WWW.Trinity.edu/rjensen/acct5342/acct5342.htm#problem

M&G Assignment.
The M&G assigned online quiz must be graded prior to the start of class.  Your partner must be present when you take the quiz, and your partner must print and sign the attest.htm file.

Details regarding the M&G textbook are given in the syllabus.

CPA Examination Project Assignments
Each week, students are to complete the assignment scheduled at http://www.trinity.edu/rjensen/acct5342/proj02.htm 

This is like stacking of building blocks.  Each week a student completes an assignment to be stacked on to previous assignments of this project.  Every week, students turn in their stack of building blocks as of the class date.  Submissions of the DOC files should be attached to email messages to rjensen@trinity.edu 

Students are required to know about available services from the ITS department at http://www.trinity.edu/its/ and the Library at http://lib.trinity.edu/ 

Week 02 Assignments:  Most of these questions pertain to  J:\courses\acct5342\access\samples\nw2000\northwind.mdb
Note that I revised Microsoft's version of northwind.mdb by adding several queries that will make your life easier.

Warning before you begin:  If you are doing a project in MS Access, beware of the screen resolution.  If you author in a relatively high resolution, your tables, forms, graphics, etc.  may not fit neatly in computers that have lower screen resolution (i.e., users might have to scroll sideways to see the items that fit nicely into a higher resolution screen.)  This is a well-known problem in database design.  Designers of a database should plan ahead regarding the screen resolutions of persons that will be keying in data into tables and forms.  They should also consider the screen resolutions of other users such as users on laptop computers or users that make presentations from LCD data projectors that cannot project high screen resolutions.  Perhaps the best advice is for database designers to lower the screen resolutions of their computers when creating databases.

If a mdb database file will not open in your computer, copy the file into your computer, go to My Computer or Windows Explorer, right click on the file name, choose Properties, and then click on Unblock.

1. Datasheet Toolbar:  First open Microsoft Access.  Then click on <File, Open Database> and open the northwind.mdb Northwind Database illustration in Microsoft Access. The Northwind Database is on the TUCC network drive path  J:\courses\acct5342\access\samples\nw2000\northwind.mdb
   Click on menu choices (View, Toolbars) and make certain the Datasheet Toolbar is visible. Be able to explain the role of each icon in the Datasheet Toolbar.
   
   [Hint: you may want to click on menu choice "Help, What's this?". The cursor will change to a question mark. You can click on any icon in the Database Toolbar to see the definition of that icon. Terms in the definition will help you search in the menu choice Help for more details.]
   
   
   
   
   
   
   
2. Navigating in a Table: For help here see the keyboard shortcuts for moving in fields and records (in a new browser window). Click on the Tables tab in the Northwind Database. Then click on the Order Details Table. Click on menu options (View, Datasheet). Then maximize the table in the window if it does not take up all space in the Access window. Records are listed in the rows and fields are listed in the columns. What are two ways to choose a cell in a given record and a given field?
   
   [Hint: arrow keys work]
   
   
   
   What is the role of the Tab key?
   
   
   
   Go to the cell in the third record in the third field. What is the role of the key combination (Shift, Tab) versus (Tab)? What is the difference between the keys (Up Arrow) versus (Ctrl, Up Arrow)?
   
   
   
   How about (Down Arrow) versus (Ctrl, Down Arrow)?
   
   
   
   What is the difference between (Home) versus (Ctrl, Home) and (End) versus (Ctrl, End)?
   
   
   
   What about (Page Up) versus (Page Down)?
   
   
   
   Note that the Datasheet mode is not usually the best way to view record contents. Later in the course, we will show how record contents are usually best viewed in custom designed "forms" rather than in the datasheet mode.
   
   
   
   
   
   
   
3. Navigating in a Table: For help here see the keyboard shortcuts for moving in fields and records (in a new browser window). Open the Order Details Datasheet. Explain the record navigation buttons near the lower left corner of the datasheet.  What is the role of the F5 key in connection with those buttons?
   
   
   
   
   
   
   
4. Navigating in a Table: For help here see the keyboard shortcuts for moving in fields and records (in a new browser window). In the Order Details Datasheet, how can you search for selected words in a given field? What are four ways to launch the Find dialog box?
   
   [Hint: one of these options entails (Ctrl, F) and the other is the alternate (right) key option of the mouse.]
   
   
   
   What is the difference between searching the "Whole Field" versus "Any Part of Field" options?
   
   
   
   
   Why do you get a message "The search item was not found" when you know the search string is contained in one or more cells?
   
   [Hint 1: It's that "Any Part of Field" thing.]
   
   [Hint 2: It may be that buggy thing about about MS Access.  For example, if you search for sucre, you would assume that, if you do not click the "Match Case" opion,  the program would search for sucre, Sucre, SUcre, SUCRE, etc.  But this is not the case in the version of MS Access on my computer today.  I can only find sucre if I click the "match case" option and have no capital letters in sucre.  This is Reason 1,979 why I hate MS Access.  I cannot find a way to make the search case insensitive.  Does your version of MS Access let you be case insensitive?]
   
   
   
   
   
   
   
5. Navigating in a Table: How can you search in all fields using the Find dialog box? 
   
   [Hint: There is an option for this in the Find dialog box. For example, search the entire Customers Table in the datasheet view for Rancho Grande.]
   
   
   
   
   
   
   
6. Navigating in a Table: For help here see some validation rules that use the LIKE comparison operator (in a new browser window). How can you search for wild card strings using the Find dialog box? To try this option out in Northwind's Order Details Datasheet, select the Unit Price field. Then click on the toolbar button for Filter by Form. Then search repeatedly for *0 versus 1# prices? What are you finding with this option?
   
   [Hint 1: the search code uses the word "Like" as follows: Like "*0" versus Like "1#".  Click here to see some validation rules that use the LIKE comparison operator.]
   
   [Hint 2:  When using the Find utility and simple filtering, you usually do not type the word Like or use the quotation marks.  For example type in *0 instead of Like "*0".  However when you use the Advanced Filters and query expressions, you type in the operator such as Like and the quotation marks.]
   
   
   
   
   
   What do the following advanced filters generate? (See menu choice (Records, Advanced Filter/Sort)
   
   Like "*4"  
   Like "*#4"
   Like "*##.4"
   Like "*.4"  
   Like "*.40" does not work.  Darned if I know why!   This is Reason 1,980 why I do not like having to teach MS Access,
   
   
   
   
   
7. Table Structure and Viewing: If all the text in a cell is not visible, how can you read the text without widening the field (column)?
   
   [Hint: Select a cell and click on the (Shift, F2) keys. Note that it may be better to view fields that are quite wide in a form view rather than the datasheet view.]
   
   
   
   
   
   
   
8. Table Structure and Viewing: How can you use the mouse to change the viewing width of any table's field?
   
   
   
   What happens if you double click the mouse on the edge of the top cell that defines the field (column) name?
   
   [Note that this option will not work properly unless the cursor changes from its default symbol to the expand/contract symbol.]
   
   
   
   How can you simultaneously do the same things for multiple columns?
   
   
   
   
   
   
9. Creation of a Table: How can you create a Microsoft Access table?
   
   [Hint: If you close all open tables, note the menu choice (Insert, Table) followed by a choice called Table Wizard.]
   
   
   
10. Design View Options of a Table: In the Northwind Database, open the Employees table in the design view. Then click on the HomePhone field. The input mask is blank. What is the appropriate mask for a United States phone number?
    
    [Hint: Open a web browser and view the Page (Document0 at http://WWW.Trinity.edu/rjensen/acct5342/helpers1.htm#mask and view the input mask coding symbols.]
    
    
    
    
    What is the main purpose of an input mask?
    
    [Hint: Use the search term "input mask" in the menu choice (Help, Index) of Microsoft Access.]
    
    
    
    
    
    
    In the Customer ID field in a Northwind database, the input mask is specified as ">LLLLL". What major things are accomplished by this input mask
    
    
    
    
    
11. Table Design Editing: Changing the viewing width of a field (column) does not change its basic designed width. How can you change its basic design width?
    
    [Hint: Shift from the datasheet view to the design view by either clicking on the far left icon in the Datasheet ToolBar or clicking on the menu choice View. Then select a field and change the field size. Note that the Datasheet mode is not usually the best way to view record contents. Later in the course, we will show how record contents are usually best viewed in custom designed "forms" rather than in the Datasheet mode.]
    
    
    
    
    
    
    
12. Table Structure and Viewing: Shift back to the datasheet view of Northwind's Order Details Datasheet. How can you change the row (record) height of all rows simultaneously without changing the font style or size?
    
    
    
    How can you change the font style and size?
    
    
    
    
    
    
    
13. Table Structure and Viewing: How can you change the ordering of the fields in a table?
    
    [Hint: You must hold the mouse button down for this maneuver. Note that changing field orderings in the datasheet view does not change the underlying field ordering in the table. To change the underlying field ordering, you must switch from menu choices (View, Datasheet) to (View, Table Design).]
    
    
    
    
    
    
    
14. Data Filtering in a Datasheet: What are three of the main differences between using the Find dialog box and data filtering in a table?
    
    [Hint: One way entails using the alternate (right) mouse click.]
    
    
    
    
    
    How can you activate and clear the filtering options using the Datasheet Toolbar? For example, in the Unit Price field of Northwind's Order Details Datasheet, how can you simultaneously view all products priced at $16.80?
    
    
    
    
    How can you quickly restore the entire all records in the datasheet view?
    
    
    
    
    What are three ways to activate and clear filters?
    
    
    
    
    
    
15. Data Filtering in a Datasheet: What are the main differences between using the Find dialog box and data filtering in a table? How can you activate and clear the filtering options using the Datasheet Toolbar? For example, in the Unit Price field of Northwind's Order Details Datasheet, how can you simultaneously view all products priced at $16.80? How can you quickly restore the entire all records in the datasheet view? What are three ways to activate and clear filters?
    
    [Hint: One way entails using the alternate (right) mouse click.]
    
    Menu choices (Records, Filter)
    Icons in a toolbar
    Menu choices in alternate (right) mouse clicks
    
    
    
16. Data Filtering in a Datasheet: Click on menu choice (Records, Filter, Advanced Filter/Sort). How do you filter (view) all records in Northwind's Order Details (or Order Type) Table that have a Unit Price of $30 or more and a quantity of 10 or less? Sort the data first in descending order as to price and then as a secondary sort use ascending order for the quantity. Use >= and <= notation and note that in the Advanced Filter/Sort window you can activate the filtering and sorting by using the Filter by Form button or the menu choice (Records, Filter, Apply/Filter Sort). In the Datasheet window, you can activate the filtering with the Data Form icon in the Datasheet Toolbar to view the filtering criteria. Use the alternate (right) mouse button to activate "Apply/Filter Sort".
    
    
    
    
    
    
    
    
    After you are finished viewing the filtered datasheet, how can you restore all records to the datasheet view?
    
    
    
    
    
    
17. Data Indexing: In very large tables, it may be inefficient to search by the Find Dialog Box or by data filtering without the use of indexes. Indexes can be defined in the Design mode of a table (not the Datasheet mode). Most fields can have duplicate entries for multiple records (e.g., hundreds of customers might have "CA" in the "State" field for all customers from California. In those instances, allow for duplicates when designating a field as indexed. A single index can be set in the design window.  Most fields must be indexed manually in the design or index windows. Multiple indexes require that you open up the Indexes window using the menu choice (View, Indexes) or click on the Table Design Toolbar icon for Indexes.   What field is indexed by default?
    
    
    
    
18. Data Indexing in a Datasheet: What fields in the Order Details Table are designated as primary key fields?
    
    [Hint: Shift from the Datasheet mode (view) to the Design mode (view), click on a primary key field (row) and look near the bottom of the Design window.]
    
    
    
    What property is required for any primary key field? Note that by default, primary key fields are "indexed" such that sorting will be according to the priority ranking of primary key fields unless the user chooses to sort on the basis of other fields. Other fields can be indexed to take priority in sorting.
    
    
    
    
    
    
    
19. Data Indexing: In very large tables, it may be inefficient to search by the Find Dialog Box or by data filtering without the used of indexes. Indexes can be defined in the Design mode of a table (not the Datasheet mode). Most fields can have duplicate entries for multiple records (e.g., hundreds of customers might have "CA" in the "State" field for all customers from California. In those instances, allow for duplicates when designating a field as indexed. A single index can be set in the design window. Only the primary key field is indexed by default. Other fields must be indexed manually in the design or index windows. Multiple indexes require that you open up the Indexes window using the menu choice (View, Indexes) or click on the Table Design Toolbar icon for Indexes.
    
    
    
    
    
    
    
20. Query Basics: A query may be a selection or action query. A selection/select query "retrieves" a subset of records from a table without changing the table. An action query changes a table with an edit, addition, or deletion of a record or field. A query is analogous to a formula or "clip" that selects a subset of records and fields from a table. However, the underlying data are only stored in the table file. A query is only stored as a "formula."
    
    Click on the Query tab in the Northwind Database. Then open the Category Sales for 1994 Query. How can you see the design of this query?
    
    [Hint: Click on the menu choice View.]
    
    
    What design differences do you note between the Category Sales for 1994 Query and the Employee Sales by Country Query?
    
    
    
    
    
    
    
21. Query Basics: Open the Query Design Toolbar and note the definitions for each icon in that toolbar.
    
    
    
    
    
    
    
22. Query Basics: What are some of the main differences between queries versus filters in databases?
    
    
    
    
    
    
    
23. Datasheet Editing: Open the Northwind illustration database in Microsoft Access. Click on the Tables tab in the Northwind database. Then click on the Order Details Table. Click on menu options (View, Datasheet). Then maximize the table in the window if it does not take up all space in the Access window. Records are listed in the rows and fields are listed in the columns. Use the F5 key to navigate to Record 105 of the table. Select the Unit Price of Record 105. What is the difference between the Navigation mode and the Insertion Point (Editing) mode for that Unit Price?
    
    [Hint: Select the Unit Price cell for Record 105 and toggle the F2 key to switch between the Navigation and Editing Modes. You can use keys (Shift, F2) to zoom the field for editing.]
    
    
    
    
    
    
    
24. Datasheet Editing:What are three ways to delete an entire record?
    
    [Hint: After selecting the record, one way entails using the alternate (right) mouse button.]
    
    
    
    
    
    
    
25. Datasheet Editing: To add a record to Northwind's Order Details Table in the datasheet mode, you must first navigate to the end of the table. A new (blank) record appears after the last recorded row. What are three ways to navigate to that new record?
    
    [Hint: One of the ways uses the (Ctrl, End) keys and another way uses an icon in the Datasheet Toolbar.]
    
    
    
    
    
    
    
26. Datasheet Editing: To add a field to a table you must go to the design view. If your table already has data typed in for records (rows), adding a new field (column) creates special problems. What are these problems and why is it best to avoid adding new fields?
    
    
    
    
    
    
    
27. Table Copying into Other Databases: One or more tables from a "source" database can be copied and pasted into a "destination" database. How is this accomplished?
    
    [Hint: Do not open the table when copying the tables from the source database or pasting it into the destination database. It is also possible to copy the table without the data (but with the table's design) into the same source or destination databases. Obviously, its name must be changed if it is pasted in the source database.]
    
    
    
    
    
    
    
28. Creating a New Database: Exit the Northwind Database and click on (File, New Database). You can choose from a number of blank templates that were designed by Microsoft or you can start a new database from scratch. The first thing you must do with your new database is save it to a directory. If you are just practicing, please save it to the TEMP or some other temporary directory on the computer.
    
    
    Try starting a new database from scratch rather than using a database template. Click on the Tables tab and note the New button on the right side. What is the role of the Table Wizard?
    
    
    
    
    
    
    
29. Creating a New Table: Suppose that you do not use the Table Wizard. After clicking on the New button on the Table tab, click on the Datasheet View. Your new table will not have any fields defined. How can you define fields?
    
    [Hint: You must save your table under a unique name for this database and then proceed to the Design view instead of the Datasheet view.]
    
    
    
    A primary key field is a field that will have unique contents (e.g., employee number, social security number, row number, etc.) rather than contents that might be identical for more than one record (e.g., last name Smith or first name Sarah). You may prefer to declare a primary key field and then give it the AutoNumber as a data type. It does not matter if you assign the primary key to the wrong type of field as long as you correct this later on by using the Primary Key icon in the Design Toolbar to reassign the primary key. To designate more than one field as a primary key field, click on the primary key option and then hold down the Ctrl key while clicking on their field selectors. The default sort of the table is on the primary key(s). Indexed fields and custom sorts may later change the sorting.
    
    
    
    
    
    
    
30. Creating a New Field: Field names cannot exceed 64 characters including blank spaces, although it is best to use much shorter names. Field names cannot include some characters like periods, square brackets, quotation marks, or exclamation marks. After you assign a name to a new field, click on the adjoining Data Type cell. Click on the arrow button that appears on this cell. For each option that appears, describe the circumstances in which you would choose that particular data type.
    
    
    
    Note that the Field Properties change with each of the data type options. Be able to explain what properties to assign for each data type. For example, if the data type is NUMBER, one property is a discretionary choice as to whether to assign a default value. When might you choose to assign a default valueand why?
    
    
    
    
    
    
    
31. Creating a New Record: How can you add data in the Datasheet versus Form views?
    
    [Hint: You must have completed the entry in the previous record and be at the end of the record listings in the Datasheet view rather than the Design view. To get to the end of the records you may use an icon in the Datasheet Toolbar. A fast way to get a Forms view from the Datasheet view entails clicking on (View, Toolbars, Forms Toolbar).]
    
    
    
    
    
    
32. Things to Know from P&S: You should know how to do the following from pages in the Perry and Schneider textbook:
    
    Finding character strings using the * wild card
    
    Adjusting field widths using the right (alternate) mouse click
    
    Sort records with menu choices (Records, Sort) and note that there are shortcut icons for sorting.
    
    Filter by selection (e.g., all records having a California attribute) and removal of a filter with a right (alternate) mouse click
    
    Filter by form (in this case you type in the filtering criteria) and removal of the filter with the Remove Filter icon.
    
    Compare Filtering by Selection vs. the aryCaliforniaClients query. (Note that you can save filtering outcomes as queries).
    
    How can you create a query by by example?
    
    Pages 76-101: will be taken up later in this course.
    
    
    
    
    

I did not assign any exercises from the P&S book.  If you want to practice some exercises, you should note the following.

If you read the file from the CD-ROM that came with your textbook, the file will have "Read Only" properties.  You can read the file into your hard drive and go to Windows Explorer.  Select the file in Windows Explorer and then remove the "Read Only" property using menu choices <File, Properties>.

In Drive J, I removed the "Read Only" property. Suppose that you want to read and revise the P&S Chapter 4 database on the path J:\courses\Acct5342\Access\psbook\Ch02\Ch02.mdb. Since you cannot write to Drive J, you must copy the Ch02.mdb file into the path C:\Junk\Ch02.mdb. After it is in the Junk folder, all you havet to do is double click on the file in Windows Explorer.

Exercises 1 and 2 are optional.
[Hint for Exercise 1 in Chapter 2: First sort by state. Then try menu choices (Records, Filter, Advanced Filter/Sort). You can drag the city field into the sort and choose (Sort, Ascending).]



[Hint for Exercise 2 in Chapter 2: You can only perform this exercise if the database file is not restricted to "Read Only." I have removed this restriction on files in Drive j. However, if you use your CD disc you will have to copy the ch02.mdb file into your Drive C and remove the "Read Only" attribute. Then open the database and do the following:

1. Click on (Queries tab, New button, Simple Query Wizard button)
2. Chose tbtClient in the Tables/Queries window
3. Select the Client Name field in the Available Fields window
4. Use the right arrow button to move the Client Name field into the Selected Fields window
5. Accept the name TbtClient query and click the remaining options to finish the Wizard
6. When the tbtClient query appears in the datasheet view, sort the one column.]

Bring your P&S textbook to every ACCT 5342 class.   You may lose out on some course points if you cannot access this book in class.