Home Inspection Table Structure

J

jerry525

Could someone help me with a jumpstart on the basic structure of this
database? It’s been a few years since I’ve worked with Access.

This database is for a small home inspection company. A buyer or seller of
a home hires the home inspector to provide a report on the condition of the
property. The data base has these components.

Major areas of the property consist of:

Grounds
Exterior
Foundation
Roof
Plumbing
Heating
Electrical
Interior
Garage
Kitchen
Bathrooms
Pool/Spa

Each of these major areas has specific categories that are inspected. For
example, the Grounds would have these things:

Driveway
Sidewalks
Retaining walls
Patio
Decks
Fences and gates

Each of these specific Categories need to be reported on. Using the
Driveway as an example, there are observations that the inspector needs to
call to the attention of the client through this report. These are some of
the observations he might comment on using a checklist approach and using a
check box approach to add the item to the final report:

( ) Appears serviceable
( ) Not functional
( ) Unsafe
( ) Near end of life span
( ) Eroded asphalt
( ) No cracks found
( ) Major cracks
( ) Evidence of poor drainage

Each of the above comments would be added to the Driveway part of the report
by activating the check box. For each of these items in the data base, the
item would not only have the checkbox for automatically adding it to the
report but also a description field attached to it so the inspector can add
whatever comment he deems necessary to describe his findings of that checked
item.

All of the above would be tied to a Customer ID, name, address, etc.

I envision a FORM for each section of the final report. For example GROUNDS
would be the title of a form and the title of a page in the report. Then
under GROUNDS, a sub section titled “Drivewayâ€. Then below that would be the
check box items applicable to the condition of the Driveway: i.e. “Appears
serviceable†(with a memo field), “Not functional†(with a memo field), etc.
He’d then check those appropriate conditions and add his comments to each.
Just those conditions would appear in the Driveway part of the GROUNDS page
of the report.

My problem is I can’t visualize how to set up the tables.

I suppose I’d have individual tables for the major areas, i.e. a table for
GROUNDS, FOUNDATION, ROOF, ETC. But then how do I incorporate for the
GROUNDS table the Driveway information versus the Sidewalks information, etc.
And then one layer deeper, how do I incorporate the check list items
appropriate to the Driveway with an appropriate memo fields for a freeform
narrative?

Any guidance would be much appreciated!

Jerry
 
A

Allen Browne

MainCategory table, with MainCatID field.
Has *records* such as:
Grounds
Exterior

Question table (one record for each thing you ask) with fields:
QuestionID primary key
MainCatID relates to MainCategory.MainCatID.
QuestionText Text
Has records like this:
1 Grounds Driveway condition
2 Grounds Sidewalks

Answer table (one record for each possible reply), with fields:
QuestionID relates to Question.QuestionID
AnswerNum Number (order of the questions in survey)
AnswerText Text (what this question says)
Has records like this:
1 1 Appears serviceable
1 2 Not functional

Inspection table, with fields:
InspectID AutoNumber pk
InspectDate Date/Time
EmployeeID who did this inspection

InspectAnswer table, with fields:
InspectID relates to Inspection.InspectID
QuestionID relates to Question.QuestionID
AnswerNum which answer applies
Comment Text (or memo?)

Ultimately this structure is what you use for a survey. For an example
database, go to:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
scroll down to Hookom, Duane, and grab the At your Survey sample.
 
J

jerry525

Thank you Allen for your speedy and thorough response. I’m starting in on
that structure today.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top