Results 1 to 3 of 3

Update query to copy known value based on closed matching fields in separate tables.

  1. #1
    JRodko is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    72

    Update query to copy known value based on closed matching fields in separate tables.

    Title is suppose to be closest matching fields not closed.

    Hello.

    It may be easiest to just view my attached example database. I have three tables of which two are mostly relevant. A KnownDirections table and NeededDirections.

    I need to copy the value from the Direction field in the KnownDirection table into the Direction field in the NeededDirections table, where that record is chosen based on where the Depth fields are the closest between the two tables.

    I need it in a select query so that I can do further calculations with it (I think).

    EDIT: I have mislabeled fields in my tables and can't change the database. intDistance is supposed to be intDepth so there are depth fields in each table.



    I really don't know if I need DLookup or if the query does that for me? I have tried using DMin to find the minimum difference between the depths and then using the corresponding Direction but I'm still learning SQL and don't really know where to start with the syntax. I have this working nicely in excel but access is giving me a tougher time. My excel formula uses INDEX(MATCH(MIN(ABS ... etc.

    Any help is greatly appreciated.

    Thank you.
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,687
    where the Depth fields are the closest between the two tables
    from the Direction field in the KnownDirection table into the Direction field in the NeededDirections table, where that record is chosen based on where the Depth fields are the closest between the two tables.
    But the Depth field isn't in both tables. No "depth" field in KnownDirection table?????

    I suggest you try to describe what the tables represent, and what it is you are trying to accomplish in plain, simple English with no database jargon. If you can describe it, nobody can build it.
    Good luck.

  3. #3
    JRodko is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    72
    Hello orange.

    My apologies for the confusion! I mixed up intDistance and intDepth. They are both supposed to be intDepth. Silly me.

    My best plain english explanation is that I am trying to copy a known direction (ie towards 0) to a table with an unknown direction. The unknown direction gets filled with the known direction (0) where there is a closest known value ie where the known depths (intDepth and intDistance) are most closely matched.

    It is basically an extrapolation of the direction where the depths are similar enough that the direction will likely be similar.

    An analogy: two space ships are travelling a similar (but not identical) course at the same time. The compass is broken in one of them but is functioning in the other. Both ships are recording the altitude every few minutes but not at identical intervals. One ship records the altitude every 5 seconds and one every 2 seconds. Also, every 10 seconds the space ship with a working compass records also records the compass direction at that altitude.

    You want to know the direction of the ship with the broken compass at any given altitude, so you have to infer the direction based on altitudes and the assumption that the flight path is nearly identical. Because the altitudes are recorded at different intervals and not likely to be the same in any record, you have to use the known compass direction at the nearest matching altitude. When you find the closest matching altitude between the two ships, you copy the compass direction to the ship with the broken compass (direction needed). I will try to lay this out in a basic table (forgive me if formatting is not good)

    SHIP 1______________________ SHIP 2
    Altitude = 0, direction = ? ___________Altitude = 0, direction = 180
    Altitude = 2, direction = ? ___________Altitude = 5, direction = 190
    Altitude = 10, direction = ? __________Altitude = 20, direction = 210
    Altitude = 25, direction = ?___________Altitude = 40, direction = 198
    Altitude = 45, direction = ? __________Altitude = 80, direction = 230

    So by matching the nearest altitude and taking the known direction, your values (?) would be: 180, 180, 190, 210, 198.

    In this example Ship 1 = NeededDirections, Ship2 = Known Direction. intDepth/intDistance are the altitudes and the directions speak for themselves.

    I am not looking to average the directions.. maybe at a later date... just a copy from the appropriate field.

    Thank you!

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

Similar Threads

  1. Replies: 3
    Last Post: 08-22-2018, 10:05 AM
  2. Replies: 1
    Last Post: 07-23-2014, 01:52 PM
  3. Replies: 7
    Last Post: 06-19-2014, 11:00 AM
  4. Replies: 8
    Last Post: 10-29-2013, 11:13 AM
  5. Replies: 1
    Last Post: 10-29-2012, 07:15 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
  •  
Tech Forums: Microsoft Office Forums