First function

[Yoohoo]

Hi all, i am new in this forum and as i am new to access database.
i am trying to learn by reading tutorial online but some things are not clear and they dont present small sample data.

for example, i am looking at the First function in access. i saw a query online such as



select first(cid),
cname,
sum(usd),
sum(pad),
sum(cad)
from <some table>
group by cname

i read that first function return the first value of a result set. but since i am new at this i am a little confuse since the tutorial does not give sample data. can somone explain what first function really does by illustrating some sample data. for example, you can post some sample data how it looks before the query is run and after the query is run using the query above. i trying to understand how this first function works. does the group by gets apply first then the first function or vice versa.

please help me understand this. thanks in advance

[pbaldy]

This should explain how it works, and why we rarely use it:

http://support.microsoft.com/kb/208190

[pbaldy]

I should have added that the group by is considered before the first, so that would result in the first cid for each cname.

[weekend00]

please keep in mind that first() returns an "uncertain" result which means you may not get the same result by running it this time and next time.

[ajetrumpet]

please keep in mind that first() returns an "uncertain" result which means you may not get the same result by running it this time and next time.

umm....why is it uncertain?

[weekend00]

umm....why is it uncertain?

I am not sure how first() works.
I believe it returns the field of first record that Access finds, and usually is the first record in physical storage position.

for example, I create a table tblTest ( f1 text ) and type in 2 records in order:
"result1"
"result2"

when I run "select first(f1) from tblTest", it returns "result1"

if I delete the first record with text "result1", then add a new record, type in "result1" and run above mentioned query, it returns "result2".

the table have the save data, but the results are different, that's what I mean "uncertain"

[ajetrumpet]

it's always certain weekend, but you have to know the order in which the records are stored. I believe that, if a table is indexed or ordered, it affects first and last() functions.

[pbaldy]

It's quite predictable. From the link I posted in post 2:

The First(), Last(), DFirst(), and DLast() functions ignore sort orders, indexes, and primary keys. These functions return the first or last undeleted record based on the order in which the records were entered into the table, not the first or last record in a specified sort order.

Your results follow that.

[weekend00]

I see. but usually we don't know (don't care) the order of the records. therefore we can't predict what we will get.

[pbaldy]

Which is why I said

This should explain...why we rarely use it

[ajetrumpet]

come to think of it, the only time I've even seen first() used in a query is with the 'find duplicates' built in query in access.