Solved Complex Query? (1 Viewer)

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
I think I have actually cracked it, many thanks for all the input, it is really useful to bounce things around.

If I make:
1. A table of all unique serial numbers
2. A table of all that failed in the last instance
3. A table of all that passed in the last instance

I will then have my list of failures and by subtracting the passes and then the fails from the complete list will also have the list of uncertainties.
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
OK, getting really confused now, the attached has a set of data on which it cannot return a max value on ABC10003 as there are two entries on the last date of which one passes and the other fails but it still lists it as a failure.
 

Attachments

  • Test.zip
    18.1 KB · Views: 74

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:26
Joined
Feb 28, 2001
Messages
27,001
it cannot return a max value on ABC10003 as there are two entries on the last date of which one passes and the other fails

This is where the problem isn't with Access. It is that you haven't told Access what to do when you have this kind of conflict. And that is a matter of checking the business model to decide what action is correct. It is a problem in need of a decision first, THEN an implementation of the response.
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
I agree entirely but do not have a solution. I could live with that if Access refused to return a last record on the Max value and had rather hoped it would instead of it cheating and taking a 'generic' last value which is what I assume it is doing.

Microsoft I now note state "You can use Min and Max to determine the smallest and largest values in a field based on the specified aggregation, or grouping. For example, you could use these functions to return the lowest and highest freight cost. If there is no aggregation specified, then the entire table is used." and assume this then defaults back to the record entry order?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:26
Joined
Feb 19, 2013
Messages
16,553
I could live with that if Access refused to return a last record on the Max value
as Doc says, you need to tell access to ignore. the reality is if there is a single record which has just a date with no time element for any serial number/date record group then all those records for that group need to be ignored.
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
and that CJ is the answer, just wish I could work out how to do it. I am happy to leave out all records that have identical dates and times (where the Max should theoretically fail to pass a value) as I am going at this from two directions.

I am trying to create a list of failures in previous years (I can accept an error factor in this as it is more trend related), I am also looking at getting exact details going forward where I have the correct date and time generated in the original data and therefore the max will work.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:26
Joined
Feb 19, 2013
Messages
16,553
to identify identical dates and times use the wizard to create a duplicate table for serial number and date. You can using a left join from your table, you can link to it on serial number and date. You want all records where the duplicate query returns null. This query gives you a working dataset you can then use as required (change names to suit)

SELECT *
FROM myTable LEFT JOIN qryDups ON myTable.serialNumber=qryDups.serialNumber AND myTable.testdate=qryDups.testdate
WHER qryDups.serialNumber is NULL
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
Sorry, I am back :unsure:

Having abandoned as suggested use of the old timeless data I now have everything working and achieve a list of genuine failures (serial numbers that fail at their last inspection.

Code:
SELECT LastScan.MaxOfScanned, LastScan.Blade, Scans.Scanned, Scans.Tank, Scans.Co, Scans.Result, Len([Scans]![Blade]) AS Expr1
FROM Scans INNER JOIN (SELECT Scans.Blade, Max(Scans.Scanned) AS MaxOfScanned FROM Scans GROUP BY Scans.Blade)  AS LastScan ON (Scans.Scanned = LastScan.MaxOfScanned) AND (Scans.Blade = LastScan.Blade)
WHERE (((LastScan.Blade) Not Like "K*") AND ((Scans.Scanned)>#2/19/2020#) AND ((Scans.Tank) Like "M2" & "*") AND ((Scans.Co) Like ([Forms]![frmMain]![cboMRO])) AND ((Scans.Result)="Fail" Or (Scans.Result)="Zone") AND ((Len([Scans]![Blade]))=8))
ORDER BY LastScan.MaxOfScanned;

I just have one more major issue, I need to check that the inspection was done on the correct machine, this means that the first instance of inspection as stated in the original table field Scans.Tank is the same as the last one when it passed.

Basically my list currently excludes a part that failed on 25/02/20 16:00 on tank m2000 but passed 25/02/20 17:00 on tank m2001.

Any clues would be greatly appreciated.
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
What I am thinking is make a second query MinofScanned that lists the serial numbers (Scans.Blade) the first time they were scanned using the date and time (Scans.Scanned) and the machine they were inspected on (Scans.Tank) and link this into my other query so that we end up with a list of parts that were inspected initially on one machine and finally passed on the same machine.
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
Beginning to lose the plot now.

If I have a table with the following:
Scanned: Date & Time
SerialNo: Text Field
Machine: Text Field

20/02/20 15:25 ABC0002 M0001
20/02/20 16:00 ABC0001 M0001
20/02/20 17:00 ABC0001 M0001
20/02/20 15:25 ABC0002 M0002
20/02/20 16:08 ABC0003 M0002
20/02/20 17:35 ABC0003 M0002

How can I pull ABC0002 from this data as it is listed under machines M0001 & M0002
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:26
Joined
Feb 19, 2013
Messages
16,553
if this is more bad data, you need to eliminate them. To do this

join the table to itself on date, time and serial number with a criteria where machine in one table does not equal machine in the other
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
No, it's not bad data, just identifying bad practice.

If a user puts a part through one test machine and it fails they can then just quickly stick it through another test machine to pass it and print off a pretty certificate saying it has passed, with borderline failures one machine might be slightly less sensitive than another. Problem is the process owner wants to identify this as bad practice and a database that amalgamates data from all machines on a monthly basis needs to identify which parts have been passed through two machines.

The official line is 'if a part fails on a machine it must not be returned to service until it has passed on the same machine'. My earlier stuff has identified all parts that should not be in service with the exception of this cross-machine testing issue which if necessary I am happy to eliminate manually if I can just get a list of serial numbers that have been on two machines (very few out of tens of thousands).
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:26
Joined
Feb 19, 2013
Messages
16,553
the query outlines will do that

SELECT T1.*
FROM myTable T1 INNER JOIN myTable T2 ON T1.date=T2.date and T1.time=T2.time and T1.serialno=T2.serialNo
WHERE T1.machine<>T2.machine

you might want to add this as well
AND T1,result='Fail' and T2.result='pass'
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
Thanks CJ, I now have both parts I need after a little jiggery-pokery, hopefully I can put this to bed now (I will avoid trying to join them together). 😁
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
OK, I now have everything working but have to be careful what data to select, my data is a huge data-set that contains both date and time (modern equipment) and only date (old equipment), I am stuck with this as it is used for many functions and editing it is not an option.

New: 17/04/20 12:45:15
Old: 17/04/20 00:00:00

What I want is to be able to query by date and just get the records that have times so want to query records between [startdate] and [enddate] and do not contain a time of 00:00:00 (the chances of a modern record having a time stamp of 00:00:00 are so minimal that I can live with it).

Any suggestions on how to do this would be greatly appreciated.
 

cheekybuddha

AWF VIP
Local time
Today, 23:26
Joined
Jul 21, 2014
Messages
2,237
Code:
-- ...
  WHERE Scanned > #2020-04-17# AND Scanned < #2020-04-18#
-- ...
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
Yes, I have that as my query is:
Code:
Between [Forms]![QMain]![qStartDate] And DateAdd("d",1,[Forms]![QMain]![qEndDate])

This covers all values on a range of days but I want to exclude any values in that range where the time is 00:00:00 (only has date content).
 

Tieval

Still Clueless
Local time
Today, 23:26
Joined
Jun 26, 2015
Messages
475
Hopefully I have sorted it:

Code:
AND Not (FormatDateTime([Scans]![ScanDate],3))='00:00:00'
 

Minty

AWF VIP
Local time
Today, 23:26
Joined
Jul 26, 2013
Messages
10,355
As an alternative
Not TimeValue([Scans]![ScanDate])='00:00:00'
 

Users who are viewing this thread

Top Bottom