[AccessD] Progressive SQL Update Query

Ryan W wrwehler at gmail.com
Wed Feb 22 15:54:21 CST 2023 

The description I gave was condensed to try and avoid confusion but here's
a longer explanation:

The Sample records logged in have tests requested by testcode (Sample,
SampleTest, SampleSelection(analytes))

Then upon Import we have AnalRun, AnalRunSequence, AnalRunSeqResults.

The samples are logged in, then analyzed and imported into an Analytical
Run, which contains the sequence (AnalRunSeqeuence) off the instrument with
the test type requested (with a TestNumPK/TestNumFK) relationship which
fills in AnalRunSeqResults with the FULL analyte list  for the TestNum in
question with only what's SELected toggled on for the analyst to
scrutinize.

It's not my system, but I do have to modify it on occasion.

What I want to happen is semi-automate the whittle-down process of which
analytes are considered trusted (Per Pauls discussion) over the series of
rereruns, if any.


At this point I think I have it with a cursor loop but I'm waiting for my
analyst to tell me which data I can test the procedure on that hasn't been
through the entire review process.



On Wed, Feb 22, 2023 at 3:47 PM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> Looks like a design issue.
>
> It appears that the issue is that you are using flags on Sample to
> indicate which metals  are
> tested on each test when you should actually have the flags on the test
> records, not the
> sample records.
>
>
> On 22 Feb 2023 at 9:03, Ryan W wrote:
>
> > Well, I can't share all the code there...
> >
> > So we take the data off the instrument (in a csv or txt file)
> > depending on the instrument, our Access FE parses it and sends it up.
> >
> > Every time a sample is imported from the flat file, every analyte that
> > the client requests is ON by default, it's up to the analyst to
> > discern whether the data is usable, hence why some things get run
> > multiple times.
> >
> > The goal is so have a progressive/cascading effect. Sample 1 comes in,
> > Chromium and Nickel get turned off, Sample 1 (#2) comes in, user hits
> > toggle and Chromium and Nickel are all that are on.  User realises
> > Chromium needs another look so Sample 1 #2 comes in, user hits toggle
> > and ideally only Chromium gets turned on for #3.   As stated the
> > current query resets the second run, there could be a third and fourth
> > but I think the way the query is currently written anything past 2 is
> > basically dead without hand holding from the analyst.
> >
> > Here's the code that basically aggregates the SEL list for the
> > samples:
> >
> > ;WITH Analytes AS (
> >    SELECT MAX(CAST(arsr.Rpt AS INT))   AS MOA --Rpt is a bit field,
> >    the
> > value is either 1 or 0
> >          ,arsr.Analyte
> >          ,ars.TestNum --the testnum that contains the master list of
> > requested analytes
> >          ,tars.SeqNo
> >    FROM   tmpAnalRunSeq                AS tars --staging table for
> >    data the
> > user is working on, to pare down the dataset
> >           INNER JOIN AnalRunSeq        AS ars
> >                ON  ars.TestNum = tars.TestNum
> >           INNER JOIN AnalRunSeqResult  AS arsr
> >                ON  ars.SeqNo = arsr.SeqNo
> >    WHERE  tars.SampType = 'SAMP'
> >           AND tars.WSID = @MyWSID
> >           AND tars.AnalDate > ars.AnalDate
> >           AND arsr.SEL = 1
> >           AND arsr.AnalyteType IN ('A' ,'C')
> >    GROUP BY
> >           arsr.Analyte
> >          ,ars.TestNum
> >          ,tars.SeqNo
> > )
> >
> > UPDATE arsr
> > SET    arsr.Rpt = 'False'
> > FROM   AnalRunSeqResult AS arsr
> >       INNER JOIN tmpAnalRunSeq AS tars
> >            ON  tars.SeqNo = arsr.SeqNo
> >       INNER JOIN Analytes
> >            ON  Analytes.Analyte = arsr.Analyte
> >            AND Analytes.SeqNo = arsr.SeqNo
> > WHERE  arsr.AnalyteType IN ('A' ,'C')
> >       AND arsr.SEL = 'True'
> >       AND arsr.Rpt = 'True'
> >       AND tars.Validated = 'False'
> >       AND tars.WSID = @MyWSID
> >       AND Analytes.MOA = 'True'
> >
> >
> >
> > The way this query is written is it's turning OFF what's already ON
> > (previously) since everything is ON as a default.
> >
> > I was using a correlated subquery in the update statement to find
> > arsr.Analyte IN (SELECT DISTINCT Analyte FROM AnalRunSeqResult where
> > TestNum = tars.TestNum and tars.WSID = @MyWSID and arsr.Rpt = 'True')
> > but I re-wrote it to use a CTE because it felt a little easier to read
> > (to my eyes)
> >
> >
> >
> > On Wed, Feb 22, 2023 at 8:50 AM James Button via AccessD <
> > accessd at databaseadvisors.com> wrote:
> >
> > > 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
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

More information about the AccessD mailing list