Create a new table in datasheet view with the given options

Reference no: EM131901245

Question: PROJECT STEPS

1. Carpenter Family Camp operates summer camps for youth as well as families on a lake in Maine situated near the coast. The camp includes activities for families as well as separate activities for adults and children. Optional adventures to nearby attractions are also available.

Create a new table in Datasheet View with the following options:

a. Rename the default primary key ID fieldtoAdventureID, and then change its data type to Short Text. (Hint:The AdventureIDfield should remain the primary key.)

b. Change the field size of the AdventureID field to 4.

c. Add a new field with the name AdventureName and the Short Textdata type.

d. Add another field to the table with the name AdultPrice and the Currencydata type.

e. Add a fourth field to the table with the name ChildPrice and the Currencydata type.

f. Save the table using Adventure as the name.

2. With the Adventure table still open in Datasheet View, change the font in the table to Arial Narrow and the font size to 12 pt.

3. With the Adventure table still open in Datasheet View, add the records shown in Table 1 below. If necessary, resize the AdventureName field so that all field values are completely visible. Save and close the Adventure table.

AdventureID	AdventureName	AdultPrice	ChildPrice
BB01	Bike & Beach	$28.00	$20.00
HR01	Horseback Ride	$30.00	$25.00
WJ01	Windjammer Cruise	$45.00	$35.00
WW01	Whale Watching	$32.00	$26.00

4. Open the Reservation table in Design View and make the following changes:

a. Change the data type for the LodgingFee field to Currency.

b. Specify that theSessionIDfield is a Requiredfield.

c. Save the changes to the Reservation table, and then close it. (Hint: Because you made changes to data types and field sizes, the "Some data may be lost" warning message appears. The data fits within the valid ranges, so ignore this message and continue saving the table.)

5. Open the Relationships window, and then add the Camper table to it. Create a one-to-many relationship between the ParentID field in the Parent table and the ParentID field in the Camper table. Make the relationship enforce referential integrity and cascade update related fields. Do not make the relationship cascade delete related records. Save the relationships, and then close the window.

6. Open the Counselor table in Design View and make the following changes:

a. Add a new field following the Specialty field. Assign the name CPR Certification to the field.

b. Set the data type for the field to Yes/No.

c. Set the Caption property to CPR.

d. Enter CPR certification current? as the description for the field.

e. Save the changes to the Counselor table, and then close it.

7. Open the Reservation table in Datasheet View, and then find or navigate to the record with the ReservationIDfield value 1700012. Change the LodgingFee field value to $140.00.

8. With the Reservation table still open in Datasheet View, apply a filter by selection to locate all records where the SessionIDfield value equals1.Change the AdultPrice field value for ParentID 101 to $245.00. Change the ChildPrice field value for the same record to $215.00. Clear all filters. Save and close the Reservation table.

9. Open the Counselortable in DesignView. Use the Lookup Wizard to change the Specialty field to a Lookup field. Type in the following four values (in the order shown) as the list of possible values for the field: Arts & Crafts, Land Sports, Nature Studies, and Water Sports. Limit the field values to only the items in the list, and do not allow multiple values for the field.

10. With the Counselor table still open in Design View, delete the HomePhone field. (Hint: If a message appears concerning deleting an index, continue with the deletion.)Save the Counselor table.

11. Switch to viewing the Counselor table in Datasheet View, and then change the SpecialCertification field value for Susan Kiley (who has a CounselorIDfield value of KS01) to Wilderness First Aid. Close the table.

12. Import the data from the file Support_SC_AC16_CS1-3a_Activity.xlsx, available for download from the SAM website. Append the records to the Activity table. Do not create a new table, and do not save the import steps.

13. Carpenter Family Camp requires all campers to submit a signed liability waiver from their parents. Create an update query to change the Waiver field value to No for all records currently in the Parent table. Run the query, and then save it using Waiver Update Query as the name. (Hint: 21 records will be updated by this query.) Close the query.

14. Use the Simple Query Wizardto create a query based on the Counselor table with the following options:

a. Include the CounselorID, FirstName, LastName, andCellPhone fields (in that order).

b. Save the query with the name Counselor Contact Query, and then close the query.

15. Create a new query in Design View based on the Counselor and Counselor_Session tables with the following options:

a. Include the SessionID field from the Counselor_Session table.

b. Include the LastName, FirstName, and CellPhone fields (in that order) from the Counselor table.

c. Sort the records in ascending order based on the SessionID field and then by the LastName field.

d. Save the query usingSession Contact Query as the name.

e. Run the query, and then close it.

16. Use the Crosstab Query Wizard to create a crosstab based on the Reservation table with the following options:

a. Use only data from the Reservation table in the query.

b. Use SessionIDas the row heading.

c. Use Children as the column heading.

d. Use a sum of the ChildPricefield as the calculated value for each row and column intersection in the crosstab query.

e. Save the crosstab query using Session-Child Crosstab as the name.

f. View the query, and then close it.

17. Create a new query in Design View based on the Reservationtable and the Session 1 Payments table with the following options:

a. Select the ParentID field from the Reservation table.

b. Select the ReservationID, SessionID, AdultTotal, ChildTotal, and Lodging fields (in that order) from the Session 1 Payments table.

c. Move the ParentID field to the right of the SessionID field.

d. Add a calculated field after the Lodging field with the alias TotalFees that calculates the sum of the AdultTotal, ChildTotal, and Lodging fields.

e. Save the query using Session 1 TotalFees Query as the name.

f. View the query, confirm that it matches Figure 1 below, and then close it.

Figure 1: Session 1 TotalFees Query

18. Open the States Query in Design View, and then add the criteria to select only those records with a State field value of NYorNJ. Save and run the query, and then close it. (Hint: This query should return records that meet one or more of the query conditions.)

19. Open the Younger Males Queryin Design View,and then add the criteria to select only those records with a Gender field value of Mand an Age field value of less than 10. Hide the Gender field. Save and run the query, and then close it. (Hint: This query should only return records that meet both of the query conditions.)

20. Open the Sessions Total Query in Design View, and then modify it by adding Totals to the query. For the SessionID field, set the Total row to Group By. For the ReservationID field, set the Total row to Count. Save and run the query, and then close it.

21. Create a Split Form based on the Parent table. Save the form as Parent Update Form,and then close the form.

22. Open the Parent Update Form,and then add a new record to the Parent table, using the values shown in Figure 2 below. Close the form.

Figure 2: New Record for the Parent Update Form

23. Create the simple report shown in Figure 3 below for the Adventure table. Save the report with the name Adventure Report, and then close the report.

Figure 3: Adventure Report

24. Use the Report Wizardto create a new report based on theParenttable with the following options:

a. Include the ParentID, FirstName, LastName, HomePhone, and CellPhonefields (in that order) from the Parent table.

b. Use no additional grouping in the report.

c. Sort the report in ascending order by the ParentID field.

d. Use the Tabular layout and Portraitorientation for the report.

e. Assign the name Parent Contact Report to the report.

f. Preview the report to ensure that it matches Figure 4 below, and then save and close the report.

Figure 4: Parent Contact Report

440_Contact report.jpg

25. Open the Session 1 Report in Layout View, and then make the following changes to the report:

a. Remove the City and State columns from the report.

b. Add a Totals row that calculates the sum of the values in the Adults column and the sum of the values in the Children column. If necessary, expand the size of the total control so that it appears completely.

c. Change the title of the report using Session 1 Camper Report as the name.

d. View the Session 1 Report in Report View, confirm that it matches Figure 5 below, and then save and close the report.

Figure 5: Session 1 Report

2200_Session report.jpg

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

Information related to above question is enclosed below:

Attachment:- Files.rar

Reference no: EM131901245

Questions Cloud

What types of businesses handle lots of cash : This week's fraud topics are cash larceny and billing schemes. What types of businesses and industries are particularly prone to these fraud issues?

Evaluate dondi properties corp v commercial savings : Discuss the court's analysis that the legislature, not the courts, need to address responsibility for lawyers' fees?

Set of normally distributed data : Suppose you have a set of normally distributed data where the mean is 120 and the standard deviation is 15. What score is located at -2 standard deviations?

Potential impact of acknowledging people on your team : Discuss the potential impact of acknowledging people on your team. How could it affect the person, the team, and/or the workplace? Include a professional exam

Create a new table in datasheet view with the given options : Carpenter Family Camp operates summer camps for youth as well as families on a lake in Maine situated near the coast. The camp includes activities for families.

Ellipse or hyperbola in standard form : Rewrite the equation for each ellipse or hyperbola in standard form. State whether the equation represents an ellipse or a hyperbola, and identify the center.

What characters are involved in the conflict : What characters are involved in the conflict? What reaction do the characters in the story have in response to this conflict?

Discuss the different employment types and relationships : reate an outline of the major headings with a two- to three-sentence description of what you will discuss under each heading.

How many selections do 2 diamonds occur : If seven cards are chosen from a well-shuffled deck of 52 playing cards. In how many selections do 2 diamonds occur?

User Account

All Pages