Answer Happy

Please Show All work for steps and SQL code.
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/articl ... 8e6?ui=en- US&rs=en-US&ad=US#ID0EAABAAA-Access 2016 To create tables: To create forms: 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) Order No (Primary key) (autonumber) Customer No (number) Total Price (currency) Order Date (date/time) Number of Purchases (number) Total Amount of Purchases (currency) Date of Last Purchase (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: Amy Adam, Houston, TX, 5, $2500, 8/15/2011 John Doe, Austin, TX, 10, $3500, 3/15/2011 O O 3. Create the following queries. (10 pts. each) ** You need to review the query link below to create each query. ** DO NOT simply select the names in the results by using filters otherwise you will receive a zero. To create queries: For query criteria: http://office.microsoft.com/en-us/acces ... 66611.aspx
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 TX customers who EITHER have purchased more than 4 times OR made the last purchase after 7/1/2011. (Result: Adam and Doe) 3.5 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) Note: You will need to create a relationship between the two tables in order to create the following two 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. To create a relationship between tables: - https://support.office.com/en-us/articl ... 4ac6-9382- - https://support.office.com/en-us/articl ... 7574-c0e3- 574ee271500a?ui=en-US&rs=en-US&ad=US 4ba3-b3f8-06f2b9b4d9a9?ui=en-US&rs=en-US&ad=US
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. Finally, create a report for any one of the above queries by using Report Wizard. (10 pts.) Data for Customers and Orders tables * Customers Customer No First Name 1 Kamphol 2 Pam 3 Mike 4 Sanjay 5 Claudia + + + * (New) Orders Order No Y Last Name ▾ Wi Zelbst Pass Gu Julie Customer No 3 лнос 3 1 City ▾ State ▾ Number of Purchases Total Amount of Purchases Houston TX $200.00 Huntsville TX $500.00 Arlington VA $1,222.00 Little Rock AK $500.00 CA $10.00 5 2 4 Total Price 1 2 3 4 5 7 (New) NOTE: It is OK if your numbers in the autonumber columns are different (e.g. 6 instead of 7). Y $300.00 $100.00 $100.00 $50.00 $500.00 $2,000.00 Order Date ▾ 2/1/1994 3/2/2001 5/18/2003 3/12/2004 4/2/2005 9/1/2009 3 2 4 4 7 Date of Last Purchase ▾ 2/12/2001 1/11/2002 3/6/2004 5/7/1995 4/21/1985