Create a list of fieldnames = "True"

  • Thread starter Thread starter CevinMoses
  • Start date Start date
C

CevinMoses

How can I generate a list of fieldnames, data type "Yes/No", that have a
"True" value and have this list show up in the detail section of a report for
each record?

I am building a database to track the preventive maintenance being done on
the ~100 machines in our shop. I have a report sorted by the [model] so that
each machine gets its own 1 page check list with things like model number,
serial number, frequency of inspection, and tasks to perform.

It's important to use a specific type of grease (marina grease,
nonconductive grease, all purpose grease, way oil, Never Seez...) for certain
areas of certain machines. I can distinguish exactly which one to use in the
task list (which is a memo field). However, I want to add a list of supplies
needed for each machine to appear on the bottom of each checklist to make it
easier for them to make sure they have the right supplies before they go off
and start the task. Each model of machines is the primary key, [model], and
the record contains lots of other information about that particular model.

I started with a memo field called [PM_Tools] and listed each tool on the
data entry form. I had to hit <Ctrl><Enter> to move to the next line in the
text box control to type the next tool, and sometimes I found that the same
tool was called 2 different things. From a data enty standpoint, it became
much easier to create fields for each tools with a data type Yes/No so that I
could just check a checkbox for that tool on the data entry form if that tool
was required to do that PM. My new problem with the report is that I can't
figure out how to list the tools that I have checked off as needing to
complete that PM.

Any suggestions?
 
Sounds like you've made the mistake of having a Boolean column for each task
to be performed (in other words, if there are a maximum of 20 tasks to be
performed, you've got 20 Yes/No fields in the table, each one with the name
of one of the tasks).

What you should have, instead, is another table that has one row for each
task that's supposed to be performed. (i.e. if machine 1 has 4 tasks that
need to be performed, there would be 4 rows in the other table, each one
pointing back to the row for machine 1. If machine 2 has 8 tasks that need
to be performed, there would be 8 rows in the other table, each one pointing
back to the row for machine 2. And so on.). You'd then be able to use a
concatenation function such as
http://www.mvps.org/access/modules/mdl0004.htm at "The Access Web"
 
Back
Top