One of the most expensive purchases a consumer can make is his or her home. For buyers of-new homes, one benefit is not having to worry about repairs. However, an examination of house construction quality in the booming central Florida building market by the Orlando Sentinel and WESH-Orlando, found thousands of problems with the new homes.

Our series, “Building Homes: Building Problems,” uncovered a serious decline in craftsmanship in central Florida’s new homes. Among the major problems we found were a myriad of heating, ventilation and air conditioning issues, wall and foundation cracks and improperly installed windows. In addition, we found everything from major construction failures to simple poor workmanship.

Don Tracy, a senior projects reporter, asked me to help him build a Microsoft Access database. We had worked together on an Access database in the past, and I knew he had a working knowledge of the program. I didn’t realize the extent of project we would be undertaking.

Tracy was doing a quantitative study of new home quality in central Florida. As part of the project, Ron Resch, a local building inspector, was selected to train engineering students at the University of Central Florida’s home constructability lab to inspect homes built in 2001. My responsibility was to build the database for the inspections information and analyze the results.

The newspaper acquired a list of homes built in 2001 from local property appraisers. Clerks working for the newspaper and television station called homeowners and asked them whether they would be willing to participate in our study. Those names and phone numbers were submitted to the inspectors to set up appointments.

With the assistance of Resch, Tracy created a paper copy of an inspection report for the students. It had more than 800 categories of information. That should have been my first tip that Access was the wrong tool for this project because Access tables are limited in the number of fields. But at the time it seemed like the best choice, and it was a database the UCF students were familiar with.

I had two challenges in designing the database. First, I needed to make a form that somewhat resembled the inspection report used by the students for data entry. To achieve this 1 created multiple tables and linked them together using subforms.

I created tables that corresponded to the distinct sections of the inspection report. For example, there was a bathroom subform and a kitchen subform. Each table contained check boxes that indicated whether the student inspectors spotted a certain problem. There was a total of 17 subforms linked to the master form.

While the UCF students inspected the houses, Tracy obtained residential inspection data from the six surrounding counties for the past three fiscal years. We also acquired inspections conducted by the city of Orlando. The goal was to determine the pass-fail rate of the official inspections by county.

The cost for the data ranged from free to under $100, varying by county. The city of Orlando provided a Microsoft Access database, but it was read-only. We exported the tables into Excel and created a new database. Polk County provided text files. However, the data was in print format and looked like screen shots of a report. used Monarch to parse the data into fields and export it as a delimited text file.

Osceola County at first refused to provide anything other than a printout; the press officer claimed that the database was not available in electronic format. After we spoke to someone in the technical department, we learned this was untrue and obtained the data in Excel. The remaining counties provided Excel spreadsheets that we easily imported into Access.

Unfortunately, the data sets had a couple of problems. Although we requested the past three years of data, the counties did not provide the exact same time periods. However, I felt there were enough records to show an inspection pass-fail rate.  

The second problem with the data was that every county had provided some duplicate records. used a select distinct query in Access to create a table with the unique values.
SELECT DISTINCT
Inspections.SITEADDRS,
Inspections.JOBDESCR,
Inspections.INSPCODE,
Inspections.CODEDESCR,
Inspections.inspecdate,
INTO
Deduped

FROM Inspections

After I compiled the results, I attempted to verify our findings with the building inspection departments of each county. Only one of the counties had calculated pass-fail rates, and the results matched ours to the decimal point.

In Access, I ran queries to determine the number of inspections daily and a failure rate by inspector. I then generated a list of inspections by day for each inspector. This helped find inspectors who appeared to pass a majority of the houses.

We found an inSpector who logged more than 90 inepections in one day. Using Maplnfo geographic information system, mapped the daily route for an inspector in each county, illustrating how little time was spent on each inspection because of travel time.

After UCF started entering data I needed to transfer it between UCF and the Sentinel to monitor the inspection results. Our technology department set up an FTP site and UCF uploaded the newest version of the database, with accompanying photos and videos, every Monday.

With the assistance of Resch, Tracy classified the problems found into three categories of severity: worth noting, concerns and priority problems. At this point i realized the restrictions of working in Access. I needed to group the problems into these categories using data from multiple tables.

The first hurdle was to convert the checkboxes on the forms into a data value. Even after converting the data to a value, was unable to run queries on the data. I solved this problem by converting the values into new tables, and used the new tables when running a query. To avoid re-creating my efforts, the UCF students downloaded a copy of the database had created with the queries and added data to that database.

When the inspections were almost completed, the editor for the project decided to put the database on OrlandoSentinel.com, our newspaper’s Web site. I knew that this would take an enormous amount of programming, and using Structured Query Language (SQL) was the best choice.

In an effort to be fair to the builders, every problem found was not published in the series. Before converting the data from Access in SQL, I asked Tracy and WESH producer Travis Sherwin which problems should be included in the Web version of the database. They refined the original list of problems down to about 200. (See the database online at  building problems.)

After having a pared down list of problems, sat down with a programmer from the Orlando Sentinel?s technology department assigned to build the Web database. Together we assigned each problem a location in the house  bathroom), a severity ranking, a system HVAC), and a system element  ductwork) for classification purposes.

After the inspections were completed, we exported the tables from Access into the Microsoft SQL Server database. There were more than 120 builders, more than 400 homes, and more than 200 potential problems in each home.

On the inspection subforms, created a comments box for problems not listed on the inspection sheet. To include this data in our database, I sorted through more than 800 separate comments to be classified. In many cases I had to create a new field for problems that had not been anticipated when the form was created. For example, there were 37 homes with mold on the HVAC unit.

I was put in charge of all the data analysis and statistics for the series. I signed off on every number and kept a spreadsheet listing every stat I checked and the date so I could easily refer back to what I had done when. This was vital because the numbers were flying everywhere, and there were several instances in which preliminary numbers made their way into the final versions of stories.

One of the main difficulties encountered was the reporter and editor changing the severity of a problem several times. Unlike the data gathered from the inspection reports, the severity rating was the most subjective part of the series. For example, a problem originally classified as a “priority problem” was changed to a “concern” the afternoon before a segment was scheduled to air on WESH. At 3 pm. I was scrambling to help Sherwin with the updated numbers for the segment running at 6 pm.

I also worked closely with the graphics department, checking off on every chart or graphic. This also paid off. A chart forthe pass-failure rate of inspections was mistakenly labeled New Home lnSpections, when it was residential inspections of homes of all ages, a category that includes renovations of older homes.

Thanks to this diligence, there were not any corrections or errors in the data was responsible for. There were a couple of corrections because data was entered incorrectly into the database by students, despite our efforts to compare written reports to the electronic database.

The response to the series has been very positive. The Web page devoted to the database generated an incredible number of hits and the message boards are full of homeowners reporting similar problems. Tracy had to set up a separate voice mail account to handle all the phone calls in response to the series. A state senator has said he will push for tougher standards on new home construction, and the local Home Builders Association has started to expand a homeowner advocacy program.

Contact Katy Miller by e-m’ail at kmiller@orlandosentinel.com.