Access; list of numbers; drop lowest score

[Guest]

I would like to drop lowest score for a eucher tournament database I've
created. I would like to write an expression that looks at each one of your
scores and drops the lowest then retallies a total score. Zero may be the
lowest score.

 

[John Vinson]

I would like to drop lowest score for a eucher tournament database I've
created. I would like to write an expression that looks at each one of your
scores and drops the lowest then retallies a total score. Zero may be the
lowest score.

Use a Delete Query with a criterion on the score such as

=DMin("[Score]", "[tablename]", "<suitable criteria>")

YOu will want to put some criteria on the dmin to drop only the lowest
score for this tournament, rather than the lowest score in the entire
table. Note that if there are ties for the lowest score, this will
delete ALL of the tied records.

John W. Vinson[MVP]

 

[tina]

hi John, i was wondering if there is a way to exclude the lowest tournament
score from a query, without actually deleting the record? the only thought i
came up with was to use Min in a Totals query, and then base an Unmatched
query on the table and the Min query; seems kind of clunky though. might
there be a better way? hmm, i'm also wondering if just one of multiple
"equal and lowest" scores could be filtered out...maybe a "SELECT TOP 1"?
i'm just exercising my curiosity of course - the original post caught my eye
and got me thinking about "what if". tia, tina

I would like to drop lowest score for a eucher tournament database I've
created. I would like to write an expression that looks at each one of your
scores and drops the lowest then retallies a total score. Zero may be the
lowest score.

Use a Delete Query with a criterion on the score such as

=DMin("[Score]", "[tablename]", "<suitable criteria>")

YOu will want to put some criteria on the dmin to drop only the lowest
score for this tournament, rather than the lowest score in the entire
table. Note that if there are ties for the lowest score, this will
delete ALL of the tied records.

John W. Vinson[MVP]

 

[John Vinson]

hi John, i was wondering if there is a way to exclude the lowest tournament
score from a query, without actually deleting the record? the only thought i
came up with was to use Min in a Totals query, and then base an Unmatched
query on the table and the Min query; seems kind of clunky though. might
there be a better way? hmm, i'm also wondering if just one of multiple
"equal and lowest" scores could be filtered out...maybe a "SELECT TOP 1"?
i'm just exercising my curiosity of course - the original post caught my eye
and got me thinking about "what if". tia, tina

Hm. PRobably a very good idea!

You can't - afaik - use a Subquery to get the TOP VALUES property of a
query directly, but you could use

SELECT * FROM Scores
WHERE ScoreID NOT IN
(SELECT TOP 1 ScoreID FROM Scores AS X
ORDER BY Score DESC);

to exclude just the one (or one of the tied) lowest scores. Probably a
much better solution!

btw... since you (quite prudently) don't post your real email, and
don't have a ContactMe on your webpage, I don't know how to get in
touch offline. Drop me a line at jvinson <at> wysardofinfo <dot> com
if you are willing.

John W. Vinson[MVP]

 

[tina]

You can't - afaik - use a Subquery to get the TOP VALUES property of a

query directly, but you could use

SELECT * FROM Scores
WHERE ScoreID NOT IN
(SELECT TOP 1 ScoreID FROM Scores AS X
ORDER BY Score DESC);

John, you always come thru! i knew you'd have a better solution than my
clunky idea - thanks!
you know, i really need to learn to write queries better. do you have any
suggestions for a book that won't dump me right in the middle of "i have no
idea what they're talking about" land? i don't mind if it starts out really
simple (a review of *very* basic concepts wouldn't hurt me at all), as long
as it takes me at least part way down the "more complex" road. tia again!
tina

Drop me a line at jvinson <at> wysardofinfo <dot> com

uh oh...dad, i only tasted the beer, i swear - and i even didn't swallow!
and the accelerator stuck on the car, honest!! <bg>

 

[John Vinson]

John, you always come thru! i knew you'd have a better solution than my
clunky idea - thanks!
you know, i really need to learn to write queries better. do you have any
suggestions for a book that won't dump me right in the middle of "i have no
idea what they're talking about" land? i don't mind if it starts out really
simple (a review of *very* basic concepts wouldn't hurt me at all), as long
as it takes me at least part way down the "more complex" road. tia again!
tina

Rebecca Riordan's _Designing Relational Database Systems_:
mindbogglingly, Amazon has five copies for under $5.00, an absolute
STEAL for a book that's worth vastly more:

http://www.amazon.com/gp/product/of...f=dp_olp_2//002-1893174-5115241?condition=all

Michael Hernandez and John Viescas _SQL Queries for Mere Mortals_:

http://www.amazon.com/gp/product/0201433362/002-1893174-5115241?v=glance&n=283155&s=books&v=glance

Joe Celko's _SQL For Smarties_ (bring pith helmet, a sturdy machete,
and a copious supply of midnight oil):

http://www.amazon.com/gp/product/0123693799/002-1893174-5115241?v=glance&n=283155&s=books&v=glance

uh oh...dad, i only tasted the beer, i swear - and i even didn't swallow!
and the accelerator stuck on the car, honest!! <bg>

<bg> indeed... if you're worried about being called on the carpet,
just remember it might be a red one!

John W. Vinson[MVP]

 

[tina]

thanks for the book recommendations, John. i'm taking the credit card out of
the freezer right now!