[AccessD] Progressive SQL Update Query

[James Button]

Maybe post the SQL that sets up the entry set for the sampling.
And then that that manipulates the first sampling needs.
And then that that manipulates the second needs.
And then that that manipulates the third (and final?) sampling needs.

As well as whatever script is used to generate the sampling process request from
the data.

Without that sort of detail your post is pretty much a statement along the lines
of:

"Somebody has made at least one error in the design and/or scripting."

JimB
 

-----Original Message-----
From: AccessD
<accessd-bounces+jamesbutton=blueyonder.co.uk at databaseadvisors.com> On Behalf Of
Ryan W
Sent: Wednesday, February 22, 2023 2:36 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Progressive SQL Update Query

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?
>
>
>
>
>
>
>
>
>
>
>
>
>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com