SQL to use specific lot code for a field based on a Yes Selection in another field

[coffeeman]

I have an auto fill for a form to update other form fields after the selection. Once this selection is made for a certain box, that box field then auto fills its box lot code field on the form. For a certain box, there are multiple lot codes in the table. Is there a way to tell the SQL query to update the lot code based on the selection of the Current Lot Code Used (Yes/No) field?

Here is what the table looks like (abridged) that has the multiple entries of lot codes for one material (A Box). I want to make sure the one selected as YES is the one that will autoupdate when A Box is selected.

Product DescriptionBox Lot CodeCurrent Lot CodeA Box061482NoA Box061549NoA Box071055YesA Box071109ANoA Box071281NoA Box071285NoA Box071285NoA Box071395NoA Box071407No

Thanks for the help

 

[HiTechCoach]

If your data example is of a single record, then probably why you are having issues is due to the structure not being properly normalized by having repeating fields.

If you do not want to fix the table structure so that it it better normalized, you will have to write some VBA code to process the through the fields in the record to find the correct field. I would suggest making a User Defined function to do this since you may need to use this in other places.