Results 1 to 12 of 12
  1. #1
    Join Date
    May 2020
    Posts
    7

    Question Query design: sum of values from surrounding xy coordinates

    Hi, total noob here. I've been doing everything in Excel up until now, but I'm currently working with a database that's too big for that. I'm trying to do the following thing:

    I'm working with three columns. Each row has a value x, y and n. I want to make a fourth column, that shows a sum of the n values from rows with the following properties:
    (x-1,y-1),(x,y-1),(x+1,y-1),(x-1,y),(x+1,y),(x-1,y+1),(x,y+1),(x+1,y+1)



    Basically, I want to model a square grid where every tile holds a value n, and for each tile I want to calculate the sum of n of the 8 tiles surrounding it. How do I go about doing that?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am not quite grasping this. Are you literally adding and subtracting 1? Or does this represent something else? Perhaps you could provide mockup in Excel of what you are trying to achieve.

    Wouldn't be surprised if this requires VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sounds a bit like the Minesweeper game to me...
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    I imagine the way to do this would be to use DLookup()

    for each record in the table, you would need to DLookup() each of the eight values separately, so that will require eight columns each looking up the value of a tile in a specific relative location (x-1,y+1).

    then you will need a ninth column to add those values together.

    good luck with your project,


    Cottonshirt

  5. #5
    Join Date
    May 2020
    Posts
    7

    Post

    I made an excel mockup, and went on to trying DLookup but couldn't get it to work, so I made a web of queries that achieved the same result. It may work in this instance, but the problem is... I want to use it to analyse a 2001x4301 grid, which makes for 8.6 million rows. Needless to say, a lookup takes forever. I considered just letting it run, but realized a single lookup could take up to 7.396e+13 checks. Thing is, the values I'm looking for aren't randomly distributed, but adjecent on a 2d grid. I want to look up the two rows directly above and below (x-1,y) and (x+1,y), along with two clusters of 3 rows with the previous and next y value (x-1,y-1), (x,y-1), (x+1,y-1) and (x-1,y+1), (x,y+1), (x+1,y+1).

    If r is the row number I want to lookup data for, I already know which rows said data is at: r-1, r+1, r+2000, r+2001, r+2002, r-2000, r-2001 and r-2002. Is there a way to lookup data from specific rows based on their relative distance?

    Edit: I just realised the method I described would produce wrong results for tiles at the edge of the grid. With the method I used in my Access file, those edge tiles also weren't calculated, but excluded. Only in Excel could I get their proper value, thanks to XLOOKUP's if-not_found argument that I set to 0.
    Attached Files Attached Files

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    What about something like this to get the sum of an individual tile?

    Code:
    PARAMETERS x_param Long, y_param Long; 
    
    
    SELECT (SELECT Sum(some_table.n) AS expr01 
            FROM   some_table 
            WHERE  ( ( ( some_table.x ) Between [x_param] - 1 And [x_param] + 1 ) 
                     AND ( ( some_table.y ) Between [y_param] - 1 And [y_param] + 1 
                         ) )) 
                  - n AS n_sum 
    FROM   some_table 
    WHERE  x = x_param 
           and y = y_param;

    Or something like this to sum all the tiles
    Code:
    SELECT (SELECT Sum(sq.n) AS n_sum        FROM   some_table AS sq
            WHERE  ( ( ( sq.x ) Between q.x - 1 And q.x + 1 )
                     AND ( ( sq.y ) Between q.y - 1 And q.y + 1 ) )) - q.n AS n_sum
    FROM   some_table AS q

  7. #7
    Join Date
    May 2020
    Posts
    7
    Thanks, but could you explain? Sq.n and q.n show up as parameters. What do they represent? Where and how do I point at the column containing the numbers that should be summed up? Should I replace anything besides some_table?

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    This is just example sql code. What I've done there is it's a query and a subquery. The subquery sums the [n] of all 9 records, or tiles, between y-1 and y+1 and between x-1 and x+1, then the main query backs out the [n] for the record in question, or the center tile. Yes, you will need to replace all the field names and the table name with however you've named them in your db. [q] and [sq] are just aliases I used for convenience, you don't have to change these or you can make them something more descriptive and less confusing to you for the future.

    If you can post your database or at least a screenshot of your table design I can rewrite the sql for you. Are you wanting to just get the sum of one tile at a time or are you looking to sum the whole table at once? The OP suggests to me that you wanted to save this calculation in the table, it's generally frowned upon to save calculations. Can you elaborate on what your project is?

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I just saw the example database you posted. This appears to work:

    Code:
    SELECT (SELECT Sum(sq.Food) AS SumOfFood
    FROM Sheet1 AS sq
    WHERE sq.X Between q.x-1 and q.x+1
    and sq.y between q.y-1 and q.y+1
    ) - q.Food AS my_sum
    FROM Sheet1 AS q;

    Make sure your x and y fields are indexed.

  10. #10
    Join Date
    May 2020
    Posts
    7
    Thanks, I had figured out myself to replace "n" with "Food" (I initially assumed it had something to do with row number) and everything worked correctly. My next challenge is to sort from largest Food_sum to smallest. I tried Sort -> Descending in the design view menu, but due to the many rows I gave up after a few hours. Is there maybe a more efficient way to sort a large array of numbers of which most are repeating, with a minority of outliers? Any way to index the new values? Or should I just let it sort 8.6 million rows like that and wait?

    I've also meant to repeat this calculation to calculate the sums of a total of three "rings" around a tile. Is this the way to do it, or am I being inefficient? Does using [Ring1] and [Ring2] link values, or repeat the entire calculation?

    Code:
    SELECT
    
    
    (SELECT Sum(sq.Food) AS SumOfFood
    FROM Datafile_worldmap AS sq
    WHERE sq.x Between q.x-1 and q.x+1
    and sq.y between q.y-1 and q.y+1
    )-[q].[Food] AS Ring1,
    
    
    (SELECT Sum(sq.Food) AS SumOfFood
    FROM Datafile_worldmap AS sq
    WHERE sq.x Between q.x-2 and q.x+2
    and sq.y between q.y-2 and q.y+2
    )-[q].[Food]-[Ring1] AS Ring2,
    
    
    (SELECT Sum(sq.Food) AS SumOfFood
    FROM Datafile_worldmap AS sq
    WHERE sq.x Between q.x-3 and q.x+3
    and sq.y between q.y-3 and q.y+3
    )-[q].[Food]-[Ring1]-[Ring2] AS Ring3
    
    
    FROM Datafile_worldmap AS q;

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    The query for the other rings looks good at first glance. As for optimizing and speeding it up... that's over my head. No, you can't index a calculated value in a query, however I believe access does some optimizations by caching info after the first time you run a query. I have no idea how much that will help you. Also, it might be helpful to break out the subqueries into separate saved queries and refer to the saved queries. Do you have to run the query on the entire table, can you filter the data down and just grab what you need when you need it?

    Again, optimization is out of my wheelhouse so hopefully someone else can chime in.

  12. #12
    Join Date
    May 2020
    Posts
    7
    Somehow, your code works when I run it in a select query, but takes forever in a make table query. Is there another way to make calculated query results into a table?

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

Similar Threads

  1. Replies: 3
    Last Post: 04-18-2017, 02:28 AM
  2. Finding Coordinates
    By QuickJeff in forum Queries
    Replies: 6
    Last Post: 07-04-2016, 01:58 AM
  3. Need help with coordinates query
    By matt_fdz in forum Queries
    Replies: 4
    Last Post: 05-19-2015, 10:36 PM
  4. GPS coordinates mapping on google map
    By engr_saud1 in forum Forms
    Replies: 1
    Last Post: 05-29-2013, 05:41 AM
  5. Border surrounding entire Report
    By yes sir in forum Access
    Replies: 1
    Last Post: 11-13-2010, 01:58 PM

Tags for this Thread

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