Reference no: EM13909704

It's chapters 1-3 , I need the Apply your knowledge, Extend your knowledge and one of the In Labs done for each chapter. This is less work than I asked for the first time. Please let me know your response.

That is the direct link to the student data files and I have attached the homework questions.

https://www.cengagebrain.com/cgi-wadsworth/course_products_wp.pl?fid=M20b&product_isbn_issn=9781285169033&token

Apply Your Knowledge Reinforce the skills and apply the concepts you learned in this chapter.

Adding a Caption, Changing a Data Type, Creating a Query, a Form, and a Report Note: To complete this assignment, you will be required to use the Data Files for Students.

Instructions: Cosmetics Naturally Inc. manufactures and sells beauty and skin care products made with only natural ingredients. The company's products do not contain any synthetic chemicals, arti-ficial fragrances, or chemical preservatives. Cosmetics Naturally has a database that keeps track of its sales representatives and customers. Each customer is assigned to a single sales rep, but each sales rep may be assigned to many customers. The database has two tables.

The Customer table contains data on the customers who purchase Cosmetics Naturally products. The Sales Rep table contains data on the sales reps.

You will add a caption, change a data type, create two queries, a form, and a report, as shown in Figure 1-83 on the next page. Perform the following tasks:

1. Start Access, open the Apply Cosmetics Naturally database from the Data Files for Students, and enable the content.

2. Open the Sales Rep table in Datasheet view, add SR # as the caption for the Sales Rep Number field, and resize all columns to best fit the data. Save the changes to the layout of the table and close the table.

3. Open the Customer table in Design view and change the data type for the Postal Code field to Short Text. Change the field size for the field to 5. Save the changes to the table and close the table.

4. Use the Simple Query Wizard to create a query for the Customer table that contains the Customer Number, Customer Name, Amount Paid, Balance, and Sales Rep Number.

The query is a detail query. Use the name Customer Query for the query and close the query.

5. Create a simple form for the Sales Rep table. Save the form and use the name Sales Rep for the form. Close the form.

6. Create the report shown in Figure 1-83 for the Customer table.

The report includes totals for both the Amount Paid and Balance fields. Be sure the totals appear completely. You might need to expand the size of the total controls. Move the page number so that it is within the margins. Save the report as Customer Financial Report.

7. If requested by your instructor, add your last name to the tide of the report, that is, change the title to Customer Financial Report LastName where LastName is your actual last name.

8. Compact and repair the database.

9. Submit the revised database in the format specified by your instructor.

10. Q How would you change the field name of the Balance field in the Customer table to Current Balance?

Using a Database Template to Create a Contacts Database

Note: To complete this assignment, you will be required to use the Data Files for Students. Visit wn.cengage.comkt/studentdownload for detailed instructions or contact your instructor for information about accessing the required files.

Instructions: Access includes both desktop database templates and web-based templates. You can use a template to create a beginning database that can be modified to meet your specific needs.

You will use a template to create a Contacts database. The database template includes sample tables, queries, forms, and reports. You will modify the database and create the Contacts Query shown in Figure 1-84.

Pei form the following tasks: I. Start Access.

2. Select the Desktop contacts template in the template gallery and create a new database with the file name Extend Contacts.

3. Enable the content. If requested to do so by your instructor, watch the videos in the Getting Started with Contacts dialog box. Close the Getting Started with Contacts dialog box.

4. Close the Contact List form.

5. Open the Contacts table in Datasheet view and delete the Fax Number field and the Attachments field in the table. The Attachments field has a paperclip as the column heading.

6. Change the data type for the ID field to Short Text, change the field name to Contact ID, and change the field size to 4. Change the column width so that the complete field name is displayed.

7. Save the changes to the Contacts table and close the table.

8. Use the Simple Query Wizard to create the Contacts Query shown in Figure 1-84. Close the query.

9. Open the Phone Book report in Layout view. Delete the control containing the date. Change the title of the report to Contact Phone List.

10. Save the changes to the report.

11. If requested to do so by your instructor, add your first and last names to the end of the title and save the changes to the report.

12. Submit the revised database in the format specified by your instructor.

13. e a. Why would you use a template instead of creating a database from scratch with just the fields you need?

b. The Attachment data type allows you to attach files to a database record. If you were using this database for a job search, what specific documents might you attach to a Contacts record?

Analyze, Correct, Improve

Analyze a database, correct all errors, and improve the design.

Instructions: Analyze SciFi Movies is a database containing information on classic science fiction movies that your film professor would like to use for teaching. The Movie table shown in Figure 1-85 contains errors to the table structure. Your professor has asked you to correct the errors and make some improvements to the database. Start Access and open the Analyze SciFi Movies database from the Data Files for Students.

What decisions will you need to make when creating queries?

Use these guidelines as you complete the assignments in this chapter and create your own queries outside of this class.

1. Identify the fields by examining the question or request to determine which fields from the tables in the database are involved.

2. Identify restrictions or the conditions that records must satisfy to be included in the results.

3. Determine whether special order is required.
a) Determine the sort key(s).
b) If using two sort keys, determine the major and minor key.
c) Determine sort order. If there are no words to imply a particular order, you would typically use Ascending,
d) Determine restrictions, such as excluding duplicates.

4. Determine whether more than one table is required.
a) Determine which tables to include.
b) Determine the matching fields.
c) Determine whether sorting is required.
d) Determine restrictions.
e) Determine join properties.

5. Determine whether calculations are required.
a) For individual record calculations, determine the calculation and a name for the calculated field.
b) For group calculations, determine the calculation as well as the field to be used for grouping.

6. If data is to be summarized and the data is to be grouped by two different types of information, create a crosstab query.

Apply Your Knowledge

Perform the following tat:
1. Create a query for the Customer table and add the Customer Number, CuStomer Name, Amount Paid, and Balance fields to the design grid. Add a criterion to find all customers whose names start with the letter C. Run the query and then save it as Apply 2 Step 1 Query.

2. Create a query for the Customer table and add the Customer Number, Customer Name, Amount Paid, and Sales Rep Number fields to the design grid. Son the records in descending order by Amount Paid. Add a criterion for the Sales Rep Number field that allows the user to enter a different sales rep each time the query is run. Run the query and enter 41 as the sales rep number to test the query. Save the query as Apply 2 Step 2 Query.

3. Create a query that joins the Sales Rep and Customer tables. Add the Sales Rep Number, Last Name, and First Name fields from the Sales Rep table and the Customer Number and Customer Name fields from the Customer table to the design grid. Son the records in ascending order by Customer Number within Sales Rep Number. All sales reps should appear in the result, even if they currently have no customers. Run the query and save it as Rep-Customer Query.

4. Create the report shown in Figure 2-88. The report uses the Rep-Customer Query.

5. If requested to do so by your instructor, rename the Rep-Customer Report in the Navigation Pane as LastName-Customer Report where LastName is your last name.

6. Submit the revised database in the format specified by your instructor.

7. (3 What criteria would you enter in the Customer Name field if you wanted to find all customers who had the word Beauty somewhere in their name?

Design, create, modify, and/or use a database following the guidelines, concepts, and skills presented in this chapter. Labs are listed in order of increasing difficulty. Labs 1 and 2, which increase in difficulty, require you to create solutions based on what you learned in the chapter; Lab 3 requires you to create a solution, which uses cloud and web technologies, by learning and investigating on your own from general guidance.

Lab 1: Querying the Dartt Offsite Services Database

Problem: The management of Dartt Offsite Services has determined a number of questions it wants the database management system to answer. You must obtain the answers to these questions.

Note: Use the database modified in Lab 1 of Chapter 1 on page AC 68 for this assignment, or see your instructor for information on accessing the required files.

Instructions: Perform the following tasks:

1. Start Access. Open the Lab 1 Dartt Offsite Services database and create a new query for the Client table. Add the Client Number, Client Name, Amount Paid, Balance Due, and Service Rep Number fields to the design grid, and restrict the query results to only those clients where the service rep number is 37. Save the query as Lab 2-1 Step 1 Query.

2. Create a query for the Client table that includes the Client Number, Client Name, and Balance Due fields for all clients located in North Carolina (NC) with a balance due less than $1,000.00. Save the query as Lab 2-1 Step 2 Query.

3. Create a query for the Client table that includes the Client Number, Client Name, Street, City, and State field for all clients whose names begin with Ba. Save the query as Lab 2-1 Step 3 Query.

What decisions will you need to make when maintaining your own databases?

Use these guidelines as you complete the assignments in this chapter and maintain your own databases outside of this class.

1. Determine when it is necessary to add, change, or delete records in a database.

2. Determine whether you should filter records.

a) If your criterion for filtering is that the value in a particular field matches or does not match a certain specific value, use Filter By Selection.

b) If your criterion only involves a single field but is more complex, use a common filter.

c) If your criterion involves more than one field, use Filter By Form.

d) If your criterion involves more than a single And or Or, or if it involves sorting, use Advanced Filter/Sort.

3. Determine whether additional fields are necessary or whether existing fields should be deleted.

4. Determine whether validation rules, default values, and formats are necessary.

a) Can you improve the accuracy of the data entry process by enforcing data validation?

b) What values are allowed for a particular field?

c) Are there some fields in which one particular value is used more than another?

d) Are there some fields for which special formats would be appropriate?

5. Determine whether changes to the format of a datasheet are desirable.

a) Would totals or other calculations be useful in the datasheet?

b) Would different gridlines make the datasheet more useful?

c) Would alternating colors in the rows make them easier to read?

d) Would a different font and/or font color make the text stand out better?

e) Is the font size appropriate?

f) Is the column spacing appropriate?

6. Identify related tables in order to implement relationships between the tables.

a) Is there a one-to-many relationship between the tables?

b) If so, which table is the one table?

c) Which table is the many table?

7. When specifying referential integrity, address deletion and update policies.

a) Decide how to handle deletions. Should deletion be prohibited or should the delete cascade?

b) Decide how to handle the update of the primary key. Should the update be prohibited or should the update cascade?