IS 312 Homework 3: Access Queries [10+1 points]
Assigned 2/11 for Mon and 2/12 for TR; Due 2/18 and 2/19 respectively
• All the problems will use the “Restaurants-2018” Access database.
• All answers must be typed, 12-point font (or equivalent) to facilitate my remarks and corrections (2-point penalty for violation).
• Solutions must be assembled in the right order (2-point penalty for out of order).
• All questions must print out design view and datasheet view, with design view before datasheet view. [Design view can only be obtained through screen print]
o Order of assembly: Q1 design, Q1 data; then Q2 design, Q2 data, etc.
Problem 1 (3 points). Create query with AND and OR.
Create a query to display restaurant ID, city, state, annual sales, and square footage, for
those restaurants in California with sales not more than $400,000,
or
those restaurants in the cities of Miami or Orlando, with sales not less than half a million. [Watch out this part – “cities of Miami or Orlando”]
Problem 2 (4 points). Total query. Create a total query with grouping, interpret the outcome of the query.
Create a total query –
grouped by city,
count the restaurant ID,
sum on square footage,
min on date opened.
*** interpret the outcome of the query, in bullet form (so you will have three bullet points).
Problem 3 (4 points). Create a query with calculated fields.
Create a query with calculated fields –
1) Year_In_Biz, which is defined as the years a restaurant is in business, rounded to 1 decimal place;
2) Sale_Per_SqFt, which is annual sales divided by square footage, rounded to 0 decimal place.
3) Conditions: Year_In_Biz at least 18, AND Sale_Per_SqFt at least $350/ft