(solution) MGIS 3310 Assignment 1: Create a Database using Microsoft Access

(solution) MGIS 3310 Assignment 1: Create a Database using Microsoft Access

Create a database using Access, instructions attached. 

MGIS 3310 Assignment 1: Create a Database using Microsoft Access 2013 or above (100 points = 10%)
FOR HOW-TOs, READ THESE TUTORIALS FIRST:
How to use MS Access: https://support.office.com/en-us/article/Access-training-a5ffb1ef-4cc4-4d79-a862-e2dda6ef38e6?ui=enUS&rs=en-US&ad=US#ID0EAABAAA=Access_2016 To create tables: https://www.youtube.com/watch?v=p_I54RvlYUU
To create forms: https://www.youtube.com/watch?v=Rj0ZBLlbsRc
To create queries: https://www.youtube.com/watch?v=QRQhBVF-6Ng
For query criteria: http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx
To create a relationship between tables: https://www.youtube.com/watch?v=mEkOWAEF-nQ
1. Create the following two tables. (10 pts.) Create fields and their properties as follows. Manually enter the data on page 2 into these tables.
Customers Table Orders Table Customer No (Primary Key) (formatted as autonumber)
First Name (shorttext)
Last Name (shorttext)
City (shorttext)
State (shorttext)
Number of Purchases (number)
Total Amount of Purchases (currency)
Date of Last Purchase (date/time) Order No (Primary key) (autonumber)
Customer No (number)
Total Price (currency)
Order Date (date/time) 2. Create a form for Customers table by using Form Wizard. Include all the fields. (10 pts.) Using this new Customers form, enter the following two additional customers:
o
o Amy Adam, Houston, TX, 5, $2500, 8/15/2011
John Doe, Austin, TX, 10, $3500, 3/15/2011 3. Create the following queries. (10 pts. each) ** You need to review the query links listed above. **
3.1 Display Last Name, City, State, and Number of Purchases of all customers. Sort the results by State in descending
order. (Result: all seven customers)
3.2 Display First Name and Last Name of all customers whose city is unavailable in the data. (Result: Claudia Julie)
3.3 Display Last Name, City, State, and Number of Purchases of all TX customers who have purchased more than 4
times between 7/1/2011 and 9/1/2011. (Result: Adam)
3.4 Display Last Name, City, State, and Number of Purchases of all customers who EITHER have purchased more than
4 times OR made the last purchase after 7/1/2011. (Result: Julie, Adam, and Doe)
3.5 Display Last Name, City, State, and Number of Purchases of all TX customers who EITHER have purchased more
than 4 times OR made the last purchase after 7/1/2011. (Result: Adam and Doe)
Note: You will need to create a relationship between the two tables in order to create the following queries, because they
include columns from both tables (unlike the above queries including only columns from Customers table). The
relationship is One-to-Many based on Customer No (the common column), because one customer can have many orders.
3.6 Display Order No, Customer No, First Name, Last Name, City, State, and Total Price of all TX customers. Total price
of the selected order must be greater than or equal to 2000. (Result: no one)
3.7 Display Order No, Customer No, First Name, and Total Price of all customers EITHER whose second letter of the first
name is the letter a OR whose Total price is greater than 100. (Result: Mike, Kamphol, Pam, and Sanjay)
4. Create a report for any one of the above queries by using Report Wizard. (10 pts.) Data for Customers and Orders tables NOTE: It is OK if your numbers in the autonumber columns are different (e.g. 6 instead of 7). HOW TO SUBMIT
Click Assignment 1 link in Blackboard, and then on the next screen click ?Browse My Computer?. Select the database file of this assignment (.accdb file), and then click Submit.