Finding the first row of a linked query

[justice8965]

Hey guys, have a question. I'm using Access 97.

So I have a linked query I made in query designer. What I'm trying to do is call a VBA function I have in a module from the query designer. What this function needs to do is check if we are currently on the first row of the query (or the earliest BeginDate field in the query, either works). If we are, it does a simple calculation. If not, I need to grab a field from the row prior to the one we're on and do the calculation. For example

if firstrow
use = (firsthalf + secondhalf) * average
else
use = (firsthalf * previousaverage) + (secondhalf * average)
end if

How would I go about doing either of these things? I can't use rs.index since its a linked table, and I've tried some other things that didn't work.

 

[namliam]

Try doing a search on "Running sum" as that is what you are looking for...

Its not easy to do but managable.

 

[gemma-the-husky]

really hard to get this INTO the query

one way
- create a temporary table, and make the calculations in the table

another way
- work all this out when you use the query

the trouble arises because access does not have a previous/next row - all intrinsic database actions are performed on a set of data, irrespective of the order (and you cannot assume access will use any given order - it will do it in what it things is the most efficient way)

therefore if you need to process a dataset in a specific sequence, you realy need to do this in code, by opening a recordset, and stepping through the items in that recordset.

----------
you can't use rs.index on a linked table directly, but you can actually open the linked database directly, and then use indexed reads

alternatively, if you have the query sorted by date, then you can use the rs.movefirst, movenext etc, to step through the database, and you will get the same order.