This is a trivial problem for someone out there with more brains than I've got.

My DB keeps track of cleans for coolrooms. The operators use up to four different cleaning products. They use whichever batch no. is 'Current'.

My customer needs to keep track of the dates during which a particular batch was used. The detail of the calculation is simple enough; it's the structure that has me tricked.

I've ended up with four extra table, one for each product. The fields in the tables are simply Date Started, Batch #, If Current. This just seems kludgey. It's also a PITA because this is a runtime solution and upgrades mean importing existing data - 4 imports to go wrong just for a bit of trivia!

I considered repeating fields for a second or two but they're out for a few reasons, one of which is that whatever number of repetitions you choose, it will be 1 less than you need.

Another alternative is permanent 'stacks' of batch info in each coolroom record. This is also a bit kludgey and difficult to print.

Am I missing some obvious solution?

You need a single table for Products: a field takes care of the product type for you. You'll need a table for the Batch which is related to the Products.

I'd have another table for Coolrooms, and a Cleaning table that effectively joins the Coolroom and Batch tables.

Thanks, Vaughan. I did consider that but rejected it on the grounds that I hate setting up a table with just four records; it also involves a messier method of getting at the current batch. Must admit it is a neater solution; I think I'll do it.

(The second half is well taken care of.)