GETTING STARTED

Open the file NP_AC16_5a_FirstLastName_accdb, available for download from the SAM website.
Save the file as NP_AC16_5a_FirstLastNameaccdb by changing the “1” to a “2”.
- If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
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 query to find all records in the tblClient table in which the Phone field value starts with the area code 716. Include the following options:
1. Display all fields from the tblClient table in the query in the following order: ClientID, ClientFirst, ClientLast, Address, City, State, Zip, Phone, BirthDate, Gender.
2. Sort the query in ascending order by the ClientLast

Save the query as qry716AreaCode, run the query, and then close it.

Create a query to find all records in the tblClient table in which the City field value is not Buffalo, Perry, or Castile. Include the following options:
1. Display all fields from the tblClient table in the query in the following order: ClientID, ClientFirst, ClientLast, Address, City, State, Zip, Phone, BirthDate, Gender.
2. Add a condition to find all records in the tblClient table in which the City field values are not “Buffalo”, “Perry”, or “Castile”. Use a list-of-values match for the selection criteria.
3. Sort the query in ascending order by the City

Save the query with the name qryNotBuffaloPerryCastile, then run and close it.

Create a query to find all records from the tblEmployee table in which the MonthlySalary value is 1500, 1850, or 3550. Use a list-of-values match for the selection criteria. The query should have the following options:
1. Display all fields from the tblEmployee table in the query in the following order: EmployeeID, LocationID, First, Last, Title, MonthlySalary.
2. Sort the query in descending order by the MonthlySalary

Save the query as qrySelectSalaries, run the query, and then close it.

Create a query to display all records from the tblTrainer table with the following options:
1. Display only the LocationID, Specialty, and Certification fields (in that order) from the tblTrainer table in the query.
2. Sort the query in ascending order by the LocationID
3. Add a calculated field named TrainerName as the first column in the query that concatenates the First field value, a space, and the Last field value.
4. Set the Caption property for the TrainerName field to Trainer Name. (Hint: Do not type the period.)
5. Save the query as qryTrainerSpecialties, run the query, and then resize the TrainerName column to best fit the data it contains.

Save and close the query.

Create a parameter query to select the tblTrainer table records for a Certification field value that the user specifies. The query should have the following options:
1. Display the LocationID, First, Last, Specialty, and Certification fields (in that order) in the query.
2. Use Enter the certification: as the prompt associated with the Certification field prompt. If the user doesn’t enter a Certification field value, the parameter query should select all records from the tblTrainer
3. Sort the query in ascending order by the Last
4. Save the query using qryCertificationParameter as the name.

Confirm the parameter query is working correctly by running the query and entering no value as the Certification field value. Then run the query again and enter NATA as the Certification field value. Close the query.

Create a find duplicates query based on the tblClient table with the following options:
1. Select the Address field as the field that might contain duplicates.
2. Select the ClientID, ClientFirst, ClientLast, and Phone fields (in that order) as additional fields in the query recordset.

Save the query as qryDuplicateHouseholds and then close it.

Create a find unmatched query that finds all records in the tblTrainer table for which there is no matching record in the tblBilling Display the TrainerID, LocationID, First, Last, and HireDate fields from the tblTrainer table in the query recordset. Save the query as qryTrainersWithoutMatchingBilling and then close it.
Create a query to display all fields from the tblEmployee table in the following order: EmployeeID, LocationID, First, Last, Title, and MonthlySalary, sorted in descending order by the MonthlySalary Use the Top Values property to select the top five records. Save the query as qryTop5Salaries, run the query, and then close it.
Open the tblLocation table in Design View. Change the ManagerID field to a lookup field with the following options:
1. Specify that the lookup field values will come from another table or query.
2. Select the Last field, and then select the EmployeeID field from the tblEmployee
3. Sort the values in ascending order by the Last
4. Confirm the Hide the key column (recommended) option is checked.
5. Resize the Lookup column to best fit the data it contains.
6. Accept the default label for the Lookup column.
7. Save your changes, and then view the tblLocation table in Datasheet View.
8. Change the ManagerID field value for the record with Location ID A to Carlson.

Close the table.

Open the tblBilling table in Design View. Create a field validation rule for the Sessions field to only allow values greater than 0. Enter Clients must participate in at least 1 session. (including the period) as the validation text. Save and close the tables, clicking Yes when warned about the data integrity rule.
Open the tblClient table in Design View. Use the Input Mask Wizard to add an input mask to the Phone The input mask should use parentheses as separators for the area code, a space between the area code and the number, and a dash between the second and third groups of numbers, as in (123) 456-7890, with only the last seven digits required. Do not store the literal display characters if you are prompted to do so. Save the tblClient table and switch to Datasheet View. Change the Phone field value for the record with ClientID A10026 to (716) 555-0001.
View the tblClient table in Design View, and then define a field validation rule for the Gender field in the tblClient Acceptable field values for the Gender field are F and M (in that order). Use the message Must be F or M to notify a user who enters an invalid Gender field value. Save the table, clicking Yes when warned about data integrity rules.

Switch to Datasheet View and test the field validation rule for the Gender field, making sure any tested field values are the same as they were before your test. (Hint: You can confirm this by retyping the correct value or by pressing the Esc key.) Close the table.

Create a crosstab query based on the tblEmployee table with the following options:
1. Use the LocationID field values for the row headings.
2. Use the Title field values for the column headings.
3. Use the sum of the MonthlySalary field values as the summarized value.
4. Include row sums.
5. Save the query using qryMonthlySalaryByTitle as the name.

Run the query, then save and close it.

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.

What Students Are Saying About Us

.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐
"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"

.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."

GETTING STARTED

PROJECT STEPS

What Students Are Saying About Us

"Order a Custom Paper on Similar Assignment! No Plagiarism! Enjoy 20% Discount"

Related posts: