If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Aggregate Functions "First" and "Last"
I can't seem to find any information pertaining to the aggregate Totals
functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay |
#2
|
|||
|
|||
Aggregate Functions "First" and "Last"
On Sat, 11 Mar 2006 14:26:30 -0800, jayceejay
wrote: I can't seem to find any information pertaining to the aggregate Totals functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay First isn't much use, and Last is of no use at all that I've been able to ascertain. They return the first and last records *IN DISK STORAGE ORDER* - an order which you cannot easily control or change. I'll use First() in a totals query where I know there will just be one record, or where it doesn't matter which record is to be shown. I've never found any circumstance where Last() is useful. John W. Vinson[MVP] |
#3
|
|||
|
|||
Aggregate Functions "First" and "Last"
John, I truly appreciate your candor! I await any other responses from other
users, but if nothing else, John, you have restored my sense of sanity. "John Vinson" wrote: On Sat, 11 Mar 2006 14:26:30 -0800, jayceejay wrote: I can't seem to find any information pertaining to the aggregate Totals functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay First isn't much use, and Last is of no use at all that I've been able to ascertain. They return the first and last records *IN DISK STORAGE ORDER* - an order which you cannot easily control or change. I'll use First() in a totals query where I know there will just be one record, or where it doesn't matter which record is to be shown. I've never found any circumstance where Last() is useful. John W. Vinson[MVP] |
#4
|
|||
|
|||
Aggregate Functions "First" and "Last"
Something just ocurred to me: Would i bepossible to use "First" on the
criteria line of a query? Find all orders WHERE Item # Field First = Item #77563? "John Vinson" wrote: On Sat, 11 Mar 2006 14:26:30 -0800, jayceejay wrote: I can't seem to find any information pertaining to the aggregate Totals functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay First isn't much use, and Last is of no use at all that I've been able to ascertain. They return the first and last records *IN DISK STORAGE ORDER* - an order which you cannot easily control or change. I'll use First() in a totals query where I know there will just be one record, or where it doesn't matter which record is to be shown. I've never found any circumstance where Last() is useful. John W. Vinson[MVP] |
#5
|
|||
|
|||
Aggregate Functions "First" and "Last"
"John Vinson" wrote in message
... On Sat, 11 Mar 2006 14:26:30 -0800, jayceejay wrote: I can't seem to find any information pertaining to the aggregate Totals functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay First isn't much use, and Last is of no use at all that I've been able to ascertain. They return the first and last records *IN DISK STORAGE ORDER* - an order which you cannot easily control or change. I'll use First() in a totals query where I know there will just be one record, or where it doesn't matter which record is to be shown. I've never found any circumstance where Last() is useful. As I read the Help, John, it will return the First or Last in the sorted order when used in conjunction with an ORDER BY clause. However, Min or Max is just as good. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) |
#6
|
|||
|
|||
Aggregate Functions "First" and "Last"
No. You could have a subquery that used First (or Last) to return a value,
and then use that value in your Where clause, but you cannot use First the way that you've shown. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "jayceejay" wrote in message ... Something just ocurred to me: Would i bepossible to use "First" on the criteria line of a query? Find all orders WHERE Item # Field First = Item #77563? "John Vinson" wrote: On Sat, 11 Mar 2006 14:26:30 -0800, jayceejay wrote: I can't seem to find any information pertaining to the aggregate Totals functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay First isn't much use, and Last is of no use at all that I've been able to ascertain. They return the first and last records *IN DISK STORAGE ORDER* - an order which you cannot easily control or change. I'll use First() in a totals query where I know there will just be one record, or where it doesn't matter which record is to be shown. I've never found any circumstance where Last() is useful. John W. Vinson[MVP] |
#7
|
|||
|
|||
Aggregate Functions "First" and "Last"
Thanks a million for your help, gentlemen.
Jay "Douglas J. Steele" wrote: No. You could have a subquery that used First (or Last) to return a value, and then use that value in your Where clause, but you cannot use First the way that you've shown. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "jayceejay" wrote in message ... Something just ocurred to me: Would i bepossible to use "First" on the criteria line of a query? Find all orders WHERE Item # Field First = Item #77563? "John Vinson" wrote: On Sat, 11 Mar 2006 14:26:30 -0800, jayceejay wrote: I can't seem to find any information pertaining to the aggregate Totals functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay First isn't much use, and Last is of no use at all that I've been able to ascertain. They return the first and last records *IN DISK STORAGE ORDER* - an order which you cannot easily control or change. I'll use First() in a totals query where I know there will just be one record, or where it doesn't matter which record is to be shown. I've never found any circumstance where Last() is useful. John W. Vinson[MVP] |
#8
|
|||
|
|||
Aggregate Functions "First" and "Last"
Last returns the field value from 'Last' record in a recordset formed
from an aggregate query: We used to use it for returning the last of a historical series up to a given date: select Last(idxAudit) as idx, idxRecord from tbl where dt #1/1/2000# group by idxRecord However, it was unreliable, and in recognition of this, for years MS has only said 'returns a random value' A Jet table with an ascending autonumber primary key will always be sorted in primary key order, and so should have a first and a last. A query with a specified sort order should also have a first and a last (for the whole recordset, and for any defined subset). Unfortunately, in evaluating complex queries, First and Last did not always applied to the correctly sorted set. Sometimes, First or Last really did return a random value. Even though you could see that the recordset was correctly sorted when Selected, when you applied the grouping, First and Last returned the wrong values. We became aware of this when we started using A97, and the help files(not worthless then as it is now) warned us off, but in fact, once we started looking, we saw the problem in A2 (Jet 2.5). So in fact I don't remember if we ever actually saw the problem with Jet 3.5, and I know we never saw the problem with Jet 4.0, because we never use First and Last that way anymore. So AFAIK, Jet 4.0 is the same as Jet 2.5: First and Last appear to select values from the First and Last records, using the sort order you have specified or the 'default sort order' of the table, but rarely the query plan is out of order, and you get a random record instead. We still use First where we know all the values in a group are identical, or irrelevant, on the basis that it must be faster and easier than using MAX. (david) "jayceejay" wrote in message ... I can't seem to find any information pertaining to the aggregate Totals functions "First" or "Last". All I can find is, "Displays the first/last record in a list. Could someone tell me why I might need such information, or how, in general, either of these two functions could benefit me? I would be most grateful. Jay |
Thread Tools | |
Display Modes | |
|
|