What would be the best design to accomplish this "Yes/No" style checklist?

[stormwaterguru]

What would be the best design to accomplish this "Yes/No" style checklist? (see attached)

I have tackled so many things in my journey learning access, yet my brain refuses to process the proper way to turn a form like the attached into something usable in Access. I'm fearing it's out of my depth (I was poking around the forum for "checklists"), so please don't hold back any new ways to think about implementing this. This is database for use of 3 people, primary user being myself.

I played around with a mock design; ended up with a junction table between the Subject of the inspection [top of the form, loosely] and then the Inspection Criteria [the checklist portion] WITHOUT a yes/no setup. Simple, but implementing this into a form with basic skills resulted in a listbox...on a continuous form. I'm sure you can imagine. Combo box is not any better. Both mean that you're left to only select items that had an issue rather than perform/record an evaluation on ALL measures. While it technically produced a sufficient enough report with the information gathered (because I can indeed say: these are the things that must be addressed), the interface of the form was unsightly, not efficient nor intuitive and seem prone to cause selecting the same item more than once/other errors. When I take a step back, I think my struggle means the design is likely problematic fundamentally or I'm overlooking another way to approach it. And certainly overthinking it.

So far as I can tell, my options are:

• Gizmo style checklist (not sure that I am capable but still need to find out if that is what is actually required to make this work)
• the Junction table method above and accept it will only be able to display marked items* & undesirable interfaces
• go against all developer recommendations and adopt MVF and only be able to display marked items*
• Scrap the form, start from zero and cry a little

*assumption; I'm sure I could get crafty. Also assume the "craftier" I get, the more I have deviated from a good design.

Hope this is the proper forum. I think it's part design, part translation of proper design into proper form. Thanks in advance!

 

[plog]

I think a form/subform gives you the look you want with the correct table setup. For lack of a better term you will need a feeder table, which sorts of acts like a junction table but is not technically connected to the main 2 tables of your database.

The top info (e.g. inspection date, owner, facility, etc.) goes into one table (Inspections) and becomes the main form. All the responses to the elements (e.g. Yes,No,N/A) elements go into another table (ElementResponses) and becomes the subform.

Now here's the magic, you have a third table (Elements) that actually hold the questions (but not the individual inspection answers) at the bottom of your current form and populates ElementResponses. So, Elements has data like this:

Elements
el_ID, autonumber, primary key
el_Type, text, holds value in 'BMP ELEMENT' column of current form
el_Issue, text, holds value in 'Issue' column of current form
el_Action, text, holds value in 'Corrective Action' of current form

Data would look like this in it:
1, Contributing Drainage Area, Excessive Trash/Debris, Remove trash...
2, Contributing Drainage Area, Bare exposed soil, Stabilize with Seed...
...
12, Aquatic Beach Vegetation, Plantings incosistent...., Consult approved plans...

You would have a main menu and a button that says 'Add New Inspection'. User clicks it and behind the scenes a new record is created in Inspections, you grab that new autonumber ID for that inspection and you run a series of APPEND queries that adds every el_ID from Elements into ElementResponses. Then you open your Inspection form to that new ID for the user. Because you added those records to ElementResponses they show up in the main form and your Access form very nearly resembles your actual paper form.

 

[GaP42]

Your form is similar to a questionaire and you may pick up some designs by searching for samples - plogs proposed arrangement is a valid starting model that follows a similar structure. For the responses to any question your form would probably use an option box control, where you might make the border of the option box not visible. Each option then translates the entry to the required value stored in the response table.
No MVF.

 

[Edgar_]

You could have 3 tables: facilities, inspectors and inspections.

Storing the entire checklist in the inspections table, though unconventional, may actually be a viable solution. While it might seem like a sacrilege, validly, the reality is that many inspection lists rarely change. In fact, some have remained identical for decades, even outlasting software like MS Windows. These checklists often stem from long-standing legislation and norms that have seen little to no updates, and inspectors typically don't stray from them. When conducting inspections, they only address the aspects required by law, and that’s the extent of their focus. That is, they won't require extra columns ever.

However, if the inspection involves multiple visits to the facilities, a separate visits table should be added to store data specific to each visit. But in my experience, most inspections are like an X-Ray of the state of the facilities and it's done in one visit.

If the inspection lists should change frequently or if the inspection list depends on the user or company, then you can use schemas such as the previously mentioned by the other posters. If the recommendations are going to change, that also should use the other schema. But, again, my experience is that these don't ever change, so they can be hardcoded in a report.

As for the UI:
- Unbound main form with subform listing inspections and an Add New Inspection button that opens the following form.
- The Form opened by the Add New button could easily be a popup form holding basic data, like facility and inspector with dates populating automatically. Once the user accepts this, it gets added to the subform list.
- The subform list should have one record for each inspection and each should have a button to view the inspection. Once the user clicks this button, the view changes from the list of inspections, to the inspection view.
- The inspection view is a form where the rest of the inspection can be filled and the yes/no/na fields can be filled with a combobox.

Since you didn't mention anything that could particularly involve Access, I am suggesting this alternative for how easy it is to implement. If you can share more thoughts about what you'll do with this data, then I might end up discarding this long post and favor the others based on that.

 

[stormwaterguru]

Your form is similar to a questionaire and you may pick up some designs by searching for samples - plogs proposed arrangement is a valid starting model that follows a similar structure. For the responses to any question your form would probably use an option box control, where you might make the border of the option box not visible. Each option then translates the entry to the required value stored in the response table.
No MVF.

I think a form/subform gives you the look you want with the correct table setup. For lack of a better term you will need a feeder table, which sorts of acts like a junction table but is not technically connected to the main 2 tables of your database.

The top info (e.g. inspection date, owner, facility, etc.) goes into one table (Inspections) and becomes the main form. All the responses to the elements (e.g. Yes,No,N/A) elements go into another table (ElementResponses) and becomes the subform.

Now here's the magic, you have a third table (Elements) that actually hold the questions (but not the individual inspection answers) at the bottom of your current form and populates ElementResponses. So, Elements has data like this:

Elements
el_ID, autonumber, primary key
el_Type, text, holds value in 'BMP ELEMENT' column of current form
el_Issue, text, holds value in 'Issue' column of current form
el_Action, text, holds value in 'Corrective Action' of current form

Data would look like this in it:
1, Contributing Drainage Area, Excessive Trash/Debris, Remove trash...
2, Contributing Drainage Area, Bare exposed soil, Stabilize with Seed...
...
12, Aquatic Beach Vegetation, Plantings incosistent...., Consult approved plans...

You would have a main menu and a button that says 'Add New Inspection'. User clicks it and behind the scenes a new record is created in Inspections, you grab that new autonumber ID for that inspection and you run a series of APPEND queries that adds every el_ID from Elements into ElementResponses. Then you open your Inspection form to that new ID for the user. Because you added those records to ElementResponses they show up in the main form and your Access form very nearly resembles your actual paper form.

I am partially following and also a bit tripped up. Would the response table be something like

PK​

InspectionFK​

MarkedResponse​

el_ID​

​

An option group on/as the subform...and is then the subform a continuous form so that it is creating individual entries/records (i.e. placeholder for appending) for each element item?


(still stewing and processing....my above understanding can't be correct....that would be an update not an append because I'd be creating the record already, sans Element. Head is swirling....my second understanding (or current thought) is that inspection record created, "APPEND queries that adds every el_ID from Elements into ElementResponses" means it populates the whole group of elements into the response table BEFORE opening, so that when form IS opened, it has each item listed and then you select the Yes/No/NA from the option group select??? Am I following???)

 

[stormwaterguru]

You could have 3 tables: facilities, inspectors and inspections.

Storing the entire checklist in the inspections table, though unconventional, may actually be a viable solution. While it might seem like a sacrilege, validly, the reality is that many inspection lists rarely change. In fact, some have remained identical for decades, even outlasting software like MS Windows. These checklists often stem from long-standing legislation and norms that have seen little to no updates, and inspectors typically don't stray from them. When conducting inspections, they only address the aspects required by law, and that’s the extent of their focus. That is, they won't require extra columns ever.

However, if the inspection involves multiple visits to the facilities, a separate visits table should be added to store data specific to each visit. But in my experience, most inspections are like an X-Ray of the state of the facilities and it's done in one visit.

If the inspection lists should change frequently or if the inspection list depends on the user or company, then you can use schemas such as the previously mentioned by the other posters. If the recommendations are going to change, that also should use the other schema. But, again, my experience is that these don't ever change, so they can be hardcoded in a report.

As for the UI:
- Unbound main form with subform listing inspections and an Add New Inspection button that opens the following form.
- The Form opened by the Add New button could easily be a popup form holding basic data, like facility and inspector with dates populating automatically. Once the user accepts this, it gets added to the subform list.
- The subform list should have one record for each inspection and each should have a button to view the inspection. Once the user clicks this button, the view changes from the list of inspections, to the inspection view.
- The inspection view is a form where the rest of the inspection can be filled and the yes/no/na fields can be filled with a combobox.

Since you didn't mention anything that could particularly involve Access, I am suggesting this alternative for how easy it is to implement. If you can share more thoughts about what you'll do with this data, then I might end up discarding this long post and favor the others based on that.

Edgar, thanks for sharing! If I am understanding "Since you didn't mention anything that could particularly involve Access" this correctly, to answer: This is just one small piece of the DB. This inspection is one of a handful of other inspections required, and all of them are done year over year, if not more frequently. The program as a whole will (hopefully) track complaints, permit applications, project sites, public and private stormwater management structures (the piece being discussed), public stormwater infrastructures, etc., etc. You are correct, it is a regulated program. The inspections and forms will hopefully be set and mostly unchanging. However, all of these pieces have to come together to pull stats to report annually to EPA. As it stood, some of these pieces existed in spreadsheets, some of them existed on other departments' programs, some purely in the mind or word of a person and are all treated as separate entities when in reality they are all relational. That's how I arrived at Access.

 

[MajP]

Here are examples on how to build a checklist.

Making a custom checklist that saves to a table

Good evening all! I have a need to build a checklist form. We have 19 steps that our leads have to do every engagement. Items 1-4 are pre-engagement, 5- 8 are mid-engagement, and 9- 19 are post-engagememt. I need a checklist form that saves checked items for the next time the DB is opened and I...

www.access-programmers.co.uk

Solved - Three-part form

I am working on a form which is going to be based on three tables. It is logging incidents reported so first table has general details (date/time), second table flags incident as specific categories - can be multiple, and third table records details of person involved - also may be more than...

www.access-programmers.co.uk

 

[plog]

I am partially following and also a bit tripped up. Would the response table be something like

PK
InspectionFK
MarkedResponse
el_ID

An option group on/as the subform...and is then the subform a continuous form so that it is creating individual entries/records (i.e. placeholder for appending) for each element item?

Yes, that's it exactly. For each new inspection you make a blank record for each question that needs to be answered. That way the continous form is populated with all the records you need and shows how you want it.

still stewing and processing....my above understanding can't be correct....that would be an update not an append because I'd be creating the record already, sans Element

No, the VBA you run when they choose to make a new Inspection would be a series of APPEND (making new records). The user would then use the form to update the records by selecting Yes, No, N/A.

 

[Pat Hartman]

Here is a very simple sample of a survey database with a schema to handle multiple surveys. When a respondent adds a new response, the code in the AfterInsert event copies all the questions for that survey to tblSurveyResponseAnswers.

 

[stormwaterguru]

Yes, that's it exactly. For each new inspection you make a blank record for each question that needs to be answered. That way the continous form is populated with all the records you need and shows how you want it.



No, the VBA you run when they choose to make a new Inspection would be a series of APPEND (making new records). The user would then use the form to update the records by selecting Yes, No, N/A.

So, I was able to create a query that Appends the Elements into the Response table OR Appends the InspectionPK (inspID), but not how to accomplish both. The closest I have come and am not sure it is what you meant is the following Query:

INSERT INTO Response (Inspection, el_ID )
SELECT Inspections.inspID, 1 AS Expr1
FROM BMP LEFT JOIN Response ON Inspections.inspID = Response.Inspection
WHERE (((Inspections.inspID)=[forms]![Form3]![inspID]));

With "1 as Expr1" being the statement that sets the FK of the Elements PK and having to perform a separate query for each item on the Elements table??? Is that what you mean by series? In other words, I might have upwards of 30 Append queries?

Conversely, I had tried:

INSERT INTO Response ( el_ID, Inspection )
SELECT DISTINCT Elements.el_ID, [forms]![form3]![inspID] AS Expr1
FROM Elements LEFT JOIN Response ON Deficiency.el_ID = Response.el_ID;

With no success, but wanted to include.

Last edit: I realize I don't need to make Queries specifically and can run as and Insert Into statement in VBA.

 

[plog]

Last edit: I realize I don't need to make Queries specifically and can run as and Insert Into statement in VBA.

I think you got it now. This doesn't involve actual Query objects, it involves VBA that compiles your SQL and executes via DoCmd.RunSQL. You really only need 1 SQL statement if you use SELECT INTO from Elements:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[stormwaterguru]

@plog aaaaand SAD face. I thought I was all done haha. That looks completely different and even more foreign. I'll dig in and see if I can find some resources on how to implement the Select Into. The W3 is very clear and concise, but does not seem implementation into VBA is simply plug & chug so I must be missing some sort of syntax to introduce/use the function.

...try not to get "banished" until then, if you don't mind Either way, thank you for all your help so far. Much appreciated.

 

[stormwaterguru]

Here is a very simple sample of a survey database with a schema to handle multiple surveys. When a respondent adds a new response, the code in the AfterInsert event copies all the questions for that survey to tblSurveyResponseAnswers.

Pat, this is wonderful! I'm digging into it now to see if it can be modified to fit our program. Is there an appropriate way to structure this to have different sources for the responses(i.e., instead of a respondent, be tied to a Project OR a Structure/Property OR a Complaint, etc.)? The survey itself is perfectly fine to be modified for the relative inspection needing to be done, but the catalyst of those inspections are tied to different...assets, if you will. Inspection A needs to be related to Project, Inspection B needs to be related to the structure it was performed on, Inspection C needs to be tied to a facility, etc. However.....the data from each is still tied to one single program.

 

[Pat Hartman]

Is there an appropriate way to structure this to have different sources for the responses(i.e., instead of a respondent, be tied to a Project OR a Structure/Property OR a Complaint, etc.)?

Easy. A respondent is a respondent. Add a type field to the table if you want to categorize them. And add a FK field if you want to relate them to other tables. One caveat though. If the FK can be a projectID or a StructureID or whatever, you won't be able to enforce RI if you use the same field to hold the different IDs. If you only have a couple of different options, you could add multiple FK fields and use only the one related to the type. Then you can enforce RI.

Having the FK's will complicate your reporting if you want a single report with all the questionnaires in one. If this is the case, I would probably make each different type a subreport but the more options you have the more complicated it gets so think about the ramifications long and hard before you start building other stuff using these tables. What do you really need to see from the other tables in any consolidated reports? Single type reports will never be a problem. They will just need unique queries because each will be joining to a different "type" table and pulling different data fields.