468,736 Members | 2,093 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,736 developers. It's quick & easy.

first value of each itemid how ?

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
2 1543
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
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.

Similar topics

reply views Thread by Sparky Arbuckle | last post: by
1 post views Thread by Slonocode | last post: by
reply views Thread by =?ISO-8859-2?B?UmFmYbMgR2llemdhs2E=?= | last post: by
2 posts views Thread by sunilkes | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.