Open Access and create a blank desktop database named Assignment3Q1_LastFirst.xlsx (Last is replaced by your last name and First is replaced by your first name).

Create a new table named Properties using the following field names and corresponding data type:

Field Name Data Type

ID AutoNumber (primary key)

DateListed Date/Time

DateSold Date/Time

ListPrice Currency

SalesPrice Currency

SqFeet Number

Beds Number

Baths Number

Address Short Text

SubDivision Number

AgentID Number

Style Short Text

Construction Short Text

Garage Short Text

YearBuilt Number

Switch to Datasheet view. Type the first 10 records as shown in the figure above.

Open the Assignment3Q1.xlsx workbook file in Excel. Click row 2, press and hold the left mouse button, and then drag through row 70 so that all the data rows are selected. Click Copy in the Clipboard group on the HOME tab.

Return to Access and click on the asterisk (*) on the first new row of the Properties table. Click

Paste in the Clipboard group to paste all 69 rows into the Properties table. Save and close the Properties table.

Create another table named Agents using the following field names and corresponding data type:

Field Name Data Type

AgentID AutoNumber (primary key)

FirstName Short Text

LastName Short Text

Title Short Text

Enter the following data in the Agents table. When finished, save and close the table.

AgentID FirstName LastName Title

1 Kia Hart Broker

2 Keith Martin Agent

3 Kim Yang Agent

4 Steven Dougherty Agent in Training

5 Angela Scott Agent in Training

6 Juan Resario President

Establish a relationship (linking a primary key and a foreign key) between the two tables by doing the following:

Click the DATABASE TOOLS tab and click Relationships in the Relationships group. Add both tables to the Relationships window and close the Show Table dialog box.

Drag the bottom border of the Properties table downward until all fields display. Drag the AgentID field from the Agents table and drop it onto the AgentID field in the Properties table. Click the Enforce Referential Integrity check box in the Edit Relationships dialog box to activate it. Click Create and close the Relationships window. Click Yes to save your changes.

Use Sort & Filter feature to list properties with a list price less than $300,000 and with two bedrooms. Hint: Open the Properties table. Click Advanced in the Sort & Filter group and click Filter By Form. Set the criteria to identify properties with a list price less than $300,000 (You will use the expression <300000 for the criteria of the list price) and with two bedrooms. Display the results and sort by ascending list price. Use Toggle Filter in the Sort & Filter group to toggle the original results and filtered results. Save and close the tableYou are the senior partner in a large, independent real estate firm that specializes in home sales. Most of your time is spent supervising the agents who work for your firm. The firm needs to create a database to hold all of the information on the properties it has listed. You will use the database to help find properties that match the goals of your customers. You will create the database, create two tables, add data to both tables, and create a relationship. Refer to the following figure as you complete this question.