N
Noor
hi all,
what is the last() function do..
I cant find anything related to it in the access help
NM
what is the last() function do..
I cant find anything related to it in the access help
NM
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
GreySky
Last() returns the last record from an aggregation domain.
In other words:
Select Last(City) From tblCompanies Group By CompanyTypeID
Would yield the City field from the last record in each
group based upon CompanyTypeID.
First() returns the first record's value in the group.
Last() returns the last record's value in the group.
Min() returns the "minimum" value among the group.
Max() returns the "maximum" value among the group.
Note that FIRST(field1) and FIRST(field2) will take data
from the same row. However, Min(field1) and Min(field2)
does not necessarily return the same row's data; they take
the Min of the field throughout the entire domain.
I hope this made sense.
David Atkins, MCP
p.s. Use TOP 100 PERCENT in a subquery to force the
subquery to implement it's Order By clause. This allows
you to be able to specify which record will then be First
or Last. Either that, or use some other mechanism to
control how the Cartesian Product Table is built.
In other words:
Select Last(City) From tblCompanies Group By CompanyTypeID
Would yield the City field from the last record in each
group based upon CompanyTypeID.
First() returns the first record's value in the group.
Last() returns the last record's value in the group.
Min() returns the "minimum" value among the group.
Max() returns the "maximum" value among the group.
Note that FIRST(field1) and FIRST(field2) will take data
from the same row. However, Min(field1) and Min(field2)
does not necessarily return the same row's data; they take
the Min of the field throughout the entire domain.
I hope this made sense.
David Atkins, MCP
p.s. Use TOP 100 PERCENT in a subquery to force the
subquery to implement it's Order By clause. This allows
you to be able to specify which record will then be First
or Last. Either that, or use some other mechanism to
control how the Cartesian Product Table is built.
G
Gary Walter
Hi Noor,Noor said:what is the last() function do..
I cant find anything related to it in the access help
In addition to what David has well stated,
to find this info in Access 200x Help:
click on the Contents tab
expand "Microsoft Jet SQL Reference"
expand "Overview"
click on "SQL Aggregate Functions"
in the right-hand window
click on "First, Last Functions"
Important to note:
"Because records are usually returned in no particular order
(unless the query includes an ORDER BY clause),
the records returned by these functions will be arbitrary."
When the query is making a group,
Last([somefield]) will be the value for [somefield]
in the last valid record in that group as
it is making that group.
One place where Last comes in handy (as David states)
is where you don't care what values the query returns
from 2 or more fields in your group, but you want to make
sure those arbitrary field values came *from the same record.*
Good luck,
Gary Walter