Detect if larger value is not in place

[jysharp2003]

Hi,
I have a customer application that needs to detect and query return records that do not have the largest value in field. Example below:
field value is 1.1.1.1 where in another table there is a value of 1.1.1.1.1 that should be in it's place.
I think that maybe a query that detects period count greater than 3 (in this example) and having the same numeric of one's (1.1.1.1) that would return in query would be our need.
Another example to solidify the need is:
field value is 1.13.1.6 where in another table there is a value of 1.13.1.6.8 that should be in its place.
Logic should be if value contains 3 periods which equates to 4 segments then the query should detect and alert on "same" numeric 4 segment values and return which is the MAX 5th (or higher).
Example in screenshot shows a 5th segment available value that should be in the U Temp record.
1.13.1.13 inside story should be 1.13.1.13.8 inside story from tv which is the MAX. I have uploaded a DB to share the sample tables.
I can't say how much I appreciate your time in getting the query right and accurate for detecting availables.

 

[bob fitz]

Cross posted: https://www.accessforums.net/showthread.php?t=81807

It has already been suggested to you that you have a problem with your table design and that until that is sorted out you will have problems.

 

[jdraw]

Also cross-posted at https://utteraccess.com/topics/2059193/posts/2762945
As others have commented --seems to be a structure/design issue underlying the request.

 

[CJ_London]

Somewhat confused by the description
this says everything but the largest

I have a customer application that needs to detect and query return records that do not have the largest value in field.

and this says the largest

Example below:
field value is 1.1.1.1 where in another table there is a value of 1.1.1.1.1 that should be in it's place.

Assuming the latter would have though using a non standard join would work - perhaps something like this to find the max in the other table.

SELECT left(A.uid, instrrev(A.uid,".")) as grouping, max(B.uid) as maxValue
From myTable A INNER JOIN myOtherTable B ON left(A.uid, instrrev(A.uid,"."))=left(B.uid, instrrev(B.uid,"."))
GROUP BY  left(A.uid, instrrev(A.uid,"."))

this can then be joined to myTable in another query to compare and see which has the highest value.

This is just a pointer for a direction you could go, I don't want to get involved with the detail