Task 1: Create and Import
Create a new blank Access 2007/2010/2013Database and name it[lastname] [initial] _ [student number] _ [course code] _ assign1.accdb(eg. genrichr_0050051005_CIS5100_assign1.accdb).
Import the four sets of data from your firm’s Intranet (CIS5100 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 2007/2010/2013using 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 Filewizard popup,browsetofind 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 fieldoption on the first page of the wizard - then click Next.
4)Select the Taboption on the second page of the wizard and turn on the First Row Contains Field Namescheck 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
•DataType–giving each an appropriate data type
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 2010 Unit E for more details on setting appropriate Data Types
•When you are sure that each field has the appropriate data type, click Next.
6)Name the table tblCustomers on the final page of the wizard and click Finish.
Repeat the above 7 steps for the rest of the text files naming each Database Table as follows:
Text File Table Name
Assignment01 Text File Items.txt tblItems
Assignment01 Text File Suppliers.txt tblSuppliers
Assignment01 Text File Orders.txt tblOrders
Note:Table names must conform with theLeszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in Appendix 2.
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 Typefrom the following:
oText, Number, Date/Time, Currency, Yes/No, Hyperlink.
•Add an appropriate Field Descriptionsto describe the data field.
•Ensure that you also set properties for each field where appropriate. You MUST set the following:
oField 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)
oCaption– set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID)
oFormat– set an appropriate format for all: Yes/No and Date data fields;Text data fields that should be stored in Upper-Case (State);and numbers that contain currency or percentages
Note:AD is the country code for Andorra– should be formatted in Euros; SV is the country code for El Salvador– should be formatted in Dollars.
oDecimals– set an appropriate number of decimal places for any single or double numbers
oDefault Value– set an appropriate default value for all Yes / No data fields
oRequired– set any data fields that must contain data to Required and leave any fields where data is optional as unchecked.
oValidation Ruleand Validation Text– include appropriate Validation Rules for any data fields where there are a limited number of options (eg. Title and State)
See the Beskeen et al textbook Access 2010 Unit E for more details on appropriate settings for each Data Type
Before proceeding to Task 3, check the Data in the four tables against the Data in the four text files to ensure that there has been no loss of Data during Task 1 and 2.
Task 3: Set Primary Keys
Set the Primary Keysfor the four (4) Tables as per the following table specificationsin Microsoft Access using Table Design View:
Table Name Primary Key(s)
tblOrders CustID, ItemID, OrderDate
Note: The primary key of the Orders table is a combined multiple-field key. It has three fields in one key.
See the Beskeen et al textbook Access 2010 Unit A and the Video provided in the Course Content section for creating primary keys
Task 4: Create Relationships between the Tables
Create the following relationships between the four (4) tables:
•tblCustomers to tblOrders
•tblItems to tblOrders
•tblSuppliers to tblItems
Check that the correct table names and field names are listed in the ‘Edit Relationships’ screen.
Ensure that you ENFORCE referential integrity, but do not set Cascading Updates or Cascading Deletes.
You should check that the computer has identified correctly the 1 to ∞ relationships between the four (4) tables.
Note:This must be done after you have established the primary keys
See the Beskeen et al textbook Access 2010 Unit E and the YouTube videos on the StudyDesk for more details on creating relationships
Task 5: Create Data and Update Queries
Create the following five (5) queries using the Query Design View. Use the names given for each query to avoid confusion for the client (make sure you submit them in the order given below).
Note: Not all queries will require the inclusion of all four tables; you must decide which tables are appropriate for the generation of each query’s output. Only tables needed should be included in the query design as extraneous tables could cause problems with the query results.
Note: Query names must conform with the Leszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in Appendix 2 – including the Query Numberand Two Meaningful Words to describe the query eg. qry1XxxxxxXxxxxx.
See the Beskeen et al textbook Access 2010 Unit B, Access 2010 Unit F and Access 2010 Unit K for more details on creating queries
•Query 1: UnfulfilledValentine’s DayClothing & Apparel Orders:
Dr Kripke would like to see how many Clothing & Apparel orders were ordered for Valentine’s Day but were unfulfilled due to the items being out of stock. In the past the majority of Valentine’s Day Clothing & Apparel orders were placed in Januaryand were for items greaterthan 1.2kilograms.
Create a Select Query that will display the full Customer’sName (including Title, Given Names and Family Name), Full Item Details (includingType, Description and Freight Weight), Order Date and Order Qty for all Clothing & Apparelorders,placed in the month of January (using the BETWEENcriteria tool), weighing more than to1.2 Kg, and that are currently on order but not in stock.
Display the results sorted by Order Date, check that have you included all necessary fields and ensure that you only display the required fields.
Note: This Query will display 9rows of data if done correctly.
•Query 2: Southern Australian Bobbleheads Orders from a Supplier:
Dr Kripke would like to have a query that he can reuse with a manually entered Supplier Name, which will allow him to see how many customers living in Southern Australia (Tasmania, South Australia and Victoria) have purchased Bobbleheads obtained from the Supplier he has specified.
Create a Select Querythat will display the full Customer’sName (including Title, Given Names and Family Name), andFull Customer’s Address for customers who live in Tasmania, South Australia and Victoria(using the IN criteria tools), and the Item Type, Description, Dispatch Date and OrderQty for all Customerswho have purchased “Bobbleheads”obtained from a user specified Supplier.
Note: As no supplier name has been given, this query must use a Parameterin place of the supplier name.
Display the results sorted by Family Nameand State, check that have you included all necessary fields and ensure that you only display the required fields and test using Supplier’sName of“Galecki Imports”.
Note: This Query will display 11rows of data if done correctly.