how is "FIRST" supposed to work

[Jesse]

I am trying to figure out the function of "FIRST" in a totals query -
with little luck so far. I think that my intuition may be getting in
the way.

Using Access 2000. I have an orders detail table (tbl_orders_d) with
the usual stuff -- trans_num, product_name, quantity, price, and a
unique auto_num field. I want to do a query that yields just 1 row
for each order (whether there is only 1 record or 5) -- so, I created
a totals query (qry_1) for tbl_orders_d. I dragged each column to the
query grid and on the Total line in the grid cell for trans_num I
applied "FIRST". (First or Last, for this problem, it doesn't
matter.) I figured - incorrectly - that this would leave me with only
the first row showing for each order_num. Instead, every row was
displayed for each order_num.

By trial and error ... The only approach that has worked for me in
qry_1 is to select ONLY the trans_num and auto_num fields from
tbl_order_d and apply FIRST to auto_num. (Since each auto_num is
unique, I have no idea why this works. Kinda like 1000 monkeys and 1
somehow types something meaningful.) Also, I have to NOT select any
other columns - otherwise, every row for every order is displayed.
Instead (since I don't know how to do sub-queries, which is fine for
now) I then created qry_2 that combines qry_1 with tbl_orders to add
back (so to speak) the product_name, quantity, and price columns --
but still only showing the first row for each order. So, success of
sorts.

And yet, my questions are:

1. What is FIRST supposed to do -- and why is it working when applied
to the unique auto-num field?

2. Is there a way to include the product_name, quantity, and price
fields in qry_1 and still have that query work - that is, so that only
1 row/order is displayed? In other words, I would like to have these 3
columns display in the results but they should not affect what rows
are displayed.

Thanks. -- Jesse

 

[Guest]

FIRST and LAST only work when the field in question is sorted. The return the
FIRST or LAST data for that field. You can get some strange results if
sorting on a text field that has leading numerical characters such as street
addresses as "2 Main St." will show up after "1111 Main St." if that field is
sorted.

If your query includes the primary key field, it will return every record as
the PK is unique. Try running the query without the PK field.

If you need the PK field, you'll probably have to do something complicated
such as a TOP statement in a subquery. That would look something like:

SELECT T1.Field1, T1.Field2
FROM YourTable AS T1
WHERE T1.Field2 In
(SELECT TOP 1 T2.Field2
FROM YourTable AS T2
WHERE T2.Field1=T1.Field1
ORDER BY T2.Field2 DESC);

 

[Michel Walsh]

First and Last should NOT be understood as earliest and latest, but as "from
the first record seen" (for the group) or for the last record seen, by the
database engine, given the execution plan the database took to get the
solution. Basically, they are a little bit 'random', as records are seen in
a deterministic way (since you cannot impose the execution plan), BUT you
can be sure that all fields aggregated by FIRST will all come from the same
record. All fields aggregated by LAST will also all come from the same
record. If you use FIRST, or LAST, only once, you can probably replace them
with MIN and MAX, but if you use it more than once, there is no real
effective counter part to these.


a b c ' fields
John 10 20
John 15 12 ' data


Then,

SELECT a, MIN(b), MIN(c) FROM ... GROUP BY a
returns
John 10 12

but note that this is NOT a record, in the original data
while

SELECT a, LAST(b), LAST(c) FROM ... GROUP BY a
would return either the first row, either the second row (dependant of the
execution plan and of the physical disposition of the data, on the file, at
the moment).



Hoping it may help,
Vanderghast, Access MVP