LEARNING OBJECTIVES Applicable course objective: • apply information systems concepts to identify and resolve problems within a business context • apply information systems skills to develop practical...

LEARNING OBJECTIVES
Applicable course objective:
•    apply information systems concepts to identify and resolve problems within a business context

•    apply information systems skills to develop practical solutions within a business context

•    communicate information systems concepts to both technical and non-technical audiences within a business context.
Applicable graduate qualities and skills gained from this assessment instrument:

•    Problem Solving

•    Communications
Task 1: Create and Import
Create a new blank Access Database (using any version from Access 2007 to current) and name it [lastname] [initial] _ [student number] _ [course code] _ assign1.accdb (eg. genrichr_0050051005_cis1000_assign1.accdb).

Import the four sets of data from your firm’s Intranet (CIS1000 Study Desk).


The easiest and quickest way to import data into Access is by using the Import Text Wizard.  The following steps must be followed carefully to ensure the data is imported correctly:
Importing Tab Delimited Text files into Access (any version from Access 2007 to current) using the Wizard
1)    On the External Data tab, in the Import & Link group, click the Text File data source icon.
2)    In the Get External Data - Text File wizard popup, browse to find the ‘Assignment01 Text File Customers.txt’ text file and select the Import the source data into a new table in the current database option – then click OK.
3)    Select the Delimited – Characters such as commas or tab separate each field option on the first page of the wizard - then click Next.
4)    Select the Tab option on the second page of the wizard and turn on the First Row Contains Field Names check box - then click Next.
5)    Set the following for each field on the third page of the wizard:
•    Field Name – keep the field name as it appears when imported

Note: Changing Field Names rather than using Captions will result in loss of marks.
•    Data Type – giving each an appropriate data type (students will be marked on their logical choice of data types, based on the sample data provided).
Note: Numbers that will not be used for calculations should be set as Text. Numbers such as Freight Weight and Order Qty must be carefully and logically set to avoid loss of data (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double).

See the Beskeen et al textbook Access 2013 Unit E for more details on setting appropriate Data Types, particularly Table E-3: Common Number field properties for logical Data Types.
•    When you are sure that each field has the appropriate data type, click Next.
6)    Select No Primary Key option on the forth page of the wizard – these will be set in Task 2 - then click Next.

7)    Name the table tblCustomers on the final page of the wizard and click Finish
Task 2: Modify Table Design
Modify the four imported tables’ structures using the given Table Specifications, from the section above, in Microsoft Access using Table Design View:
•    Use the above table specifications, checking that you have the correct table names and field names.
•    Check that all fields have the appropriate Data Type from the following:
o    Text, Number, Date/Time, Currency, Yes/No, Hyperlink.
•    Add an appropriate Field Descriptions to ALL data field with a well-structured description, eg. for CustID an appropriate description would be:
Customer Identification | Data Type: Short Text: 3 Characters
•    Ensure that you also set properties for each field where appropriate. You MUST set  the following:
o    Field Size – set appropriate logical fields sizes for all Text and Number data types (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double)
o    Caption – set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID)
o    Format – set an appropriate format for all: Yes/No and Date data fields; Text data fields that should be stored in Upper-Case (State); Hyperlinks data fields that should be stored in lower-case (EmailAddress); and numbers that contain currency or percentages

Note: IE is the country code for Ireland – should be formatted in Euros; NZ is the country code for New Zealand – should be formatted in Dollars.
o    Decimals – set an appropriate number of decimal places for any single or double numbers
o    Default Value – set an appropriate default value for all Yes / No data Fields
o    Required – set any data Fields that must contain data to Required and leave any Fields where data is optional as unchecked.
o    Validation Rule and Validation Text – include appropriate Validation Rules for each of the following data Fields: Title; State; and Item Type. This ensures that only limited value options can be entered for these data Fields