4 Replies Latest reply on Jul 3, 2015 8:33 AM by AlPhuket

    Repeated Instrument Tests - need to populate a current field with field from previous test

    AlPhuket

      Title

      Repeated Instrument Tests - need to populate a current field with field from previous test

      Post

      I get several CSV files daily of test results from several thousands electronic modules.  So far my input script increments a Test Number field on each input, and each module is defined by a unique number.  I've been trying for a couple of days to figure out how to add the previous test result to the current failure list, so I can determine modules that have not been repaired since the last test.  I would also like to be able to determine how many failures from the previous test have been resolved (module number still in table, but passing tests now).  I would be most grateful for any advice that could steer me towards a (hopefully not too complicated) solution. smiley

        • 1. Re: Repeated Instrument Tests - need to populate a current field with field from previous test
          philmodjunk

          But how is your solution designed now? What needs to happen in your database in order to "add the previous test result to the current failure list"?

          • 2. Re: Repeated Instrument Tests - need to populate a current field with field from previous test
            AlPhuket

            I run a script for each test result file that adds all the new records to the existing database, so it winds up with up to 50,000 records per day in the one table.

            • 3. Re: Repeated Instrument Tests - need to populate a current field with field from previous test
              philmodjunk

              Sorry, but that really doesn't provide the information that I need. My crystal ball is currently down for a "maintenance update" wink so the only details about the design of your database that I know are those you choose to put in this forum thread.

              Is there just the one table in your database? Or are there related tables linked to the one updated by your imports? If so, what are the relationships?

              To repeat my previous question: "add the previous test result to the current failure list" means what in terms of your database? That could mean that you have to modify your data after it is imported--either in the table that's the target of your import or in a related table--or it could describe something very different.

              I have to know more detail before I can help you.

              • 4. Re: Repeated Instrument Tests - need to populate a current field with field from previous test
                AlPhuket

                Phil, There is only one table.  It has fields for unique serial numbers for the modules, date, time, and several fields with values as well as other calculated fields that I've set up to check whether the previously mentioned values are in spec or not - if not, then the calculation enters a "1" in that field. As well as having three pass/fail fields that are either a "1" or a "0", I have another calculated field that is a "1" if any of the other fields is a "1".

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                Example Test Table
                SerialDayOfYearTestNumberResistanceLeakageFail_ResistFail_LeakageFailAnyTime
                45451279178189050000900
                454515781781160051010900
                454512791782150051011200
                454515781782160051011200

                In the above simplified table the first two records were entered from the first test results, with serial 45451279 passing all tests and 45451578 failing Resistance.  When the second set of tests is added, 45451279 has now also gone bad and will be a new failure, and 45451578 is still bad, in which case I would like the report on failures in the second test to show the resistance from the first test as well as its new reading.

                In a second report, I would like to generate a list of all failures from the first test that were now showing good results and had been repaired.

                I hope I that's a better explanation ...  I was thinking this would be a situation for a self-join, but I have never used anything like that before, so was hoping for another solution unless the self-join possibility isn't as difficult as I'm thinking.  Thanks for looking at this.