Inspection Sheet

[Greg]

I am building a database to store information from housing inspections. I
have 29 question each with a Pass/Fail check box, a floor indicator(What
floor the defect is on), and a Direction (which wall). I am not sure the best
way to go about creating this.
In addition to this Each sheet has a Builder, Subdivision, Lot,
Superintendent, Crew Leader, Inspector, and Inspection Date. I am having
trouble setting up the heirarchal structure.
I would appreciate any help. I am relatively new to Access and may need a
little bit more focused response.

 

[tina]

"each sheet" = one inspection, correct? if so, something along these lines
might be appropriate:

tblQuestions
QuestionID (primary key)

QuestionText



tblInspections

InspectionID (pk)

Builder

Subdivision

Lot

Superintendent

Crew Leader

Inspector

InspectionDate



tblInspectionAnswers

AnswerID (pk)

InspectionID (foreign key from tblInspections)

QuestionID (fk from tblQuestions)

Pass/Fail

Floor

Direction



note: assuming that the Floor and Wall data will only be entered for a
particular question when Pass/Fail = Fail, i might remove those two fields
to a separate table, as



tblInspectionAnswers

AnswerID (pk)

InspectionID (foreign key from tblInspections)

QuestionID (fk from tblQuestions)

Pass/Fail



tblAnswerDetails

AnswerID (primary key AND foreign key from tblInspectionAnswers)

Floor

Direction



hth

 

[tina]

my original post is a mess, sorry. here it is again, tried to clean it up
for easier reading:

"each sheet" = one inspection, correct? if so, something along these lines
might be appropriate:

tblQuestions
QuestionID (primary key)
QuestionText

tblInspections
InspectionID (pk)
Builder
Subdivision
Lot
Superintendent
Crew Leader
Inspector
InspectionDate

tblInspectionAnswers
AnswerID (pk)
InspectionID (foreign key from tblInspections)
QuestionID (fk from tblQuestions)
Pass/Fail
Floor
Direction

note: assuming that the Floor and Wall data will only be entered for a
particular question when Pass/Fail = Fail, i might remove those two fields
to a separate table, as

tblInspectionAnswers
AnswerID (pk)
InspectionID (foreign key from tblInspections)
QuestionID (fk from tblQuestions)
Pass/Fail

tblAnswerDetails
AnswerID (primary key AND foreign key from tblInspectionAnswers)
Floor
Direction

hth

 

[Duane Hookom]

Consider a normalized structure similar to At Your Survey found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Greg]

I set it up that way but I still have a few questions about
inputing the data. I would like to show all of the questions
in a form at the same time so that it looks exactly like the
inspection sheet.
Also, you were right that I only need Floor and Direction
when PassFail = Fail. Does setting it up the second way you
described allow me to only have that come up when it is
Fail?

--
Greg

my original post is a mess, sorry. here it is again, tried to clean it up
for easier reading:

"each sheet" = one inspection, correct? if so, something along these lines
might be appropriate:

tblQuestions
QuestionID (primary key)
QuestionText

tblInspections
InspectionID (pk)
Builder
Subdivision
Lot
Superintendent
Crew Leader
Inspector
InspectionDate

tblInspectionAnswers
AnswerID (pk)
InspectionID (foreign key from tblInspections)
QuestionID (fk from tblQuestions)
Pass/Fail
Floor
Direction

note: assuming that the Floor and Wall data will only be entered for a
particular question when Pass/Fail = Fail, i might remove those two fields
to a separate table, as

tblInspectionAnswers
AnswerID (pk)
InspectionID (foreign key from tblInspections)
QuestionID (fk from tblQuestions)
Pass/Fail

tblAnswerDetails
AnswerID (primary key AND foreign key from tblInspectionAnswers)
Floor
Direction

hth

 

[Greg]

I spent some time looking at this but I may not have enough knowledge
to figure it out. Does what Tina said sound like it will work?
The end goal is to be able to sort this by crew, superintendent, subdivision.
And eventually do some small calculations.
I appreciate your time.
Thank you
--
Greg

Consider a normalized structure similar to At Your Survey found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Duane Hookom]

Tina has suggested something similar to At Your Survey. She proposed a table
of Questions and a table of Inspection Answers. This is much like At Your
Survey. You asked about a form to display all questions which is a form in
At Your Survey.

--
Duane Hookom
MS Access MVP
--

I spent some time looking at this but I may not have enough knowledge
to figure it out. Does what Tina said sound like it will work?
The end goal is to be able to sort this by crew, superintendent,
subdivision.
And eventually do some small calculations.
I appreciate your time.
Thank you

 

[Greg]

I deal with mostly construction guys that have never or almost never
used a computer for everything. When I look at that survey it takes me
a couple of minutes to take it all in; they would just quit and not use it.
I need something that is much simpler both in appearence and in design.

I have taken an introductory course in Access but it appears that it was
not enough. I think that your design may be too sophisticated for my
knowledge level.

 

[tina]

I only need Floor and Direction

when PassFail = Fail. Does setting it up the second way you
described allow me to only have that come up when it is
Fail?

at the table level, moving those two fields to a separate table simply
prevents having a lot of records where those two fields are blank (i'm
assuming that for a lot of questions, Pass/Fail will = Pass). it saves
space, mainly. at the form level, it will be easy enough to set up a data
entry form where those two fields only show when Pass/Fail = Fail.

I would like to show all of the questions
in a form at the same time so that it looks exactly like the
inspection sheet.

that's "fairly" easy to do, but i hesitate to try directing you in a post,
since you say that your Access experience is limited. if you want to post
your email address, i'll use the table design i posted to set up a few
"quick and dirty" forms to provide an example of how you can set up the data
entry for ease-of-use, and then email the demo db to you so you can look at
how i did it.

if you want to go this route, make sure you "disguise" your email address,
or you'll get spammed to death. something like
myemailDELETEALLCAPITALSaddressatsomethingMORECAPITALSdotcom

hth

 

[Greg]

gregbCONSTRUCTIONSUPERINTENDENTatintegratedstuccoPRODUCTIONASSISTANTdotcom
Thanks

 

[tina]

"test" email sent.

gregbCONSTRUCTIONSUPERINTENDENTatintegratedstuccoPRODUCTIONASSISTANTdotcom
Thanks

 

[Duane Hookom]

The survey application is just a demo of how your questions can be
organized. It would be up to you or someone you contract with to make it fit
your situation.

 

[Greg]

I could not get your database in Outlook. I sent you a message on your email.
Thank you