Query that can filter using field name

[Kemidan2014]

So I am considering altering an Access database a prevoius employee set up for another department to help make sort sheets. basically when they select a product and fill in some basic information it creates report and a sub form to list out areas to be sorted.
the way original person did this is they built a table to list out 79 possible "areas" we reference for sorting in the plant.
then they built another table (to which i think they stopped using the first) to copy this table over and over and over again with added fields of part name, type of part etc. they added a Check box field to denote what areas required for that product in case they need to make changes in the future. this new table is now over 8000 records long

i feel like the SAME thing could have been done using the FIRST short 79 record table by adding Yes no fields and naming the fields by the product name and enabling each area as needed for each product name (basically instead of going Down with the table, running it accross)

Is it possible to change the form that would use this table to reference field names to achieve this instead of current which is a query that is searching by product type in a list of 8000??

 

[Micron]

If your form is based on a query, then as long as you can get the results out of the query that you want, the form can show that. However, I suspect whoever built the db did so with an Excel design approach, which is about the worst thing you can do in a relational database. The fact that the approach is to frequently add tables and/or table fields suggests that to me, and you seem to be prepared to do worse by adding more fields. If that is the case and you don't fix it, you will always struggle. Database tables should be tall and related to other tables, not wide like spreadsheets. I'd recommend you research db normalization if you are not familiar with the concepts of it.