Determine The Length Of Time Each Property Was On The Market

Determine the length of time each property was on the market before it sold

Your task is to manage Dysan Investment's database, which contains all information regarding listed and sold properties. You are required to analyze the sales data to determine how long each property was on the market prior to sale, calculate commissions for sales, and summarize the data by employee and subdivision.

First, you will open the provided Access database named Exp19_Access_Ch03_ML2_Investment_Properties.accdb. Then, create a new query in Design View, incorporating data from all three tables: Agents, Properties, and SubDivision. You will include the following fields in order: LastName (from Agents), DateListed, DateSold, SalePrice, SellingAgent, and ListingAgent (from Properties), along with Subdivision (from SubDivision).

Apply a criterion to filter out properties that have not been sold, i.e., where DateSold is not empty. Run the query to verify that only properties with sale dates appear, and then return to Design View. Format the SalePrice as Currency for clarity.

Next, you are to create calculated fields within the query: a) DaysOnMarket, which computes the number of days between listing and sale, with a caption of "Days on Market"; b) ListComm, which calculates the listing agent's commission at 3.5% of SalePrice, formatted as Currency with the caption "Listing Commission"; and c) SellComm, which calculates the selling agent's commission at 2.5% of SalePrice, also formatted as Currency with caption "Selling Commission." Save this query as "Sales Report."

Switch to Datasheet View, add a Total row, and compute the average days on market, total sales price, total listing commissions, and total selling commissions. Save the query and then create a copy named "Sales Summary by Last Name." In this version, remove the fields DateListed, SellingAgent, ListingAgent, and Subdivision, and switch to Design View. Group data by LastName, with the Total row set to Where for DateSold, and calculate the sum of SalePrice (captioned "Total Sales"), average of DaysOnMarket (formatted as Fixed), and sum of ListComm and SellComm. Show the sum of Total Sales in the Total row, adjust column widths for clarity, and save this query.

Then, create another copy called "Sales Summary by Subdivision." Open this query in Design View, replacing the LastName field with Subdivision. Group data accordingly, sort the results based on the average DaysOnMarket from shortest to longest, and limit the output to the top five subdivisions with the lowest average Days on Market. Save and close all objects, exit Access, and submit the database as instructed.

Paper For Above instruction

Managing real estate sales data involves analyzing key performance metrics such as time on market and commissions earned by agents. In this scenario, the goal is to utilize Microsoft Access to extract meaningful insights from a comprehensive database of properties, agents, and subdivisions associated with Dysan Investment.

The first step in the process is opening the provided database file, which consolidates all relevant data. Using the Designer in Access, a query named "Sales Report" is constructed to foster a detailed view of recent sales. This query integrates data from the Agents, Properties, and SubDivision tables, focusing specifically on properties that have been sold, indicated by non-NULL DateSold entries. By including fields such as LastName, DateListed, DateSold, SalePrice, and the names of the selling and listing agents, the foundation is laid for subsequent calculations.

To analyze the duration properties remained on the market, a calculated field called DaysOnMarket is created. This field subtracts the DateListed from DateSold, providing a precise count of days each property was available prior to sale. Formatting this value as "Days on Market" enhances readability, especially when multiple properties are assessed simultaneously.

In addition, the query calculates the commissions for agents involved in each transaction. The ListingCommission, set at 3.5% of the SalePrice, and the SellingCommission, at 2.5%, are added as calculated fields. These fields are formatted as currency to facilitate straightforward financial interpretation. These calculations help assess the profitability of each sale and the earnings of the agents involved.

Once the "Sales Report" query is finalized, reviewing the data in Datasheet View with a Total row provides aggregate insights. Computing averages and sums — such as the average days on market and total commissions — offers a macro-level perspective on sales performance. This foundational report serves as a basis for more specialized summaries.

Building upon this, the "Sales Summary by Last Name" query refines the analysis to evaluate employee performance. By grouping data by employee last names, reviewing total sales (captured as Total Sales), average days on market, and cumulative commissions, the report highlights individual agent productivity. Removing extraneous fields in Design View streamlines the focus, while setting criteria to filter for specific data enhances relevance.

The next step involves creating the "Sales Summary by Subdivision" report, which shifts the focus from individual agents to neighborhoods or subdivisions. This report sorts the subdivisions by the average days on market in ascending order, spotlighting areas with the quickest sales cycles. Limiting the output to the top five subdivisions emphasizes key areas that outperform in turnover rate.

Completing this process involves saving the queries, closing the database objects, and ensuring all steps are accurately executed. Overall, this analytical approach provides valuable insights into sales efficiency, agent performance, and subdivision attractiveness — essential metrics for strategic decision-making within real estate investment firms.

References

• Batini, C., & Scannapieco, M. (2006). Database Design, Application, and Development. Auerbach Publications.
• Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
• 2. Roussopoulos, N., & Sellis, T. (1995). Query processing techniques for spatial database systems. ACM SIGMOD Record, 24(2), 15-24.
• Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts (6th ed.). McGraw-Hill Education.
• Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Kroenke, D. M., & Boyle, R. J. (2017). Database Processing: Fundamentals, Design, and Implementation. Pearson.