Make table query expression – following record (2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Our webmaster/admin Claude 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.

Reply | Quote

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

Our webmaster/admin Claude 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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote