| Title: | MSACCESS |
| Moderator: | BUMP::HONER |
| Created: | Tue Dec 01 1992 |
| Last Modified: | Mon Jun 02 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 1661 |
| Total number of notes: | 6339 |
I've got a table that looks something like this:
CODE Number OtherValues
AAA 0 blah
AAA 2 foo
BBB 1 bletch
BBB 5 fluffle
CCC 0 barney
CCC 3 fred
DDD 0 Mickey
DDD 2 Minnie
EEE 5 Goofy
FFF 2 Donald
FFF 4 Huey
FFF 4 Dewy
FFF 5 Louie
What I need to do, preferrably in a Query, is to find the record for
each code with the minimum value in "Number"...
AAA 0 blah
BBB 1 bletch
CCC 0 barney
DDD 0 Mickey
EEE 5 Goofy
FFF 2 Donald
Ideas?
-Steven
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 1643.1 | SHRCTR::peterj.shr.dec.com::PJohnson | Wed Mar 19 1997 12:01 | 37 | ||
I created a tbale from your data that looks like this: Code Number OtherValues AAA 0 blah AAA 2 foo BBB 1 bletch BBB 5 fluffle CCC 0 barney CCC 3 fred DDD 0 Mickey DDD 2 Minnie EEE 5 Goofy FFF 2 Donald FFF 4 Huey FFF 4 Dewy FFF 5 Louie And created a query using the "totals" button, the SQL code for which looks like this: SELECT DISTINCTROW Table1.Code, Min(Table1.Number) AS MinOfNumber, First(Table1.OtherValues) AS FirstOfOtherValues FROM Table1 GROUP BY Table1.Code; and teh result looks like this: Code MinOfNumber FirstOfOtherValues AAA 0 blah BBB 1 bletch CCC 0 barney DDD 0 Mickey EEE 5 Goofy FFF 2 Donald Took less than a minute of clicking! Pete | |||||
| 1643.2 | VMSNET::S_VORE | Smile - Mickey's Watching! | Wed Mar 19 1997 13:06 | 4 | |
First -- thanks much. I was digging in the 'totals query' area but
somehow hadn't come across First.
| |||||