Can't figure out how to do an Form to identify pending tasks

Hi!

I need help building an airtable database to solve this problem:

  • We are installing 200 mechanical machines at a site.
  • Each of the machines must be inspected following a 6 page form, each page could be considered a section (Electrical, Mechanical, Fluids etc…)
  • Each section has around 20 questions, to which the only possible answers are “OK” “Not OK” and “Not Applicable” in a checkbox way.

At the end of the day the purpose of this is to know the “pending/to be fixed tasks” of each machine, organized by sections.

So what I need to do is a database (open to other solutions, tried with AirTable but couldnt figure it out) that works as a form for capturing all the answers to those questions, divided by sections, for each one of the machines. Once the data has been captured, I need to be able to QUERY on the data, for it to show me the following:

  • Which machines have a pending task
  • What Section of the questionary showed the “Not OK” answer marked
  • What the "Not OK"item marked was.

- Example of the OUTPUT:
MACHINE 15 Section: Mechanical | Pending: Fix Bearings Section: Electrical | Pending: Replace Cable
MACHINE 27 Section: Fluids | Pending: Leaking Gaskets Section: Electrical | Pending: Replace Cable

The way I can think about it is Something like this:

IF NOT OK then mark item; IF item marked THEN mark Section; IF Section marked THEN mark Machine Print Marked (MACHINE, SECTION, ITEM)

This way we can know what exactly is missing… I am VERY lost on how to achieve this so I would really appreciate the help. I had set up a questionnaire with a column for each question on a single table, but then when i want to filter everything to sho the “Not ok” answers only, I cant because I would need to filter through each question looking for the not ok.