NJIT MIS 385

Ms Access Assignment

Submission instructions

What to submit:

• one Ms Access file (.accdb)

Questions

This exercise is connected to "Topic 1.3. Creating a Table in Ms Access". Please watch the videos and complete this exercise simultaneously.

In this exercise, we will create the Students table in Ms Access. The Students table keeps the following information about each student:

• ID number (9 digits of the type 000-00-0000)
• Name (broken down by its components)
• Address (broken down by components)
• Major
• Gender identity
• Birth date
• Whether or not the student is on financial aid (it simply keeps yes or no)
• Credits taken so far
• GPA

Part A. Create the table

1. Open a new Access file. It automatically opens a table with a column called ID. This is the primary key field that Access automatically assigns. It has datatype of "Autonumber", which means that Access will assign IDs automatically by generating numbers. We can change this default setting later.
2. Save the Access file with the name "Students".
3. Name the table as "Students".

Part B. Columns and their data types

1. Switch to "Design view", and change the name of the ID to StudentID. You may enteran explanation like "Student ID number from registrar" in the "description" area. Leave the data type as Autonumber for now.
2. In the next row, create a field named "FirstName", choose datatype "Short Text".
3. Repeat the step above for "MiddleName" and "LastName".
4. Create four new fields for the address data: "StreetAddress" , "City" , "State", and "ZipCode". You need to decide what data types to choose for each. Note that "ZipCode" should not have Number as data type, because zip codes do not have numeric meaning (it does not make sense to subtract or multiply zip codes). Zip codes just happen to be codes that use characters that are numbers.
5. Create fields: "Major", "Gender", "BirthDate", "FinancialAid", "Credits", and "GPA". Choose appropriate data types for each. For "FinancialAid", select "Yes/No" datatype.

Part C. Columns and their field properties

In this part, we are going to add extra settings for each of the fields created previously. To do that, for each field, inspect "Field properties", and make the changes below.

1. Start by going over all the fields created. For each of the fields, should we enforce it to never be NULL (i.e. left empty)? For example, it makes sense to enforce that "FirstName" and "LastName" are never left NULL, but not the same for "MiddleName" since some people do not have one. Additionaly, for major, we may allow it to be NULL since the student may have not declared a major yet. Under "Field Properties", select Yes in Required, for those fields that you want to prevent from ever being left NULL.
2. For each of the fields that have data type Short text, choose an appropriate character length under Field Size. For example, for "FirstName", a field size of 40 characters is likely enough since it is unlikely to find a first name that will have more than 40 characters; on the other hand for "ZipCode", we know for sure that we will need at most 9 characters and never more than that. Be conservative choosing the length, but not too conservative, because recall that the storage space for Short Text is proportional to the maximum number of characters allowed.
3. For "ZipCode", create an input mask corresponding to the zip codes.
4. For the "BirthDate" field, under format, select the best format to display the birth date.
5. For "FinancialAid" field, chooseformat "Yes/No" instead of the default "True/False".
6. For "Credits", choose an appropriate field size (think about the maximum number of credits a student can have). Again, be conservative, but not too much (a student will never have 1,000,000 credits).
7. For "GPA", choose an appropriate field size. Note that GPA is not a whole number in general. Under Decimal Places, choose an appropriate number of decimal places.
8. Back to the first field, "StudentID". It should be an ID number with 9 digits of the type 000-00-0000 (SSN format), therefore, Autonumber does not work. Choose an appropriate data type and respective field size, and then apply an input mask so it shows up with the SSN format.

Part D. Add data

The table design is now complete. Switch the view to "Datasheet" instead of "Design", andenter some data (at least three records).

Part E. Submit the Students.accdb file to Canvas.

Save all your work, close Ms Access, and submit the .accdb file to Canvas.