Solved Complex Query? (1 Viewer)

[Tieval]

I have a complex question and am hoping that somebody stuck at home will be able to help.

I have a process where parts may be tested and fail, a repair attempted and the part retested, possibly on more than one occasion until such time as the part has satisfactorily passed final inspection.

Although there is a lot of other information, the table contains the following basics which cannot be changed.

Date Serial No Result
21/03/2018 ABC10001 FAIL
21/03/2018 ABC10002 FAIL
21/03/2018 ABC10002 PASS
22/03/2018 ABC10001 PASS
23/03/2018 ABC10002 FAIL

The list has two serial numbers (there would be thousands) inspected on multiple dates sometimes passing and sometimes failing. I am trying to count and list all individual serial numbers that have failed the last test carried out on them.

In the example ABC10001 failed and then passed so is OK, I want to identify ABC10002 which failed, then passed but was then tested again and failed so effectively I want to list unique serial numbers where the most recent inspection record for that unique serial number was a failure and count how many instance there are.

Any ideas would be greatly appreciated.

 

[jdraw]

"I have a process where parts may be tested and fail, a repair attempted and the part retested, possibly on more than one occasion until such time as the part has satisfactorily passed final inspection. "

Are you saying that ALL parts will Pass at some point --there are no Failures that never, ever go to trash or scrap?

"the table contains the following basics which cannot be changed. "
Every table should have a primary key. What uniquely identifies your test records?

 

[Tieval]

Hi JDraw,

No, some parts do just fail but then they would still be unique serial numbers where the last inspection of them failed. Effectively this will list all the serial numbers that should have been sent to scrap.

The table does have a primary key which is an auto-number showing the order in which records were entered, the data comes from various test centres so this may not be in date order.

 

[jdraw]

OK. So can you give us a few sample records that have the appropriate autonumber and Date fields.
Also, how exactly would we identify the latest record for a specific serial number? Just trying to understand your meaning of "unique serial numbers where the last inspection of them failed. "
I asked because of this record
32/03/2018 ABC10002 FAIL

 

[Tieval]

Please find attached details, the latest or last record should be the most recent by date.

Unfortunately I now realise that modern records have full date and time in this field but older ones assume the time as 00:00 so on those I would have to use a combination of date and then highest primary key number.

and that was a typo, it should have been 23/03/2018

 

[CJ_London]

on those I would have to use a combination of date and then highest primary key number.

I would be inclined to modify your older data to include a time element based on the ID, then you only need a simple query. Something like this

UPDATE Scans SET Scanned = [scanned]+[id]/100000
WHERE Scanned=DateValue([Scanned])

the 100000 needs to be bigger that your maximum ID to ensure you always generate a value less than 1

only thing to watch out for is if you have two records, same serial number, same scanned date but one with a time element and one without

so you can run this query to see if this is a problem

SELECT Scans.ID, Scans.Scanned, Scans.SerialNo, Scans_1.ID, Scans_1.Scanned, Scans_1.SerialNo
FROM Scans INNER JOIN Scans AS Scans_1 ON Scans.SerialNo = Scans_1.SerialNo
WHERE (((Scans.Scanned)=DateValue([Scans].[Scanned])) AND ((Scans.ID)<>[Scans_1].[id]) AND ((DateValue([scans_1].[Scanned]))=[scans].[scanned]));

if it is, probably easier to change manually than to write a query

finally, to get your result

SELECT Count(Scans.ID) AS CountOfID
FROM Scans INNER JOIN (SELECT Scans.SerialNo, Max(Scans.Scanned) AS MaxOfScanned
FROM Scans
GROUP BY Scans.SerialNo) AS LastScan ON (Scans.Scanned = LastScan.MaxOfScanned) AND (Scans.SerialNo = LastScan.SerialNo)
WHERE (((Scans.Result)="fail"))

 

[The_Doc_Man]

If the testing is done only once per day, it is immaterial whether time is included. You might get what you want with something like this.

SELECT SerialNo, Scanned, Result 
FROM Scans 
WHERE Result = 'FAIL' AND Scanned = DMax( "Scanned", "Scans", "SerialNo=" & SerialNo ) ;"

There could be many other ways to do this.

 

[Tieval]

Many thanks CJ, will take a good look.

 

[CJ_London]

@Doc - not sure I agree - if you had a fail, followed by a pass on the same day (i.e. with a later ID) your query would return the fail

All depends on whether the order of the ID can be relied on as being a time order - and as we always say, we should not give the ID any meaning.

 

[Tieval]

I suspect I am going to run into a real issue here. On the old data I may have two records from the same day from different pieces of equipment.

Record Date Serial No Result System
00001 21/01/19 ABC10003 Pass 1
00002 21/01/19 ABC10003 Fail 1
00003 22/01/19 ABC10003 Pass 1
00004 22/01/19 ABC10003 Fail 2

Record 00004 could be earlier than record 00003 (information not available) so really I need to get the result or an inconclusive result.

 

[CJ_London]

that is what my first query was about. You suggested it could be based on the ID i.e. the order (in principle) in which the records were created reflects the order in which the tests were undertaken. But if you can't rely on that principle, better to ignore or at least put a caveat on the results.

 

[jdraw]

Agree with the caveat, and would emphasize Don't promise perfection - especially if you can not determine the "last" record with certainty. Even if you concoct a scheme that is "pretty good" to assign an order to records, it won't be exact. But depending on the purpose of the exercise, it may give "the patron" enough information.
I would suggest you don't do too much physical coding until you get a requirement that you can meet with the data quality being provided to you.

 

[The_Doc_Man]

Yep, just now noted that the first example DID had a case of two tests, same day. What I said wouldn't work. Shot from the hip, I guess.

The fly in the ointment here is the statement "Record 00004 could be earlier than record 00003" which, if true, really makes this one hard to crack with any reliability.

 

[CJ_London]

are there other fields that can help to determine order? I see you have hours and cycles columns for example?

 

[Tieval]

Unfortunately no, although I am happy to report a failure to report but cannot work out how to do it. Effectively I could list ones that fail their last inspection and ones where the last inspection is indeterminable.

Effectively:
ABC00001 Pass
ABC00002 Fail
ABC00003 Unknown

This is for an exercise rather than a fixed product and I am trying to save the time of looking at the history of each individual serial number.

 

[jdraw]

Tieval,
You know your situation and requirement better than any reader. If you can't describe or determine the order of tests with any certainty, then I don't see how responses could anything more than "guesses".

 

[Tieval]

You are absolutely right and I now know the answer, I cannot reliably determine the facts. I am stuck with the data as some of it is twenty years old, modern data does have a correct date and time and I am wondering now if there is a method to run this type of query and in instances where the last record cannot be found record these as such.

 

[CJ_London]

I am wondering now if there is a method to run this type of query and in instances where the last record cannot be found record these as such.

not sure what this means - a last record will always be found unless there are no records at all for a particular serial number

 

[Tieval]

Using your earlier example, and I know this is a little crazy, the DMax would not work and presumably fall over. My wild idea was to list the unique serial numbers where the DMax didn't work.

 

[CJ_London]

suggest you try the last query I provided in post#6 but add a criteria to only include records from when you started to use the time part

SELECT Count(Scans.ID) AS CountOfID
FROM Scans INNER JOIN (SELECT Scans.SerialNo, Max(Scans.Scanned) AS MaxOfScanned
FROM Scans
GROUP BY Scans.SerialNo) AS LastScan ON (Scans.Scanned = LastScan.MaxOfScanned) AND (Scans.SerialNo = LastScan.SerialNo)
WHERE Scans.Result="fail" AND Scanned>#01/01/2019#