By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

first value of each itemid how ?

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ca**********@yahoo.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,
ItemIDAutoNumber. 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.
Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.