INFO321 MS Access Assignment
Quick MS Access assignment, must have knowledge in MS Access.
I have attached the instructions and file needed to begin the assignment.
AgencyNo AgencyName Street City State Zip AgencyPhone
1 Big Cedar Realtors 123 Second Street Branson West MO 77889 555-666-7700
2 Sunset Cove Realtors 7878 Lake View Lane Branson MO 77770 555-555-8800
3 Green Mountain Realty 9900 West Sunset Drive West Branson MO 77990 555-999-9999
4 Shepherd of the Hills Realtors 7744 Pokeberry Lane Hollister MO 77660 555-888-4444
Type
Cabin
Condo
Log Cabin
Patio Home
Ranch
Two Story
ListingNo Type SchoolDistrict SqFt LotAcres BR Bath Garage Pool Asking RealtorNo ListingDate
1 Ranch Ozark Mountain 3400 0.5 4 4.0 2 false 108,000.00 1 1/3/17
2 Ranch Ridgedale 3000 0.5 2 3.0 0 false 154,800.00 1 1/4/17
3 Cabin Ridgedale 2900 0.5 3 2.5 2 false 162,000.00 12 1/30/17
4 Cabin Horseshoe Bend 1200 0.4 4 3.0 2 false 180,000.00 2 2/28/17
5 Ranch Ridgedale 2500 1.0 3 2.5 0 false 238,800.00 12 3/31/17
7 Two Story Ozark Mountain 3000 1.0 4 4.0 2 false 331,200.00 2 4/30/17
8 Two Story Shell City 1800 0.4 3 3.0 2 false 165,600.00 13 5/31/17
9 Ranch Horseshoe Bend 2000 1.5 3 3.5 0 false 126,000.00 7 2/28/17
10 Ranch Ridgedale 2000 0.5 3 3.0 2 false 300,000.00 8 3/31/17
11 Ranch Ridgedale 1215 0.5 3 2.0 0 false 102,000.00 1 2/28/17
12 Ranch Greenview 2200 0.7 3 3.0 2 false 474,735.60 2 4/30/17
13 Two Story Blue Eye 3000 0.5 4 3.0 2 false 239,940.00 3 4/30/17
14 Two Story Greenview 5500 0.4 4 2.5 3 true 233,400.00 12 5/31/17
15 Two Story Branson West 2500 0.6 3 2.0 2 false 227,880.00 3 1/4/17
16 Ranch Branson 2700 0.6 4 3.0 2 false 227,880.00 4 3/31/17
17 Log Cabin Cape Fair 2000 1.0 3 2.0 0 false 227,880.00 4 4/30/17
18 Ranch Kimberling City 2500 1.2 3 4.0 2 false 227,880.00 13 5/14/17
19 Two Story Shell City 3200 0.9 3 4.5 0 false 225,000.00 4 5/31/17
20 Two Story Kimberling City 2700 1.0 3 3.0 2 false 177,480.00 4 6/14/17
21 Patio Home Kimberling City 2200 0.4 2 2.0 1 true 167,880.00 4 6/30/17
22 Cabin Kimberling City 1350 0.7 2 2.0 2 false 153,480.00 5 1/3/17
23 Two Story Galena 2000 0.5 4 1.0 0 false 149,880.00 5 1/30/17
24 Patio Home Galena 1200 0.4 3 2.0 0 false 144,000.00 6 4/30/17
25 Cabin Kimberling City 1900 0.5 3 1.0 0 false 134,280.00 8 3/31/17
26 Ranch Lampe 3000 2.0 3 2.5 3 false 479,998.80 8 5/14/17
27 Cabin Oakmont 2000 1.2 4 3.0 0 false 336,000.00 13 3/1/17
28 Patio Home Oakmont 3200 1.0 4 4.0 2 true 336,000.00 13 6/30/17
29 Cabin Cape Fiar 1500 1.0 2 1.5 1 false 270,000.00 5 1/3/17
30 Two Story Shell City 2200 1.0 3 2.0 1 false 360,000.00 12 1/30/17
31 Patio Home Shell City 1400 1.2 2 1.0 0 false 228,000.00 12 4/30/17
32 Cabin Oakmont 2400 2.0 3 2.0 1 false 234,000.00 13 3/31/17
33 Ranch Oakmont 3300 1.0 3 3.0 2 false 486,000.00 13 5/14/17
34 Cabin Oakmont 2200 0.7 2 2.0 0 false 348,000.00 1 3/1/17
RealtorNo RFirst RLast RPhone AgencyNo
1 Aaron Kelsey 555-222-8877 1
2 Phil Kirkpatrick 555-111-9900 1
3 Jessica Podor 555-333-3344 3
4 Malika Thompson 555-444-7788 3
5 Jane Ann Welch 555-223-0044 2
6 Shari Duncan 555-228-5577 2
7 Trixie Angelina 555-220-4466 2
8 Mary Baldwin 555-888-7777 1
9 Deb Trumpet 555-333-0123 1
10 Cy Clone 555-888-9999 4
11 Rob Zacharias 555-888-1111 4
12 Mindi Schwartz 555-888-0000 1
13 Andrew Barker 555-999-2222 2 Access Independent Challenge 3:
a) Start Access and open the LakeHomes-6.accdb database from the location where you store your Data Files, then enable the content if prompted.
b) In Query Design View, create a query with the following fields in the following order:
a. AgencyName from the Agencies table
b. RFirst and RLast from the Realtors table
c. SqFt and Asking from the Listing table
c) Sort the records in descending order by the SqFt field.
d) Save the query BySqFt, view the datasheet, enter your last name (Sierra) instead of Schwartz for the listing with the largest SqFt value.
e) In Query Design View, modify the BySqFt query by creating a calculated field that determines price per square foot. The new calculated fields name should be PerSqFt, and the expression should be the asking price divided by the square foot field, or [Asking]/[SqFt].
f) Remove any former sort orders, sort the records in descending order based on the PerSqFt calculated field, and view the datasheet. Save and close the BySqFt query. ##### means the data is too wide to display in the column. You can make the data narrower and also align it by applying a Currency format.
g) Reopen the BySqFt query in Query Design View, right-click the calculated PerSqFt field, click Properties, then change the Format property to Currency. View the datasheet then save and close the BySqFt query.
h) Copy and paste the BySqFt query as CostSummary.
i) In Design View of the CostSummary query, delete the RFirst, RLast and SqFt fields.
j) View the datasheet, change the Big Cedar Realtors agency name to your last name (Sierra) followed by Realtors.
k) In Design View, add the Total tow, then sum the Asking field and use the Avg (average) aggregate function for the PerSqFt calculated field.
l) In Datasheet Ciew, add the Total row and display the sum of the SumOfAsking field. Widen all columns as needed, save and close it.
m) Close the LakeHomes-6.accdb database, then exit access.
