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"?
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.
Sorry, but that really doesn't provide the information that I need. My crystal ball is currently down for a "maintenance update" 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.
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 Serial DayOfYear TestNumber Resistance Leakage Fail_Resist Fail_Leakage FailAny Time 45451279 178 1 890 5 0 0 0 0900 45451578 178 1 1600 5 1 0 1 0900 45451279 178 2 1500 5 1 0 1 1200 45451578 178 2 1600 5 1 0 1 1200
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.