[AccessD] Progressive SQL Update Query

[Ryan W]

I'm not sure how to explain this without explaining poorly or not getting
the message right.

We have data we get from clients and they ask us to analyze them for trace
metals,
so the client list wants to know about 10 trace metals out of 30.

How it currently works is we run the analysis and import the data, our
software pulls it in and matches the requested list to the analysis and
turns off all trace metals not requested.

Sometimes one (or more) of the metals requires re-analysis for whatever
reason. So the user toggles the 'bad' hits out of the sequence. (say they
turn off Lead and Copper)

So we re-run it and import it and if the data is good, they turn off
everything BUT Lead and Copper.

In some cases, we have a third analysis. (sometimes we have to dilute the
sample to get a good reading)...

So say in that second analysis, Copper was not good. So they turn it off..
the third Analysis gets copper ONLY turned on for reporting.


Right now the toggling is all done by the user.

I want to write a progressive query that looks at the aggregated analysis
for anything requested (SEL), that's OFF and turn it ON (or vice versa) on
the following analysis (this works).

However, if there's a third analysis in the sequence everything ends up
turned off because Analysis 1 and 2 meet the requested analysis.

So in this example:

Sample 1's list contains all trace metals, but lead and copper are off
Sample 2's list after hitting "toggle" button contains lead and copper and
everything else is off.
Sample 3 is completely off because the aggregated list of required trace
metals are satisfied by 1 and 2.

However Sample 2 needs user intervention and turns off Copper.  I want them
to be able to hit "Toggle" again and turn ON Copper for Sample 3, but leave
Copper OFF on Sample 2, even though Sample 1 doesn't satisfy the copper
requirement.

My query logic works fine if there's only 1 rerun, but if there's more than
one is where the problem lies.


I don't know if I need to use a cursor or a recordset and a looping
mechanism to make this work instead of a straight up batch query?