Same Query returns different Results each time

[Rob]

I have a query that I have written which returns different
results each time. Although it returns the same number of
records, The value in one of the fields varies. The field
which varies contains a date, which I am sorting ascending
and using the First function to return the earliest date.
I am also setting a criteria to only return dates between
a certain date range. The query otherwise runs fine, but
only sometimes returns the results I am expecting. I'm
guessing this maybe something to do with the "First"
grouping option.
Any ideas what is causing this and how it might be
resolved?

Thanks in Advance

Rob

 

[Michel Walsh]

Hi,


First does not mean EARLIEST, it just mean "the first value seen by the
engine" and it depends on the query plan (execution plan) adopted by the
database engine... You can regard it as "pick the value from a random
record" (but all values returned, say f1 and f2 in

SELECT FIRST(f1), FIRST(f2) FROM ...

would be from the same random record).

You should probably use MIN( dateTimeField ) to get the earliest date_time
field value. To get associated values (from the same record than where than
MIN occur), you may take a look at
http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP