Best way to search multiple fields of one table? (part 2)

Discussion created by argus2 on May 12, 2018
Latest reply on May 13, 2018 by argus2

This original question was asked by Walt105 on Sept 14, 2016 and was answered.

I have a similar question but didn't want to steer that discussion in a different direction, so I started this "part 2" :


I, at times, cause myself grief and this was brought to light by one of philmodjunk's responses in that original discussion:

Best way to search multiple fields of one table?

"I'm sure that there are valid cases for using it, but in my own work, if I come up with the need to search for the same data in multiple fields of the same record, I find that I have a poor design and those multiple fields should actually be multiple records instead or I wouldn't have the same data in those multiple fields."


In my case, I believe poor design is the culprit.

If my attachment can be opened, you will see a screen shot of the dilemma.

The database (one table) tracks hundreds of jobs (with their estimated task times) and each job can have up to 16 different tasks all performed on different days. Each date is it's own field from a drop down calendar that corresponds to the task. ie Date_Task1, Date_Task2, Date_Task3 and so on.

This has been working great until someone asked me how many hours I worked on a particular day. The answer seemed simple. Just set up a find of all jobs for (example) "April 23, 2018" and let the calculation fields do their thing from the found set. That's where the poor design may come into play. All the information is recorded but I can't find a way to search multiple (date) fields, over all the records, for a particular date.