Microsoft® Access® Exercise
Lori Mixson
CIS/206
August 15, 2011
Rosalind Bailey

Table of Contents
Microsoft® Access® Exercise 3
Scenario 1 3
Scenario 2 4
Scenario 3 5
References 6

Microsoft® Access® Exercise * Scenario 1 * Jack, a veterinarian, has experienced tremendous growth within his business. Currently, all animal records are shelved in folders and are sorted alphabetically by the owner’s name. Jack is ready to rid himself of the time and space these files take and has inquired about Access®. He has asked about the key functions of using Access® and how a table, query, primary key, and report are important to his database needs. * This is a very good scenario for how really well Microsoft® Access® is such a wonderful tool for a growing business. Incorporating Microsoft® Access® will be perfect for building the tables and databases that will convert much of the red tape into a more condensed and convenient system. * The first thing to explain would be how tables combined together will make life easier and far more organized. By connecting the tables and creating Databases, using a primary key and relationships to connect the tables information will be at their fingertips. * Another feature that Access® has is the SQL® servers this makes conducting business across networks much easier as well. When the business is expanding into multiple branches the use of this feature would be something to keep in mind. * Finally walk the customer through setup and explain the features after the setup has been completed. Should there be any other issues tackle them one at a time until fixed and the customer is confident and ready to go forward on their own. * *

* Scenario 2 * Frank has created a database to inventory his customers’ past due accounts to ensure that he receives payment. He has created two tables: one for the customers’ demographics and the other for the type of services rendered. When he creates a report, the customers’ demographics are all that are displayed, not the amount that is past due. My approach would be to identify the fields in the services table, to establish if amount due can be calculated from the data entered and determine the structure of the query upon which this report is based. The challenge is terminology, since Frank is new to Access® so he will possibly have trouble describing the objects. To handle this begin by asking him to open the database while on the phone and describe what he observes. Next, would be to explain the items he describes. The probable cause of the problem are improper links (relationships) in the query. Frank may need to read the information in the tables to me then recreate the tables on my system with 10 records each so that I am able to duplicate his query to find the problem. Once we have determined the cause of the problem, ask him to make the necessary changes in his query (and/or tables) while on the phone together. Next would be to show him how to verify that the new query works properly. My first attempt would be to talk Frank through it over the phone. If that don’t work then I would send him the duplicate database on my system so I can explain what he's looking at and walk him through it a step at a time.

*

* Scenario 3 * Stephanie has been using the same database and queries to track her commemorative plate collection. She has recently discovered that her plates have serial numbers and has decided to record this within her Access® database. When she replaced each plate’s ID with the appropriate serial number, she was unable to use her queries. She has called the support desk wanting to know why this has happened. Stephanie also wants to know if each serial number is unique or similar to the assigned ID number she has used in the past. Stephanie is apparently experienced in using Access®. Hopefully, she made the serial number change after making the backup copy of the database. The reasons that queries would not work would be if she changed a field name, database name, or the serial number is a different data format then the original plate ID. She could have used Auto number for the plate ID, which is a long integer. Furthermore, since the serial numbers usually have letters in them, she may have changed this field to a text data type. Therefore, any query links to the ID number would not work due to a data type mismatch. To compare the serial numbers to each other and to the original plate ID, she would need to add the plate ID field, set it back to Auto number (or reset whatever she used to set them). Another way would be to open the original database backup and add the serial number field and enter the information. To ensure that the serial numbers are unique, she only needs to run a Find Duplicates query on the table. This should be done before making the field a primary key, to prevent losing records. If no duplicates are found, this field can be keyed.
References
Bunzel, T. (2007). Master Visually® Microsoft® Office 2007. Hoboken, NJ: Wiley.