Exp19_Access_App_CapAssessment – Student Loans 1.0

Exp19 Access App CapAssessment Student Loans 1.0 

EX19_AC_VOL1_GRADER_CAP_AS

Access  Application Capstone Assessment – Student Loan

Project Description:

Quill Financial Services has recently started working with student loans. You will build the company’s database. The initial set of financed student loans are stored in an Excel spreadsheet. You have already compiled the list of colleges and universities into an Access table. You will use your expertise to import the information from Excel, modify the table, create relationships, create queries, create forms, and create reports.

1

To start, you have been provided   with a database the Information Technology department created. The database   has one table. You will be importing an Excel spreadsheet into a table and   creating a primary key. 

Start Access. Open the   downloaded Access file named Exp19_Access_Ch03_CapAssessment_Student_Loans.accdb.

2

Import the exploring_acap_grader_a1_Clients.xlsx Excel workbook into a table   named Clients. While importing the data, make   sure to select the option First Row Contains Column Headings, and select   ClientID as the primary key field.

3

Now that you have imported the   data from the spreadsheet, you will modify the field properties in the   Clients table and demonstrate sorting.

Open the Clients table in Design   view. Change the ClientID field size to 6 and remove the @ symbol from the ClientID format   property. Change the ZIP field size to 5. Change the ExpectedGraduation field to have 0 Decimal Places. Delete the   Comments field. Add a new field named LastContact as the last field in the table.   Change the data type to Date/Time, and change the format to Short Date. Switch   to Datasheet View, and apply Best Fit to all columns. Sort the table on the   LoanAmount field in descending order, then save and close the table.

4

Now that the table is imported   and modified, you will create a relationship between the Colleges and Clients   tables.
 

  Open the Relationships window. Add the Clients and Colleges tables to the   window, and create a one-to-many relationship between the CollegeID fields in   the Clients and Colleges tables. Enforce referential integrity between the   two tables and select the cascade updates and cascade delete options. Save   the changes, and close the Relationships window.

5

Polly Esther, a financial   adviser, would like your assistance in helping her find certain information.   You will create a query for her and demonstrate how she can change information.
  Create a new query using Design view. From the Clients table, add the   LastName, FirstName, Email, Phone, and ExpectedGraduation fields, in that   order. From the Colleges table, add the CollegeName field. Sort the query by   LastName and then FirstName, both in ascending order. Set the criteria in the   ExpectedGraduation field to 2019. Run the query. Save the query as 2019 Graduates and close the query.

6

Now that you have created the query, you will create a second query   for Polly that will calculate the loan payments for which each student will   be responsible (assuming monthly payments).
 

  Create a copy of the 2019 Graduates query. Name the copy Loan Payments and open   the query in Design view. Remove the criteria from the ExpectedGraduation field.   Create calculated field named MonthlyPayment that determines the estimated monthly student loan payment. The loan   will have a fixed rate of 5% interest, paid monthly, for 10 years. Using the   Pmt function, replace the rate argument with 0.05/12, the num_periods argument with 10*12, and the present_value argument with the LoanAmount field. Use 0 for the future_value and type arguments. Format the field as   Currency. 

Run the query. Ensure the payment displays as a positive number. Add   a total row to Datasheet view. Average the MonthlyPayment field and count the   values in the LastName column. Save and close the query.

7

Stann Dupp, the director of   finance, needs to summarize information about all of the student loans Quill   Financial Services offers based on each college. You will create a totals   query for him to summarize the number of loans, average loan amount by   college.

Create a new query using Design   View. From the Colleges table, add the CollegeName field. From the Clients   table, add the ClientID and LoanAmount fields. Display the Total row, and   group by CollegeName. Show the count of ClientID and the average LoanAmount.

Change the caption for the   ClientID field to Num Loans, and the caption for LoanAmount to Avg Loan. Format the LoanAmount field as   Standard. Run the query. Save the query as Loan Summary by College and close it.

8

Jay Walker, one of the company’s administrative assistants, will   handle data entry. He has asked you to simplify the way he inputs information   into the Clients table. You will create a form based on the Clients table.

Create a Split Form using the Clients table as the source. Change the   height of all of the fields and labels to .25 collectively. Reorder the fields in the bottom half of the split   form so the FirstName displays before the LastName field. Switch to Form view   and click the row for Riya Gonzalez. Change her expected graduation date to 2022. Save the   form as Client Information and close it.

9

Stann is hoping you can create a   more print-friendly version of the query you created earlier for him to   distribute to the executives. You will create a report based on the Loan   Payments query.

   Create a report using the Report Wizard. From the Loan Payments query, add   the LastName, FirstName, Email, ExpectedGraduation, CollegeName, and   MonthlyPayment fields. Do not add any grouping or sorting. Ensure the report   is in Landscape orientation. Save the report as Loans by Client and view the report in Layout   view. Adjust the width and position of the fields and labels so that all of   the values are visible. Save the report.

10

Now that you have included the   fields Stann has asked for, you will work to format the report to make the   information more obvious.

   Apply the Integral theme. Group the report by the ExpectedGraduation field.   Sort the records within each group by LastName then by FirstName, both in   ascending order. Switch to Print Preview mode and verify that the report is   only one page wide (Note: it may be a number of pages long).

11

Save the database. Close the   database, and then exit Access. Submit the database as directed.

Subject: “Systems Analysis and Design”

Requirement: Chapter 10 Program Design.

Please complete Mini-case 1 (Attached) in, found on page 345 in the 6th edition text 

(page 403 in the 5th edition)  “Systems Analysis and Design”.

Task:

a. Develop a structure chart for this segment of the Holiday Travel Vehicles system.

You can use Visio, WORD, or any other modeling tool to display your structure chart 

b. What type of structure chart have you drawn in #a, a transaction structure or a transform structure? Why?

We will build a set of console-based programs that helps a grocery store keep track of its special customers and its product inventory. The inventory level for a product, like a bottle of soda, increases when a supplier delivers them. The level decreases when a customer purchases one or more of the product. We allow for customers to return some items for a refund. Occasionally, a product is recalled so we have to return some units back to the supplier for a reimbursement.

Main program #1 allows us to add (or delete) products in our Application. We will create this program in class. We will use the program to create the Product file (ProdInv.dat) for the project.

Main program #2 allow us to (create and) update our list of special Customers. Customers are given an ID number, so it easier to keep track of them. They are given a credit limit and start with no debt. Customer #0 is a “dummy” representing customers that are not in our frequent shopper program, so remain anonymous. The dummy has a $0 credit limit. You will use your program to create a file (Customer.dat) storing the following customer information:

IdNameDebtCredit Limit0Dummy$0.00$0.001Dan Daley$0.00$200.002Lex Luthor$0.00$100.003Lana Lane$0.00$300.004Peter Parker$0.00$50.005Ben Bradley$0.00$50.00

Main program #3 is used to keep track of the activities for a single day. The program reads in the current product and customer information  from the files. Then it will reflect the current activities:

1) The program lists those items whose inventory is within 10% of its reorder point.

2) 20 bars of soap are delivered and the supplier is paid.

3) Lex Luthor buys 1/2 lb. of salami and a bottle of soda, if available.

4) 30 bottles of soda are delivered and the supplier is paid.

4) Peter Parker buys all of the milk that is in stock.

5) Ben Bradley returns 2 bars of soap that he bought earlier.

6) All of the remaining bread is thrown away.

The key data from each of these activities will be appended to a formatted text file (Activities.dat) that can be reviewed in the future. ProdInv.dat is updated to reflect these activities.

Task A5

Only PhD candidates contact me

 Discuss the philosophy or reasons behind the development of object-oriented languages. Cite at least three references. 

250 words 

Assignment:
Provide a reflection of at least 500 words (or 2 pages double spaced) of how the knowledge, skills, or theories of this course have been applied, or could be applied, in a practical manner to your current work environment. If you are not currently working, share times when you have or could observe these theories and knowledge could be applied to an employment opportunity in your field of study. 

Requirements:

Provide a 500 word (or 2 pages double spaced) minimum reflection.

Use of proper APA formatting and citations. If supporting evidence from outside resources is used those must be properly cited.

Share a personal connection that identifies specific knowledge and theories from this course.

Demonstrate a connection to your current work environment. If you are not employed, demonstrate a connection to your desired work environment. 

You should not, provide an overview of the assignments assigned in the course. The assignment asks that you reflect how the knowledge and skills obtained through meeting course objectives were applied or could be applied in the workplace. 

Exp19_Access_Ch02_ML1 – Game Collection 1.2

Project Description:

You have cataloged your vintage video games in an Access database. In  this project, you add two tables to a database you have started? One to  identify the game system that runs your game and the other to identify  the category or genre of the game. Then you will join each table in a  relationship so that you can query the database.

Start Access. Open the downloaded   Access file named Exp19_Access_Ch02_ML1_Games.  Grader has automatically added   your last name to the beginning of the  filename. Save the file to the   location where you are storing your  files.

Open   the Games table and review the fields containing the game information. Close   the table.
 

  Create a new table in Design view. Add the field name SystemID and select AutoNumber as the Data Type. Add the caption System ID. Set the SystemID field as the primary   key for the table.

Add   a second field named SystemName   with the Short Text Data Type.   Change the SystemName field size property to 15.   Add the caption System Name.   Save the table as System.

Switch   to Datasheet view and enter the following system names, letting Access assign   the System ID:
 

XBOX 360
PS3
Wii
NES
PC Game
Nintendo 3DS
 

  Double-click the border to the   right of the System Name column to set the column width to Best Fit. Save and   close the table.

Create   a new table in Design view. Add the field name CategoryID   and select AutoNumber as the Data   Type. Add the caption Category ID.   Set the CategoryID field as the   Primary Key for the table.

Add   a second field named CategoryDescription   and accept Short Text as the Data   Type. Change the field size property to 25.   Add the caption Category Description.   Save the table as Category.

Switch   to Datasheet view, and enter the following Category Description, letting   Access assign the Category ID:
 

Action
Adventure
Arcade
Racing
Rhythm
Role-playing
Simulation
Sports
 

  Close the table.

Establish   relationships in the database by adding the Category, Games and System  tables to the Relationships   window. Close the Show Table dialog box.  Create a one-to-many relationship   between the SystemID field in the  System table and the SystemID field in the   Games table, enforcing  Referential Integrity. Select the option to cascade   update the related  fields.

Create   a one-to-many relationship between the CategoryID field in  the Category table   and the CategoryID field in the Games table,  enforcing Referential Integrity.   Select the option to cascade update  the related fields. Save and close the   Relationships window.

Create   a simple query using the Query Wizard. From the Games table, add the GameName and Rating fields (in that order). Save the query using the title Ratings. Switch to Design view. Sort the   Rating field in ascending order.

Click   the gray column selector at the   top of the Rating field, then click and drag the column to move it to the first position of the query design grid.   Add a Total row, click in the Totalrow   of the GameName column, click the arrow,   and then select Count.  The records   will be grouped by rating, and the number of games will  be counted for each   group. Run, save, and close the query.

Create   a new query in Design view. Add the Category,   Games, and System tables to the query design window. Add the following   fields to the query (in this order).
 

GameName

CategoryDescription
   Rating
   SystemName
   DateAcquired

Sort   the query in ascending order by GameName and run the query. Save the query as   Game List and close the query.

Copy   the Game List query in the   Navigation pane and paste it with the name PS3 Games.   Modify the query in Design view by using PS3    as the criteria for SystemName. Remove the sort by GameName and sort  in   ascending order by CategoryDescription. Save and run the query.  Close the query.

Create   a new query in Design view. Add the Category,   Games, and System  tables to   the query design window. Add fields (in this order) to  display the name of   the game, its rating, the category description of  the game, and the system   name. You only want to display Wii games with  a rating of Everyone or Teen.    Because the system name is the same for the games you plan to pack and  share,   hide this value from the results. Save the query as Thanksgiving Games and run the query. Close the query.

 Do you feel the benefits of cloud computing are worth the threats and vulnerabilities? Have we arrived at a point where we can trust external agencies to secure  our most precious data? Please explain your answer. 

• 8.1 What is the difference between RFC 5321 and RFC 5322?
• 8.2 What are the SMTP and MIME standards?
• 8.3 What is the difference between a MIME content type and a MIME transfer encoding?
• 8.4 Briefly explain base64 encoding.
• 8.5 Why is base64 conversion useful for an e-mail application?
• 8.6 What is S/MIME?
• 8.7 What are the four principal services provided by S/MIME?
• 8.8 What is the utility of a detached signature?
• 8.9 What is DKIM?

In the document