Easiest way to get first and last

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know that there are many ways to get the first and last records. What if I
want to get the first and last records of a two column table or recordset?
So basically, the first row, and last row, but 4 seperate records. Thanks!
 
I don't understand the 4 separate records part. There would be one record
that is the first row it would have both columns and one record for the last
row with the two columns, so the 4 records is confusing. Also, be aware that
what are returned as the first and last records will be affected by you
current sort order or current index.
 
So if I want A1 and B1 and say A100 and B100, how would I get that? I think
I can get A1 and A100, but how do I get the B1 and B100? Does this make
sense?
 
Databases are not speadsheets!!!

The concept of first and last record only applies in a sorted Query or
Recordset. There is no guarantee that a Query or Recordset will remain
in any particular order between invocations unless it is sorted, in
which case "first" and last will be defined according to that sort
order. If you are using such a Query or Recordset, sorted by the
contents of Field A (using your nomenclature) the first record will
be that with the lowest value for A and the last record will be that
with the highest value for A. The values returned for field B in those
records will be those associated with the lowest and highest values
for A in the original dataset. Similarly, but in reverse, if you sort
by the contents of field B, your first and last records will be as
determined by the contents of field B.


So if I want A1 and B1 and say A100 and B100, how would I get that? I think
I can get A1 and A100, but how do I get the B1 and B100? Does this make
sense?
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
As Peter reinterated my point regarding sort order, and that what you want to
get is a moving target, I think you are hard pressed to do this. Unless
someone more skilled in SQL than I has a cute trick, I don't believe you can
do it that way. The only way I can think of would be to use VBA to open a
recordseet sorted the way you want it, and loop through each record, evaluate
the field values based on the rules you want to impose, and return just those
records in another recordset.
 
Back
Top