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

 

[Pat Hartman]

I'm not sure why the same data is being stored in multiple places. That is certainly contributing to your problems. The other thing is that strings separated by dots will NOT ever sort or compare "logically" UNLESS you make the stanzas fixed in length such as:
0001.0001.00001.0013.0001.0004

A better solution if the depth is fixed is to just have each stanza as a separate NUMERIC field. The best solution is probably to normalize and use a recursive table which is very difficult for most people to manage.