first value of each itemid how ?

[captain_2010]

hi,

From below table want only the first value of each item id.

and how to get it, tried to use top n function in select statement
it takes only the first record.

itemid qty
1 10
1 5
1 15
2 20
2 15
4 10
4 5
4 2

How to get first record of each item only.

captain

[Salad]

ca**********@ya hoo.com wrote:

hi,

From below table want only the first value of each item id.

and how to get it, tried to use top n function in select statement
it takes only the first record.

itemid qty
1 10
1 5
1 15
2 20
2 15
4 10
4 5
4 2

How to get first record of each item only.

captain

If your ItemID records have an autonumber, I would create a query
(Query1) that selects the ItemID and Qty and sort on ItemID,
ItemIDAutoNumbe r. That way the query will sort the records in the order
they were entered. Then create a Totals query using Query1, group on
the ItemID and select First for qty.

Access does not necessarily open up a table in sequential mode. So you
may end up with a First that may not be the first. Hopefully by
ordering it first by the authonumber you would get the true First entry.

[ajamrozek]

Might try using the Max(qty).
Turn the Aggregation tool on. By default all fields selected will have
"Group By" on, change this to Max for the qty field.
SQL would look like
SELECT itemid, MAX(qty) as MaxOfqty
FROM tablename
GROUP BY itemid