[AccessD] Progressive SQL Update Query

[Ryan W]

https://i.imgur.com/34g0Srj.png


Here is an image of an example I was working on yesterday:

Sample 1 had Chromium and Nickel off

So hitting "toggle" turned Chromium and Nickel ON on #2 (accurate)

And subsequently everything went OFF for Sample #3 (also accurate based on
current query logic)

The user then toggled Chromium off on Sample #2 and hit Toggle and it
turned his Chromium back on for #2, and #3 was still all off.

I guess the gist of it is if the list is hand manipulated it resets the
list and anything > run #2 gets entirely turned off.





On Wed, Feb 22, 2023 at 8:09 AM Ryan W <wrwehler at gmail.com> wrote:

> 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?
>
>
>
>
>
>
>
>
>
>
>
>
>
>