Open Access and create a blank desktop database named Assignment3Q1_LastFirst.xl
ID: 3850974 • Letter: O
Question
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.
Explanation / Answer
I will create one database:
CREATE DATABASE Assignment3Q1_varsaleela.xlsx;
then create one table named properties:
CREATE TABLE PROPERTIES (ID AUTO NUMBER PRIMARY KEY,Data Listed DATE/TIME,DateSold DATE/TIME,ListPrice CURRENCY, SalesPrice CURRENCY,SqFeat NUMBER,Beds NUMBER,Baths NUMBER,Address SHORTTEXT,SubDivision NUMBER,AgentID NUMBER,Style SHORTTEXT,Construction SHORTTEXT,Garage SHORTTEXT,YearBuilt NUMBER);
enter the data into properties table using in the same way in quetion.
then create another table Agent:
CREATE TABLE Agent(AgentID AUTONUMBER PRIMARYKEY,FristName SHORTTEXT,LsatName SHORTTEXT,Title SHORTTEXT);
Then enter the records in Agent table:
INSERT INTO aGENT(AgentID,FirstName,LastName,Title) VALUES (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');
linked two tables with the relationship primary key & foreign key:
CEATE TABLE Properties(ID AUTONUMBER,DateSold DATE/TIME,sALESpRICE currency,SqFeat NUMBER,aGENTid number,PRIMARY KEY (ID),FOREIGN KEY (ID) reffrences Agent(ID));
then sort listprice lessthan 300000:
SELECT FROM Properties [ListPrice<300000] [ORDER BY colomn1,colomn2,.....,colomn10][DESC.ASC];
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.