PROJECT DESCRIPTION
You are working with the organizers of the Solar Energy Conference to be held in Phoenix, Arizona in March of 2016. The conference will bring together delegates from companies around the world that specialize in developing solar energy solutions. A database for the conference has already been developed. This database includes tables listing data about workshops, delegates and the companies they represent, and conference staff. You are working in the database and will be editing some of the tables and other components as part of ongoing efforts to ensure the database is as up-to-date and useful as possible.
GETTING STARTED
- Download the following file from the SAM website:
- SC_Access2013_CS_P1a_FirstLastName_1.accdb
- Open the file you just downloaded and save it with the name:
- SC_Access2013_CS_P1a_FirstLastName_2.accdb
- Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically.
- To complete this SAM Project, you will also need to download and save the following data file from the SAM Website onto your computer:
- support_SC_A13_CS_P1a_Company.xlsx
- Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
- Create a new table in Datasheet view with the following options:
- Rename the default primary key ID field to PackageID and change the Data Type to Short Text. (Hint: PackageID should remain the primary key.)
- Change the Field Size of the PackageID field to 4.
- Add a new field with the name PackageName and change the Data Type to Short Text.
- Add another field to the table with the name Price and change the Data Type to Currency.
Save the table with the name Conference Packages.
2. With the Conference Packages table open in Datasheet view, change the font in the table to Arial Narrow and the font size of the table to 12 pt.
3. With the Conference Packages table still open, add the records shown in bold in Table 1 below to the Conference Packages table. As necessary, widen the columns to make all text visible for each record. Save and close the table.
Table 1: Conference Packages
| PackageID | PackageName | Price |
| FC16 | Full Conference Package | $175.00 |
| WO16 | Workshop Package | $100.00 |
| SV16 | Site Visit Package | $100.00 |
| KO16 | Keynote Package | $75.00 |
4. Open the Relationships window and add the Delegates and Conference Packages table to the Relationships window. Create a one-to-many relationship between the PackageID field in the Conference Packages table and the PackageID field in the Delegates table. Select enforce referential integrity and cascade update related fields. Do not make the relationship cascade delete related records. Save and close the Relationships window.
5. Open the Delegates table in Design view and make the following changes to the Deposit field:
a. Change the Data Type from Short Text to Currency.
b. Change the Default Value property to 50.
6. With the Delegates table still open in Design view, specify PackageID field as a required field.
7. Add a field to the end of the Delegates table with the following options:
- Set SpecAccommodations for the field name.
d. Set the Data Type property of the field to Long Text.
e. Set the Description property to Special Accommodations required for Workshop or Site Visit participation.
f. Set the Caption property of the field to Special Accommodations.
8. Change the Field Size property of the ReservationID field to 6. Save the changes to the Delegates table. (Note: Because there was a change to a field size, the "Some data may be lost" warning message will appear. The data fits within the valid ranges, so ignore this error and continue saving the table.)
9. View the Delegates table in Datasheet view and then find or navigate to the record with the ReservationID value of LO001. Update the Special Accommodations field value to Wheel Chair accessibility.
10. With the Delegates table still in Datasheet view, apply a filter by selection to locate all records where the CompanyID value is equal to DSNM01. Update the PackageID field value for Lalo Garcia's record (who has a ReservationID of LG001) to FC16. Also update the Workshop field value to ACT01. Clear all filters applied to the table. Save and close the Delegates table.
11. Open the Workshops table in Design view. Use the Lookup Wizard to change the Category field to a Lookup field. Type in the following three values (in the order shown) as the list of possible values for the field: Presentation, Panel Discussion, Hands-On Training. Limit the field values to only the items in the list, and do not allow multiple values for the field.
12. With the Workshops table still open in Design view, delete the Confirmed field from the Workshops table. Save the Workshops table.
13. Switch the Workshops table to Datasheet view and change the Category field value to Panel Discussion for the Efficiency Levels for Solar Power workshop (which has the WorkshopID field value ESP01). Close the table.
14. The Solar Energy Conference organizers offer priority registration for Gold-level corporate sponsors, but are planning to open the registration to companies at other support levels. To identify the companies in the Company table as Gold-level corporate sponsors, create an Update query to update the SponsorLevel field value to "Gold" for all records currently in the Company table. Run the query and save it as Gold Update Query. Close the query.
15. Import the data from the Excel file support_SC_A13_CS_P1a_Company.xlsx available for download from the SAM website. Append the records into the Company table. Do not create a new table and do not save the import steps.
16. Create a simple query using the Query Wizard based on the Sites table with the following options:
a. Include the SiteName, ContactName, ContactNumber fields (in that order).
b. Save the query with the title Sites Contact Query.
Run and close the query.
17. Create a new query in Design view based on the Company and Delegates tables with the following options:
a. Include the Company field from the Company table.
b. Include the LastName, FirstName, and Phone fields (in that order) from the Delegates table.
c. Sort the records in ascending order based on the Company field and then by the LastName field.
d. Save the query with the name Company Contact Query.
Run and close the query.
18. Create a crosstab query based on the Delegates table with the following options:
a. Use only data from the Delegates table in the query.
b. Use the PackageID field as the row heading.
c. Use the CompanyID field as the column heading.
d. Use a Count of the ReservationID field as the calculated value for each row and column intersection in the crosstab query.
e. Save the query with the name Package-Company Crosstab.
View the query and then close it.
19. Create a query based on the Conference Packages and Delegates tables with the following options:
a. Select the FirstName, LastName, and Deposit fields from the Delegates table.
b. Select the PackageName and Price fields from the Conference Packages table.
c. Move the Deposit field to the right of the Price field.
d. Add a calculated field after the Deposit field with the alias TotalDue that calculates the difference between the Price field and the Deposit field.
e. Save the query with the name Outstanding Payments Query.
View the query, confirm that it matches Figure 1 on the following page, and then close it.
Figure 1: Outstanding Payments Query
20. Open the Keynote Attendance Query and add the criteria to select only those records with a PackageID field value of FC16 or KO16. Save and run the query and then close it.
21. Open the Sunworks Small Deposit Query and add the criteria to select only those records with a CompanyID field value of SWWA01 AND a Deposit field value less than 100. Hide the CompanyID field. Save and run the query, then close it.
22. Open the Site Visits Total Query in Design view and modify it by adding totals to the query. For the SiteName field, set the total row to Group By. For the ReservationID field, set the total row to Count. Save and run the query, then close it.
23. Create a Split Form based on the Delegates table. Save the form as Delegates Entry Form.
24. Use the Delegates Entry Form to add a new record to the Delegates table, using the values shown in Figure 2 on the following page.
Figure 2: Delegates Entry Form
25. Create the simple report shown in Figure 3 on the following page for the Workshops table. Save the report with the name Workshops Report and close the table. (Hint: The time and date values in your report header may not match those shown in Figure 3.)
Figure 3: Workshops Report
26. Create a new report using the Report Wizard based on the Company Payment Query with the following options:
a. Include all fields from the Company Payment Query.
b. Use no additional grouping in the report.
c. Sort the report in ascending order by the Company field values.
d. Use the Tabular layout and the Portrait orientation for the report.
e. Set the title of the report to Company Payment Report.
Preview the report and then save it.
Figure 4: Company Payment Report
27. Open the Company report in Layout view and make the following updates to the report so that it matches Figure 5 on the following page:
a. Change the title of the report to Company Representation.
b. Remove the Address and Postal Code columns from the report.
28. Add a Total row to the Company report that calculates the sum of the values in the Number of Delegates column. If necessary, expand the size of the total control so that it appears completely. View the Company report in Report view, confirm that it matches Figure 5 on the following page, then save and close the report.
Figure 5: Company Report
Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project.
