Forms for an Inspection Checklist

[Chip]

Howdy from Oklahoma!!!, I am relatively new to ACCESS and very new to VB so
please be gentle...

Here is my situiation, I built an extremely large table to contain about 60
'yes/no' fields of an inspection checklist. I have been told that this was
wrong database archetecture of tables. Ok, i goofed up, but i was told to
create 3 tables as follows:

"tbl_FacicliyID_InspID" that has FACID and InspID as fields that would
relate a Inpestion ID# for each Inspection conducted at a facility. (each
facility will have multiple inpections)

"tbl_InspQusnID_ QusnDesc" that has InspQusnID that is an ID# for each
queation on the chaecklist and QusnDesc is the field that has the
Inspection Question text that will be shown on the form that will be
answered by the user.

"tbl_InspID_InspQusnID_Score" that has the relationship of the InspID to
the InspQusn and keeps the 'Score' (the Yes/No answer) for each question.

How do i build forms/subforms to show ALL the checklist items? I have not
been able to get
forms/subforms to show me all of the questions at the begining of inputting
a checklist, it is just showing when they are answered. I am wanting to
build queries that will pull all the negative (the questions answered "No")
trends from the checklists.

Any help would be greatly appreciated, I know I may need to post to the
Query and Tables groups for further guidance but i want to make sure i have
my tables correct or if there is a better way of doing this.

The original large table is currently working except that I cannot build a
query just pull the negitive answers (the No answers) for all the questions
of the checklist. Basically to show trends where problems are present.


As alwasys, THANKS IN ADVANCE!!!!!

 

[Michel Walsh]

Hi,

May be not the most economical, in development time, but here a possible
implementation.


-- design
I assume you use a split database (backend for the data, front end for
the application). I assume you have a limited number of facilities. I would
use one local table (front end) for each "facility" and I will create a form
for each, using the temp table to be associated. As example, formU would
have the ten checks associated to facility U and formV will have the 12
checks associated to facility V.

When it will be time to display an inspectionID from facilityU, I would
first copy the pertinent data from the back end table into my local front
end table, then display the formU for edition, then, on close, push back the
data from the temp table to the back end table (common to all users). To
create a new inspection, I would start with a copy of a defautU table to the
local tempU table, and proceed as previously.

-- main form
I could use a TabContol, on tab per facility, a the appropriate form
previously made, as subform. In other word, under tab FacilityU, I would
have the formU as subform, and under tab FacilityV, formV as subform. The
main form, the one with the TabControl, will be in a state hinting the
process to be preformed ( Me.NewRecord = true, a new record is tentatively
added, otherwise, the Me.InspectionNumber supply the possible information to
be displayed ).

If the InspectionNumber can only refer to just one Facility, then, I
would rather use a single subform rather than the TabControl, and change the
Source property of the subform, to "formU", or to "formV", as appropriate
(since that scenario assume that knowing the InspectionNumber determines the
facility, so the form, to use, as subform). In this case, I would also add a
FormUnknown, a form used a subform when the facility is still not determined
(such as when we append a new record, we still do not know the facility this
inspection would refer to, so, displaying a "blank" subform is more
appropriate than otherwise).

-- reporting
To get all the "false" score, I would rather use a report based on the
back end table, records being "filtered" for the wanted inspection, or use a
list box (locked) displaying the description of every item that got wrong.


-- optimization
Furthermore, extra work, but I am not sure I would store a record with
two fields, one of the field being a Boolean... the mere presence of the
record (first field) can be interpreted as a yes and its absence, a no (or
the reverse, depends on what is the more frequent). It is then easier to
SPOT a problem, if you store the problems, since a simple INNER JOIN will do
all the job for you.


I am conscious those explanations may need a lot of re-"hydratation" to
be digestible, ...

Hoping it may help,
Vanderghast, Access MVP