Chat with us, powered by LiveChat Using Microsoft Access and complete a Project activity. The project will ask you to download instructions and starter files for you to use for this project. Within the ins | EssayAbode

Using Microsoft Access and complete a Project activity. The project will ask you to download instructions and starter files for you to use for this project. Within the ins

  Using Microsoft Access and complete a Project activity. The project will ask you to download instructions and starter files for you to use for this project. Within the instructions, there are screenshots of what your end file should look like.   

New Perspectives Access 2016 | Module 3: SAM Project 1a

C:UsersakellerbeeDocumentsSAM DevelopmentDesignPicturesg11731.pngNew Perspectives Access 2016 | Module 3: SAM Project 1a

Healthy Myles Fitness Center

USING QUERIES TO UPDATE AND RETRIEVE INFORMATION

GETTING STARTED

· Open the file NP_AC16_3a_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as NP_AC16_3a_FirstLastName_2.accdb 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

1. You are employed at Healthy Myles Fitness Center and are in charge of consolidating the company’s records and other important information into a database. You have already set up the basic structure of the database and created relationships between tables, and will now work with queries to make information easier to update and retrieve.

Open the tblTrainer table in Datasheet View. Display the subdatasheet for the Randy Lee (TrainerID field value 312), and then update the record with the BillingID field value A80643 to include 7 sessions and an amount of $950. Close the tblTrainer table.

1. Open the tblLocation table, and then delete the Downtown record (LocationID 335). Save and close the tblLocation table. (Hint: if a message warning that you are about to delete 1 record appears, click Yes.)

1. Create a new query in Design View based on the tblClient table. Add the FirstName, LastName, BirthDate, and Gender fields, in that order, to the query. Save the query as qryClientBirthday, run it, and then close it.

1. Create a new query in Design View based on the tblClient, tblBilling, and tblTrainer tables. Save the query as qryClientsAndTrainers, and then do the following:

3. Add the LastName field from the tblClient table to the query.

3. Add the LastName field from the tblTrainer table to the query.

3. Add the StartDate, EndDate, Sessions, and Amount fields, in that order, from the tblBilling table to the query.

Save and run the query, and then close it.

1. Create a new query in Design View that uses the tblTrainer table, and then do the following:

4. Add the LastName, Specialty, Certification, HireDate, and Minors fields, in that order, to the query design.

4. Add criteria to the Certification field to select records that contain the value NASM. Save the query as qryTrainerNASM, run it, and then close it.

1. In the Navigation pane, copy the qryTrainer query and paste it in the navigation pane, rename the copied query as qryTrainer2015, open it in Design view, and then do the following:

5. Add criteria to the HireDate field to select records with contracts that begin on or after January 1, 2015.

5. Hide the TrainerID field so it does not appear in the query results but remains in the query design. (Hint: Make sure you hide this field in Design View, not in Datasheet View.)

Save and run the query, and then close it.

1. In the Navigation pane, copy the qryTrainer query and paste it in the navigation pane, rename the copied query as qryTrainerNSCAMinors, open it in Design view, and then do the following:

6. Add criteria to the query to select records with the Certification field value NSCA-CPT and the Minors field value Yes.

6. Sort the records in ascending order by the LastName field.

6. Move the Minors field so it appears to the right of the Certification field in the query design.

6. Save and run the query, and then close it.

1. In the Navigation pane, copy the qryTrainer query and paste it in the navigation pane, rename the copied query as qryTrainerNATAOrMinors, open it in Design view, and then do the following:

7. Add criteria to the query to select records with the Certification field value NATA or records that indicate that the trainer accepts clients who are minors.

7. Save and run the query, and then close it.

1. Open the qryTrainerBilling query in Datasheet View. Change the font size for the datasheet to 12 pt.

1. With the qryTrainerBilling query still open in Datasheet View, add a total row to the query datasheet. In the total row, use the SUM function to total the values in the Amount field, and use the AVG function to average the values in the Sessions field. Widen the Sessions field to display the result in the total row (approximate width 18.8) Save and close the qryTrainerBilling query.

1. Create a new query in Design View that is based on the tblBilling and tblClient tables, and then do the following:

10. Add the ClientID and LastName fields from the tblClient table to the query.

10. Add the Sessions and Amount fields from the tblBilling table to the query.

10. Add a total row to the design grid, and group the query results by the LastName field values.

10. In the total row, use the SUM aggregate function to calculate the sum of the Amount field values. Set the caption of the Amount field to Total Amount. (Hint: Do not type the period.)

10. In the total row, use the SUM aggregate function to calculate the sum of the Sessions field values. Set the caption of the Sessions field to Number of Sessions. (Hint: Do not type the period.)

10. Save the query as qryClientTotals. Run the query, and then close it.

1. Open the qryClientBilling query in Design View and update it as described below:

11. Add a calculated field with the title SessionCost to the end of the query that calculates the cost per session. The formula will calculate the Amount field value divided by the Sessions field value. (Hint: The division symbol is the / symbol.)

11. Use Cost per Session for the calculated field’s caption property.

11. Format the SessionCost field as currency.

11. Move the SessionCost field to the left of the FirstName field.

11. Sort the records first in descending order by the SessionCost field values, and then in ascending order by the LastName field values.

11. Save and run the query, and then 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.

2

,

IfSuccessful_Status GA_Status_Icon SAM_Logo
true
false
ID FirstName LastName AssignmentGUID UserID
false Delvone Scott {651B4C3E-7DBA-4E77-A575-E70456AE9A16} {651B4C3E-7DBA-4E77-A575-E70456AE9A16}
ID FirstName LastName ProjectName SubmissionNum MaxScore Score EngineVersion
ID StepNumber Description IfSuccessful StepScore StepMaxScore ErrorText ActionName StepActionOrder
BillingID ClientID TrainerID StartDate EndDate Amount Sessions
A80214 B21845 585 2/5/16 5/3/16 ¤ 850.00 7
A80222 B13298 146 4/9/16 5/2/16 ¤ 350.00 3
A80342 A10025 704 3/7/16 3/28/16 ¤ 1,000.00 10
A80399 A10236 774 3/16/16 4/27/16 ¤ 600.00 6
A80565 B15263 585 2/1/16 2/17/16 ¤ 600.00 6
A80610 B10216 585 4/1/16 5/17/16 ¤ 1,200.00 12
A80643 B10216 312 2/2/16 2/18/16 ¤ 800.00 6
A80675 A10236 612 3/3/16 5/19/16 ¤ 1,550.00 12
A84575 A10025 146 3/9/16 3/21/16 ¤ 750.00 6
A84975 A10236 709 3/22/16 3/24/16 ¤ 150.00 2
B80345 A10488 312 3/28/16 4/18/16 ¤ 1,000.00 10
B80671 B10216 585 3/1/16 3/2/16 ¤ 250.00 2
B80699 A10488 585 3/8/16 3/29/16 ¤ 450.00 4
B85654 B10589 146 3/2/16 3/17/16 ¤ 1,550.00 12
B85659 B15674 709 4/22/16 5/22/16 ¤ 450.00 4
ClientID FirstName LastName Address City State Zip Phone BirthDate Gender
A10025 Alfred Pineda 465 Grant St. Buffalo NY 14213 716-555-4815 12/15/64 M
A10236 Randy Gibson 396 Amherst St. Buffalo NY 14207 716-555-2412 1/6/83 M
A10488 Christopher Miller 266 Elmwood Ave. Buffalo NY 14222 716-555-9912 9/3/94 M
A10522 Sandy Locke 291 Ellicott St. Buffalo NY 14207 716-555-2156 4/8/74 F
B10216 Maria Johnston 701 Washington St. Buffalo NY 14203 716-555-9855 2/28/71 F
B10589 Andrew Dombrowski 75 Edward St. Buffalo NY 14202 716-555-9034 7/1/76 M
B13298 Mary Dumas 141 Abbott Rd. Buffalo NY 14222 716-555-3664 5/3/73 F
B15263 Jenny Brown 76 Pearl St. Buffalo NY 14222 716-555-2598 9/5/83 F
B15674 Sarah Fleming 126 Michigan Ave. Buffalo NY 14204 716-555-5612 2/2/87 F
B21845 Jose Ramos 1176 South Park Ave. Buffalo NY 14220 716-555-2367 2/7/89 M
LocationID LocationName LocationOpened Address City State ZipCode Phone
301 Elmwood 5/2/14 1400 Elmwood Ave. Buffalo NY 14216 716-555-1212
302 Riverside 6/8/15 733 Hertel Ave. Buffalo NY 14207 716-555-8315
308 Sheridan 1/5/15 5200 Sheridan Dr. Buffalo NY 14221 716-555-7621
318 Eastside 2/7/15 883 Jefferson Ave. Buffalo NY 14204 716-555-4455
321 Genesee 7/2/15 229 W Genesee St. Buffalo NY 14202 716-555-9427
335 Downtown 4/8/14 300 Pearl St. Buffalo NY 14202 716-555-2154
TrainerID LocationID FirstName LastName Specialty Certification HireDate Interests Minors
146 301 Jennifer Young Personal Trainer ACE 3/19/14 nutrition, strength training, motivation false
312 308 Randy Lee Strength Training NSCA-CSCS 9/27/13 yoga, nutrition true
454 301 Josh Martinez Sports Medicine NATA 11/9/15 martial arts, strength training false
585 302 Cam Williams Sports Medicine NASM 6/10/14 nutrition, martial arts false
612 318 Raj Patel Personal Trainer NSCA-CPT 1/4/16 yoga, dance fit, nutrition true
693 321 Sandy Larson Personal Trainer PTA Global 2/11/15 martial arts, nutrition, yoga false
704 302 Melissa Rogers Strength Training NSCA-CSCS 6/15/15 sports medicine, yoga false
708 301 Carson Garner Personal Trainer NSCA-CPT 2/25/13 nutrition, yoga, swimming true
709 321 Richard Sanderson Sports Medicine NASM 6/1/16 strength training, swimming false
774 308 Sally Andrews Personal Trainer NSCA-CPT 4/12/14 nutrition, sports medicine, strength training false
SELECT [FirstName] & " " & [LastName] AS StudentName, [_GradingReport].ProjectName, "Submission #" & [SubmissionNum] AS SubmissionCt, [_GradingReport].Score, [_GradingReport].MaxScore, "Score is: " & [Score] & " out of " & [MaxScore] AS Grade, [_GradingReportSteps].[StepNumber] & ". " & [Description] AS Step, [_GradingReportSteps].[StepScore] & "/" & [_GradingReportSteps].[StepMaxScore] AS StepGrade, [_GradingReportSteps].ActionName, IIf([ifsuccessful]=True,Null,[ErrorText]) AS Feedback, [_GradingReportSteps].IfSuccessful, [_GradingIcons].GA_Status_Icon, [_GradingReportSteps].StepNumber, [_GradingReportSteps].StepActionOrder, [_GradingReportSteps].ID, [_GradingIcons].SAM_Logo, [_GradingReportSteps].Description, [_GradingReport].EngineVersion FROM _GradingReport, _GradingIcons INNER JOIN _GradingReportSteps ON [_GradingIcons].[IfSuccessful_Status] = [_GradingReportSteps].IfSuccessful;
SELECT tblBilling.BillingID, tblClient.FirstName, tblClient.LastName, tblClient.BirthDate, tblBilling.ClientID, tblBilling.Amount, tblBilling.Sessions FROM tblClient INNER JOIN tblBilling ON tblClient.[ClientID] = tblBilling.[ClientID];
SELECT tblTrainer.TrainerID, tblTrainer.FirstName, tblTrainer.LastName, tblTrainer.Specialty, tblTrainer.Certification, tblTrainer.HireDate, tblTrainer.Minors FROM tblTrainer;
SELECT tblBilling.BillingID, tblTrainer.FirstName, tblTrainer.LastName, tblBilling.TrainerID, tblBilling.Amount, tblBilling.Sessions FROM tblTrainer INNER JOIN tblBilling ON tblTrainer.[TrainerID] = tblBilling.[TrainerID];

Related Tags

Academic APA Assignment Business Capstone College Conclusion Course Day Discussion Double Spaced Essay English Finance General Graduate History Information Justify Literature Management Market Masters Math Minimum MLA Nursing Organizational Outline Pages Paper Presentation Questions Questionnaire Reference Response Response School Subject Slides Sources Student Support Times New Roman Title Topics Word Write Writing