I need a piece of database software written for either MS Access or PHP/MySQL (explain your choice) that will dynamically generate a resale price for real estate properties as they are added to the database. I need it to filter out which properties are listed at 20% or lower of the average sales price per sqft for that particular subdivision. Please add in some extra time for tweaking this workflow. Here is the workflow as I imagine it at this point:
Download property data into database (MySQL or desktop MS Access file), with current status (A=active,P=pending sale, S=Sold, etc). See attached spreadsheet for data setup (header "S" = current status).
-- Each upload can be assumed that it’s the most recent data, so update data if the ML# matches one already in database. Data that may change: status (“S”), list price.
-- The ML# will be unique
-- Data will be in CSV format
-- Add/update field "last update", date and time each unique record was updated or added
-- Add/update fields “LP-Sqft” and “SP-Sqft” to be calculated on updates
-- A, AC status generates “LP-Sqft” number on new and updated records
-- S status update generates “SP-Sqft” number field for that ML# record and stored
Generate this report on demand: pull up all records where status=A, AC, and where the list price/sqft is <80% of that property's "SUBD" average SP-Sqft
To calculate unique SUBD values:
-- Average SP-Sqft for those within the same SUBD is viewable at the top along with subject property information
-- select usable criteria for SUBD pricing using these fields: MAPSCO page, Zip (within 1 or 2 numbers), then middle 8 characters of SUBD using %LIKE%, and records updated within 9 months. (Note: we may need to modify how this searches depending on results). This is to narrow down records to those that are nearby to the subject record and are in the same subdivision. If the SUBD is less than 8 characters, use the entire field.
-- question: SP-Sqft for each SUBD is updated upon uploading and saved as field in a separate SUBD table, or process these calculations upon demand?
-- Report’s table show last 9 months of records where status=S (or all of them, if less than 9 months of data), showing the SUBD name for visual inspection for each record. Table is sortable by SUBD. Fields for results table: ML#, Address, City, Zip, Property Type, Bd, FB, St, YrBlt, Sqft, SoldPrice, Sold Price/Sqft, Frcslr, Subd.
-- Check off properties to calculate SP-Sqft (select all/none buttons); automatically selects all
-- SP-Sqft average is calculated by adding Sold Price numbers, then dividing by the sum all Sqft numbers of those selected records