Posted: February 27th, 2023

EX19_AC_VOL1_GRADER_CAP_HW

Transfer

8/1/20

$ 1,500.00

Stevens Institute of Technology
8/1/20

15
$ 120.00
$ 1,500.00

.00

$ 1,500.00

1/1/21

$ 140.00

8/1/20
6
6
$ 100.00
$ 1,750.00

1/1/21
39

$ 150.00
$ 1,500.00

8/1/20
3
0
$ 100.00
$ 1,500.00

8/1/20
21
21
$ 140.00
$ 2,750.00

1/1/21
36

$ 150.00

Passaic County Community College
8/1/20
21
18
$ 120.00
$ 2,750.00

1/1/21
75

$ 1,750.00

1/1/21
48
45
$ 175.00
$ 1,500.00

Passaic County Community College
1/1/21
18
15
$ 120.00
$ 1,500.00

Bergen Community College
1/1/21

66
$ 175.00
$ 2,500.00

1/1/21
15
12
$ 120.00
$ 2,750.00

1/1/21
36
36
$ 150.00
$ 2,750.00

Hudson County Community College
8/1/20
42
42
$ 175.00
$ 1,750.00

College of Saint Elizabeth
1/1/21
57
57
$ 175.00
$ 2,250.00

Atlantic Cape Community College
8/1/20
30
27
$ 140.00
$ 1,500.00

8/1/20
42
36
$ 150.00
$ 1,500.00

Passaic County Community College
8/1/20
45
42
$ 175.00
$ 2,000.00

8/1/20
45
39
$ 150.00
$ 2,000.00

Bergen Community College
1/1/21
21
18
$ 120.00
$ 2,000.00

8/1/20
54
51
$ 175.00
$ 1,500.00

Atlantic Cape Community College
8/1/20
72
72
$ 200.00
$ 1,500.00

1/1/21
27
24
$ 140.00
$ 2,750.00

College of Saint Elizabeth
1/1/21
42
39
$ 150.00
$ 2,250.00

Sussex County Community College
1/1/21
45
39
$ 150.00
$ 1,500.00

8/1/20
54
45
$ 175.00
$ 2,000.00

8/1/20
9
9
$ 100.00
$ 2,000.00

Passaic County Community College
1/1/21
57
48
$ 175.00
$ 2,250.00

5/15/20
75
63
$ 175.00
$ 2,500.00

8/1/20
27
24
$ 140.00
$ 2,500.00

8/1/20
48
42
$ 175.00
$ 1,500.00

Berkeley College
5/15/20
48
42
$ 175.00
$ 1,750.00

1/1/21
6
6
$ 100.00
$ 2,750.00

8/1/20
51
42
$ 175.00
$ 2,750.00

StudentID	AdmittingSchool	AdmissionDate	CreditsEarned	CreditsTransferred	RegistrationFee	TuitionDue
	12	3					45	0		Stevens Institute of Technology												8/1/20			15		$ 120.00	$ 1,500.00
1234		51	Union County College				9					$ 100.00			$ 2,250.00
123452			Bergen Community College					1/1/				21				39	3			6			$ 150.00
123453				18
1234			54	Bloomfield College													1/1/21	60	$ 1		75
123455	William Paterson University of New Jersey				24						$ 140.00			$ 2,500.00
123456					Passaic County Community College					48								42						$ 175.00		$ 2,750.00
1234			57	Ramapo College of New Jersey			5/15/20		30				27				$ 1,750.00
123458	Brookdale Community College
123459		Hudson County Community College					36
123460	Rowan College at Burlington County
123461	Montclair State University
123462			Atlantic Cape Community College	33						$ 2,000.00
1234	63
123464	Warren County Community College			72		$ 200.00
123465	New Jersey City University
1234	66
123467	69
123468			College of Saint Elizabeth
123469	Stockton University
123470
123471
123472
123473	Caldwell University
123474
123475		Sussex County Community College
123476
123477	The College of New Jersey
123478
123479	Mercer County Community College
123480
123481
123482	Cumberland County College
123483		Berkeley College
123484
123485	Centenary College
123486	Pillar College
123487	County College of Morris
123488
123489	Thomas Edison State College
123490	Georgian Court University

Grader-Instructions Access

Project

Exp19_Access_App_CapAssessment – Transfer Student Advising 1.2

Project Description:

You were recently hired by your local college to help with registering all transfer students. The college?s Transfer Advising Department is a one-stop location for transfer students to come with questions. They have been working with Excel spreadsheets generated by the Information Technology department, but they are hoping to do more with an Access database. They have had a number of problems, including employees putting information in the wrong fields, putting information in the wrong format, and creating incorrect formulas. They are also hoping for more consistent ways of finding information, as well as being able to generate reports. Your tasks include importing an existing Excel worksheet as a table into your Access database; modifying the table; creating a relationship between two tables; creating queries with calculated fields, functions, and totals; creating a form for input; creating a report; and backing up the database.

Steps to Perform:

Step	Instructions	Points Possible
1	To start, you have been provided with a database the Information Technology department created. The database has one table and one form. You will be importing an Excel spreadsheet into a table and creating a primary key. Start Access. Download and open the file named aApp_Cap1_Advising.accdb. Grader has automatically added your last name to the beginning of the filename.		0
2	Import the aApp_Cap1_Transfer.xlsx Excel workbook into a new table named Transfer Schools. While importing the data, ensure that StudentID has a data type of Short Text, and select StudentID as the primary key.			10
3	Now that you have imported the data from the spreadsheet, you will modify the field properties in the Transfer Schools table and demonstrate sorting. Open the Transfer Schools table in Design view. Set the StudentID field size to 10. Remove the @ symbol from the StudentID format property. Change the AdmittingSchool field size to 7 5 . Change the RegistrationFee and TuitionDue fields to have 0 decimal places. Switch to Datasheet view, saving the changes that you’ve made. Resize the AdmittingSchool column by double-clicking on the border between AdmittingSchool and AdmissionDate. Sort the Transfer Schools table on the CreditsTransferred field in ascending order. Save and close the table.
4	Now that the table is imported and modified, you will create a relationship between the Transfer Schools and Transfer Students tables. Add the Transfer Schools and Transfer Students tables to the Relationships window. Create a one-to-one relationship between the StudentID field in the Transfer Students table and the StudentID field in the Transfer Schools table. Enforce referential integrity between the two tables and cascade updates and deletes. Save the changes and close the Relationships window.
5	You will demonstrate changing information in a form. Open the Transfer Students Data Entry form. Locate the record for Ellen Sullivan and change her major to History. Close the form.
6	Adam McChesney, an adviser in the center, would like your assistance in helping him find certain information. You will create a query for him and demonstrate how he can change information. Create a new query using Design view. This query will access fields from both the Transfer Schools and Transfer Students tables. From the Transfer Students table, add the FirstName, LastName, Major, Class, and GPA fields. From the Transfer Schools table, add the AdmissionDate, TuitionDue, CreditsEarned, and CreditsTransferred fields. Save the query as Transfer Credits. Set the criteria in the AdmissionDate field to 1/1/2021. Run the query (19 records will display). Enter the TuitionDue for Bianca Bain as $2200 and the GPA for Edgar Conway as 3.65. Save and close the query.
7	Now that you have created the query, you will create a second query for Adam that will calculate the number of credits students lost upon transfer, the tuition payments for which they will be responsible (assuming four payments per semester), and the due date of the first payment. Create a copy of the Transfer Credits query. Name the copy Transfer Calculations. Open the new query in Design view. Remove the criteria from the AdmissionDate field. Create a calculated field in the first empty field cell of the query named LostCredits that subtracts CreditsTransferred from CreditsEarned. Create another calculated field named TuitionPayments that uses the payment function and determines tuition paid in four installments. The student’s tuition payment and a 2.5% interest rate should be used in the function. Use 0 for the future_value and type arguments. Ensure that the payment appears as a positive number. Format the TuitionPayments calculated field as Currency. Create another calculated field named FirstPayment after the TuitionPayments field. To calculate the due date, add 30 to their AdmissionDate. Run the query and verify that the three calculated fields have valid data. Add a total row to the datasheet. Sum the TuitionDue column and average the TuitionPayment column. Save and close the query.
8	Cala Hajjar, the director of the center, needs to summarize information about the transfer students for the 2020–2021 academic year to present to the College’s Board of Trustees. You will create a totals query for her to summarize the number of transfer students, average number of credits earned and transferred, and total tuition earned by transfer institution. Create a new query in Design view. Add the Transfer Schools table. Add the AdmittingSchool, StudentID, CreditsEarned, CreditsTransferred, and TuitionDue fields. Sort the query by AdmittingSchool in ascending order. Show the Total row. Group by AdmittingSchool and show the count of StudentID, the average of CreditsEarned, the average of CreditsTransferred, and the sum of TuitionDue. Format both average fields as Standard. Change the caption for the StudentID field to NumStudents, the caption for the CreditsEarned average to AvgCreditsEarned, the caption for the CreditsTransferred average to AvgCreditsTransferred, and the caption for the sum of TuitionDue to TotalTuition. Run the query. Save the query as Transfer Summary. Close the query.
9	Hideo Sasaki, the department’s administrative assistant, will handle data entry. He has asked you to simplify the way he inputs information into the new table. You will create a form based on the new Transfer Schools table. Create a Split Form using the Transfer Schools table as the source. Change the height of the AdmittingSchool field to be approximately half the current height.Switch to design view and then remove the layout from all the labels and fields. Shrink each field so it is approximately as large as it needs to be. Switch to layout view and then click record 12 3455 in the bottom half of the split form. Make sure all fields are still visible in the top half of the form. If not, adjust the controls so all values are visible. Move the CreditsTransferred field so it is to the right of the CreditsEarned field on the same row. Change the title of the form to Transfer Schools Overview. Save the form as Transfer Schools Form. Save and close the form.
Cala is hoping you can create a more print-friendly version of the query you created earlier for her to distribute to the Board of Trustees. You will create a report based on the Transfer Calculations query. Create a report using the Report Wizard. Add the Major, FirstName, LastName, Class, GPA, and LostCredits fields from the Transfer Calculations query. Do not add any grouping or sorting. Ensure that the report is in Landscape orientation. Save the report as Transfer Students Report and view the report in Layout view.
11	Now that you have included the fields Cala has asked for, you will work to format the report to make the information more obvious. Apply the attached Wisp theme. Group the report by the Major field. Sort the records within each group by LastName then by FirstName, both in ascending order. Adjust the text boxes so the values for the Major field are completely visible. Switch to Print Preview mode and verify that the report is only one page wide (Note: it may be a number of pages long).
12	Close all database objects. Close the database and then exit Access. Submit the database as directed.

Total Points

100

Created On: 07/11/2019 1 Exp19_Access_App_CapAssessment – Transfer Student Advising 1.2

ID		mSysRowId

1	efRL4oMb4NUS2rI62av5d7vFtsu539w28XD55Pg+szc=-~ESg7Fgxzj/ye7hPS0aExtA==

mSysRowId

Freshman

Sophomore

Freshman
3.15

Information Technology
Sophomore

Psychology
Freshman
2.6500000000000004

Information Technology
Freshman

Sophomore

Freshman

Business Administration
Freshman

Natural Science
Sophomore
3.0

Campbell

Information Technology
Freshman

Information Technology
Sophomore

Natural Science
Freshman

Junior

Liberal Arts
Freshman

Natural Science
Sophomore

Engineering Science
Sophomore

Business Administration
Sophomore
3.0

Computer Science
Freshman
1.7000000000000002

Medical Informatics
Sophomore

Information Technology
Sophomore

Information Technology
Sophomore
1.8

Elementary Education
Freshman

Riya
Engineering Science
Sophomore
2.75

Sanchez

Junior
2.6500000000000004

Information Technology
Freshman

Business Administration
Sophomore
2.2

Business Administration
Sophomore

Business Administration
Sophomore
1.5

Information Technology
Freshman
1.8

Business Administration
Sophomore

Psychology
Junior

Majida

Freshman

Sophomore
1.9500000000000002

Computer Science
Sophomore

Nursing
Freshman
1.5

Bhatnagar

Elementary Education
Sophomore

StudentID	LastName	FirstName	Major	Class	GPA
123450	Sullivan	Ellen				Natural Science						Freshman		3.0
123451	Hepburn	Audrey								Information Technology	3.15
123452	McGraw	Adriana			Elementary Education								Sophomore		1.8
123453	Wilson	Cornelius		Medical Informatics	2.0
123454	Ennis	Claudius			Psychology		1.5
123455	Conway	Edgar			Engineering Science
123456	Connelly	Rosaline			2.6500000000000004
123457		Campbell	Ross
123458	Blair	Isabella		1.7000000000000002
123459	Bard	Jessica						Business Administration	3.2
123460	Abernethy	Robin	Theater	3.3000000000000003
123461	Adair	Margaret	3.6500000000000004
123462	Bain	Bianca
123463	Paris	3.95
123464	McFadden	Nathaniel			Computer Science			Junior	2.0500000000000003
123465	Pillai	Nishi		2.2
123466	Abdul	Farrah	2.4000000000000004
123467	Mukopadhyay	Rakesh		Liberal Arts	3.7
123468	Bettar		Majida		2.75
123469	Flores	Tomas	1.75
123470	Kang	Kyong	3.5500000000000003
123471	Hayashi	Kenji
123472	Gonzalez	Adolfo
123473	Ito	Yukio		1.9500000000000002
123474		Sanchez		Riya	2.9000000000000004
123475	Pawar	Arun
123476	Kwon	Kyu	3.0500000000000003
123477	Gutierrez
123478	Alfonso	Music
123479	Zhu	Gang	3.25
123480	Yoshida	Toshio
123481	Mitchell	Anthony	2.5500000000000003
123482	Matsumoto	Kazuo
123483	Baker	Christopher
123484		Bhatnagar	Dhruv	2.3000000000000003
123485	Wang	Dai	3.9000000000000004
123486	Hajjar	Sociology	2.8000000000000003
123487	Cho	Moon		Nursing
123488	Kadam	Priyanka	3.1500000000000004
123489	Patil	Harish
123490	Raj	3.45

SELECT DISTINCTROW *
FROM [Transfer Students];

Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

Calculate the price of your order

Type of paper needed:

Pages:

You will get a personal manager and a discount.

Academic level:

We'll send you the first draft for approval by at

Total price:

$0.00