Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make table query expression - following record (2002)

    I am running a "make table query"

    One of my columns has the following expression

    Expr1: Int(([MinOfField1]-[field1])/[spacing]) = ([MinOfField1]-[field1])/[spacing]

    No big deal, it works fine, it yields 0 or -1. I want to have another column to evaluate the current record, and compare it with the following one. How do I reference the "following record" in my expression? What would be the syntax?

    Thank you
    Guillermo

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make table query expression - following record (2002)

    Basically, you can't. This is not a spreadsheet and record order is largely irrelevant in a relational database since it changes depending on the indexes involved and what you're doing with the records. If you explain what you're trying to accomplish by referencing another record, perhaps someone can suggest an alternative.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table query expression - following record (2002)

    I have an Excel spreadsheet to load gridded data from a map (a point set), and trim down a certain number of "nodes" based on spacing in X and Y.

    My data comes from an ASCII file consisting of 3 columns : X coordinate , Y coordinate and altitude.

    But excel is limited by the amount of rows you can load, so I guessed that the same task could be done with Access.

    I linked the ASCII file to a table. I have a query to extract the max and min X and Y. I have another "make table query" with an expression that will yield true or false if X or y are multiples of a "spacing" that I supply to trim down the data. This make table query uses as input for the expression, the fields in the first query.

    In that way I can trim my data to keep only for example, a 10 * 10 X-Y spaced grid.

    But I also want to keep the node data that belongs to the end of each rib of the new grid (imagine mapping an ice cap; I want to keep the data corresponding to the end of each data line in X and Y, where the ice cap actually ends). In Excel, my data is sorted by X and Y, and I have a formula that checks the value of X (or Y) in each row, and the value of X immediately above. If it is not the same, it flags the row, sow I can eventually keep that row in my final listing, with the autofilters.

    I thought that with Access, I could query a sorted table, and have an expression that would mimic the formula I have in Excell to flag the records where my X (or Y) value changes in the sorted list.

    Excel works fine with small datasets, but ASCII with 100,000 lines (real life) are dificult to handle

    Comments, suggestions? Thank you
    Guillermo

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make table query expression - following record (2002)

    You can use a user-defined function for this, but it will be slow on a 100,000 record table. See ACC2000: Referring to a Field in the Previous Record or Next Record, in particular Method 2: Using Code.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make table query expression - following record (2002)

    Help was great!!!

    Can you tell when will the lounge search tool be reinstated? I really miss it!

    The lounge is the MVP (most valuable place) in the internet for Excel, Access, and MS applications help I know. I greatly appreciate the feedback from the lounge's MVPs. Thank you.
    Guillermo

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make table query expression - following record (2002)

    Unfortunately, we don't know when the search function will be back.

    Our webmaster/admin <!profile=Claude>Claude<!/profile> has been suffering from severe back problems for two months, which have made it impossible for him to spend much time on the Lounge. Another problem is money. You may have noticed that we now have Ads by Google at the bottom of the Lounge pages. Clicking the links will contribute to the cost of a new server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •