Results 1 to 6 of 6
  1. #1
    jysharp2003 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    5

    Query to detect and return a higher value that should be in its place.


    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 it's 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.
    Attached Thumbnails Attached Thumbnails screenshot of Max logic.jpg  
    Attached Files Attached Files

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,705
    Didn't look at you db - it's too early yet (for me) because of the lack of understanding what this process and design is about. If the tables are related, then why does one seem to have a PK field (ID) and the other does not contain those PK values as a foreign key? That will make this infinitely harder. By your post, I can't even tell which table might be the parent/one side and which contains the related records/many side - or even if they're supposed to be related. Perhaps this is a one time fix?

    If 1.13.1.13 is the parent, then IT should have the unique value of (e.g.) 26 and every 1.13.1.13.x in the other table should have an fk value of 26, then you'd just find the max of the trailing number where the fk matches the pk value of 26. Without that relation, you have a difficult problem IMO.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    jysharp2003 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    5
    Thanks for the time on this. Not a one time fix but on going detection of a field that should be the higher available.
    I am still stumped on this so any other coders suggestions?

  4. #4
    Welshgasman is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    163
    Crossposted at https://www.access-programmers.co.uk.../#post-1721363 with nowhere the detail mentioned here.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,705
    any other coders suggestions?
    Yes,maybe read this if you haven't seen it before
    https://www.excelguru.ca/content.php?184
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,042
    Quote Originally Posted by jysharp2003 View Post
    .....I am still stumped on this so any other coders suggestions?
    Well, to fix one problem, field "uL4" is not needed; it shouldn't be in the either table because:
    1) if you edit either "uID" or "uname", you MUST also edit field "u L4" , otherwise the data becomes worthless - which/what is the correct data?
    2) you are storing data redundantly. (in more than one place)

    I realize that this is a customer application, but it should be addressed.

    So, one might never reference the field "uF4" - use queries and create a calculated column "uid" & " " & "uname" to get the same data (as the current "uL4").



    There is always Plan Z:
    Depending on the number of records (in uTemp?) to check, you could use brute force and use VBA to check the records......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-15-2019, 11:28 AM
  2. Query to detect the lack of a record.
    By mond007 in forum Access
    Replies: 2
    Last Post: 07-07-2016, 09:41 AM
  3. Want to add buttons to go one record higher
    By hendrikbez in forum Access
    Replies: 6
    Last Post: 12-15-2015, 12:24 PM
  4. Replies: 4
    Last Post: 09-05-2011, 11:10 PM
  5. VBA/Access form Detect return of software
    By superfury in forum Programming
    Replies: 1
    Last Post: 07-06-2011, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums