Access

Access Tutorial 1: Mini-Case 1

Scenario: Nick, the owner of a small shop catering to the local fishermen, decided to expand his product line when the lake started to dry up after a drought. Blessed with a large parking area, he decided to add car sales, and thus his business was reborn as Nick's Bait, Tackle, and Used Cars. Determined to keep his used car inventory as organized as his assortment of shiners and larvae, Nick decided that he needed to build a database. He'd start small with one table and then expand it as necessary. Nick realized that it's generally better to create a design with all of the tables that would be needed first, but he figured that a one-table database would be sufficient for a while, and anyway it would be a good way to learn how to use Microsoft Access.

Your Task: Create an Access database with a table called vehicles to keep up with the list of cars on the lot of Nick's store.

Create your vehicles table to hold data listed in Access Table 1-4 for each vehicle. Set the various parameters for the field to match the description of the field provided.

Create a form for entering data into the vehicles table. Use your form to enter the data listed in Access Table 1-5 :

Access Tutorial 1: Mini-Case 2

Scenario: Since getting their digital cameras, the Couleur family takes a lot of pictures. As the family computer expert, Amy is usually the one who has to upload the photos to the family computer and keep them organized. She noticed that everyone seems to take and keep a lot more photos now that everything is digital. Amy would like to create an application to help her keep track of the photos that her family takes. She wants to be able to search based on date, family member, or location to find the file names of the photos on her computer. Having just studied Access at school, Amy has decided that a database might be the perfect application for storing her photo information.

Your Task: Create an Access database with a table called photos to keep up with the list of photos of Amy's family.

Create your photos table to hold the data listed in Access Table 1-6 for each digital photo. Set the various parameters for the field to match the description of the field provided.

Create a form for entering data into the vehicles table. Use your form to enter data. Enter about 10 records. An example record is provided in Access Table 1-7 .

Access Tutorial 2: Mini-Case 1

Scenario: Canard and Sons, founded in 1980, was a small, family-run maker and distributor of gourmet pâtês. In 1988, the company was sold to MultiBrands, Inc. A new management team was installed, but the conglomerate retained the original name, which had become quite well known among pâté connoisseurs. Since then the enterprise has grown from a small family business to one that employs over 30 people. Toby Childers, the director of human resources, has long maintained a list of the company's employees in the form of a single-table Access database. Using the database, Mr. Childers can answer many questions that he and other managers might have about the company's associates.

File: Download and open the Access database ACC_2_MC_Employees.accdb.

Your Task: Use queries with the Access database ACC_2_MC_Employees.accdb to answer the following questions:

Make a report based on salary30kto50k. Group the data by the birth date and sort the data within groups by salary.

Access Tutorial 2: Mini-Case 2

Scenario: Pepé L'Arrange is organizationally challenged. He's very talented and when he gets around to doing anything he generally does it very well. The problem is that he just can't keep up with what it is he needs to do next. He's decided that he needs to take the database skills that he learned in class and build himself a tool that he can use to organize his life. Unfortunately, Pepé has forgotten to get it built, so he needs your help. What's needed is a database table that Pepé can use to keep up with the tasks that he needs to do and when they need to get done. In a moment of organizational inspiration, he did manage to make a list of the fields that are needed in this table. These are shown in Access Table 2-3 .

File: There is no file to download for this case.

Your Task: Build a database with the table described in the Scenario. Create a form that Pepé can use to enter tasks. Add queries to the database that will help Pepé by answering the following questions.

Also, create a report based on one of the queries that will show the tasks for the next week in order of due date and priority. You should make up data and enter it into your table to test the components that you create.

Access Tutorial 3: Mini-Case 1

Scenario: Breanna had always been a voracious reader. She read just about anything she could get her hands on: mysteries, romance, biographies, science fiction. You name a book and there's a high probability that Breanna has read it. She also owns many of the books that she's read. Breanna is about to go off to college in another city, and her vast book collection has become a bit of a problem. She can't take them with her—too much to transport and too many to fit in her dorm room. In addition, her entrepreneurial parents are eager to rent out her room. The solution? Store the books in the attic. But, before she does, Breanna wants to take an inventory and store her book lists in a database rather than keep paper-based lists.

Your Task: Help Breanna by creating the Access database tables shown in the Access data model in Access Figure 3-46 . Use appropriate data types for each field. Fields that are bold in the figure represent the primary keys for each table. (Note: both fields in the authorISBNs are bold. This is known as a compound primary key. It means that the combination of the two field values should be unique for each record.) Use the Access Relationship window to add the relationships between tables. Remember to enforce referential integrity.

Create data entry forms for your database. Make one form for each table.

Enter appropriate data into your database using the forms that you created. You may make up the data, or you can find inspiration using your personal library.

Create Access queries that will answer the following questions:

Create a report based on the query bookList. Group by authors' last names. Subgroup by publisher name.

Access Tutorial 3: Mini-Case 2

Scenario: Melita Voiture is the proud owner of a car service in the big city. Her cars are hired out on a daily basis to her customers. In addition to Melita's fleet of cars, she owns a garage with several mechanics on staff to maintain the fleet. Melita's customers prefer their cars in perfect working order. While Melita covers the costs of preventive maintenance, other repairs are paid for by the customer. Melita needs to keep track of when the cars are hired out and to whom. In addition, she wants to keep careful track of how often cars have been maintained, who worked on them, and the details of the work performed. In order to keep up with this data, she's commissioned you to create a database based on the model shown in Access Figure 3-47 .

Your Task: Create a database based on Melita's data model. Your database should include the tables, fields, and relationships shown. Choose appropriate data types and set input masks and lookup fields as appropriate. In addition, create queries to answer the following questions:

Access Tutorial 4: Mini-Case 1

Scenario: MyFace.com, a social networking Web site popular with the cool kids, just bought a database of student names from the local community college for use in marketing the features of their site. They hope to mine the data to identify trends that they can use to improve their services and attract more eyeballs. For example, if they were to find out that there is a significant number of students majoring in fifteenth-century Asian art, they could add provocative photos of Ming vases to their Spring Break pages. The folks at MyFace.com are willing to provide an extra 10Mbytes of Web storage and status as a gold platinum user to anyone who can help them query the database to answer the following questions.

File: Download and open the Access database ACC_4_MC_Students.accdb.

Your Task: Use SQL to query the Access database students.accdb to answer the following questions:

Access Tutorial 4: Mini-Case 2

Scenario: Jacquey Sparrow works for a small Web2.0 startup. A new feature that she wants to add to the Web site is an online “link-saver” application. This application will let people store bookmarks online so that a user may log in to access links to his or her favorite Web sites from any computer connected to the Internet. As a first step, she has created a database that can be used to store the links. She wants to test the data model before writing instructions for the Web developers. To do this, Jacquey needs to run SQL queries against the data model that might be implemented in the Web application.

The initial design for the Web application includes (1) the links database, (2) a main page that shows a brief introduction to the purpose of the link-saver Web page and links users to other pages; (3) a new-user function that allows a new user to create an account; (4) a login function for an existing user to log in; (5) a logout function for a logged-in user to log out; (6) a page where users may view their personally stored bookmarks, a new bookmark can be added, and a bookmark may be deleted.

File: Download and open the Access database ACC_4_MC_LinkSaver.accdb.

Your Task: Use SQL to query the Access database linkSavers.accdb to test the following tasks or answer the following questions:

Part C Interpretive Questions

Based on your work in Part C, answer the following questions:

1.  

When querying a database using QBE, what are criteria used for?

2.  

When using QBE, what is the difference between asking, “What are all the classes that Employee A and Employee B are teaching?” and “What are all the classes that both Employee A and Employee B are teaching together?”

3.  

Although we mentioned that SQL was beyond the scope of this project, you can look at the SQL view for any of your queries. It is one of the choices on the same View menu that you used to select the design view after running your queries. Look at the SQL for Query 2 above. Can you make sense of the SQL query? Look for things like table and field names. You can even change the September query to an August query by changing only one character—try it!

Part D Interpretive Questions

Based on your work in Part D, answer the following questions:

1.  

What other reports would you suggest that Isaac should create for Ms. Johns to help her understand and manage the courses and classes that Wild Outfitters provides to its customers?

2.  

We have mentioned several Access objects throughout this project. Did you notice any additional objects when you were working in the Design view? What were some of these objects, and how did you know that you were looking at an object?