PROJECT DESCRIPTION

Amy Rosario needs to update some of the data in the Contractor and Office tables to reflect a contract change and the closure of a business location for PRN Nursing Services. She also needs to view specific data about clients, contractors, and billing. She asks you to help her maintain the data in the database and to create query objects to view the data she needs to review.

GETTING STARTED
•    Download the following file from the SAM website:
o    NP_Access2013_T3_P1b_FirstLastName_1.accdb
•    Open the file you just downloaded and save it with the name:
o    NP_Access2013_T3_P1b_FirstLastName_2.accdb
o    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.
•    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
1.    Open the Contractor table in Datasheet view. Display the subdatasheet for the record with ContractorID 1015, and then update the record with BillingID 2009 to include 30 total hours and an hourly rate of $60. Close the Contractor table.
2.    Open the Office table in Datasheet view. Change the font size for the datasheet to 12 pt.
3.    Select and resize the Office ID, State, and Zip columns in the Office table datasheet to best fit the data they contain.
4.    Delete the record with OfficeID 103 from the Office table. Save and close the Office table.
5.    Create a new query in Design view that is based on the Client table. Add the FirstName, LastName, ClientSince, and Corp fields, in that order, to the query design. Save the query as ClientStartDates, and then run it.
6.    In the ClientStartDates query datasheet, use Filter By Selection to select only those records for clients that were contracted in the year 2014. (Tip: Select 2014 in the Client Since field and then select the Ends with 2014 option in the Selection menu.) Redisplay all records in the datasheet, but do not clear the filter you just applied. Save and close the ClientStartDates query.
7.    Create a new query in Design view that is based on the Client, Billing, and Contractor tables. Save the query as ClientsAndContractors, and then do the following:
a.    Add the LastName field from the Client table to the query.
b.    Add the LastName field from the Contractor table to the query.
c.    Add the StartDate, EndDate, TotalHours, and HourlyRate fields, in that order, from the Billing table to the query.
d.    Save and run the query, and then close it.
8.    Use the Navigation Pane to copy the ClientsAndContractors query, and then paste it as ClientsAndContractorsJulyStart. Modify the ClientsAndContractorsJulyStart query by adding a condition to the StartDate field to select records with clients that were contracted on or after July 1, 2016. Set the StartDate field so it does not appear in the query results, but remains in the query design. Save and run the query, and then close the query.
9.    In the Navigation Pane, copy the ClientsAndContractorsJulyStart query, rename the copied query as ClientsAndContractorsJuly, and then do the following:
a.    Add a new condition to the query to select records with contracts that start on or after July 1, 2016 and end on or before July 31, 2016.
b.    Sort the records in ascending order by the StartDate field.
c.    Change the StartDate field so it appears in the query results, and then move the StartDate field so it follows the LastName field in the query design.
d.    Save and run the query, and then close it.
10.    Create a new query in Design view based on the Contractor table. Add the LastName, Interests, Credentials, HireDate, and PA fields, in that order, to the query design. Add a condition to the Credentials field to select records that contain the value MSN. Save the query as MSN, run it, and then close it.
11.    In the Navigation Pane, copy the MSN query, rename the copied query as MSNOrPA, and then add a new condition to the MSNOrPA query to select a record with a Credentials field that contains the value MSN or a record that indicates that the contractor is a physician assistant. Save and run the query, and then close it.
12.    Create a new query in Design view that is based on the Client and Billing tables. Save the query as ClientAmounts, and then do the following:
a.    Add the LastName and FirstName fields from the Client table to the query.
b.    Add the TotalHours and HourlyRate fields from the Billing table to the query. (Tip: The fields should be in the order LastName, FirstName, TotalHours, and HourlyRate.)
c.    Save and run the query.
13.    Add the Total row to the ClientAmounts query datasheet, and then use a function to calculate the total number of hours and the average hourly rate. Save and close the query.
14.    Create a new query in Design view that is based on the Client and Billing tables. Save the query as ContractDays, and then do the following:
a.    Add the LastName field from the Client table to the query.
b.    Add the TotalHours, StartDate, and EndDate fields from the Billing table, in that order, to the query.
c.    In Design view, create a calculated field named NumberOfDays in the fifth column of the query design grid that determines the number of days in each contract, by creating an expression that subtracts the StartDate field value from the EndDate field value. Set the Caption property for the calculated field to Number of Days.
d.    Sort the records by the NumberOfDays field in descending order.
e.    Save and run the query. Resize the Number of Days column to best fit the data it contains.
f.    Save and close the query.
15.    Create a new query in Design view that is based on the Billing and Contractor tables. Save the query as ContractorTotals, and then do the following:
a.    Add the OfficeID field from the Contractor table to the query.
b.    Add the TotalHours and HourlyRate fields from the Billing table to the query.
c.    For each office, use an aggregate function to calculate the total number of hours and the average hourly rate for contractors assigned to each location. For these two columns, use the field names TotalContractHours and AverageHourlyRate, and the captions Total Contract Hours and Average Hourly Rate, respectively.
d.    Save and run the query. Resize the Total Contract Hours and Average Hourly Rate columns to best fit the data they contain.
e.    Save and close the query.
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.

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT