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

Minimum

P: n/a
I've build a small database for my swimmingclub. It consists of 4
tables:
participants (e.g. participant 1, 2, 3 etc.)
competitions (e.g. swimming contest 1, 2, 3 etc.)
programs (e.g. 100m breaststroke, 200m butterfly)
times (participant 1 - swimming contest 1 - 100m breaststroke - 1.23,2
participant 2 - swimming contest 1 - 50m butterfly 0.30,3 )

The times table has one-many relations with the participants,
competitions, and programs table. (1 swimmer can do many programs, a
program can be swum by many many participants)

-> I a want to make a query in which the fastest time is selected for
every program (e.g. the club records).

* When I use the min-function in the times column, i get the fastest
time regardless of the program (useless)
* I can use the min-function in the times column, with the selection
criterium equal to one of the programs (50m breast, 100m butterfly).
But then I have to repeat this query for every program. Can this be
simplified?
* When I sort the times by program, and then use the minum on the
times column, i get the following error: You're trying to run a query,
in which the expression programname is not part of a statistical
function.
* I could sort by program, then by time, and take the top-values. But
if i use the top-values function I get only the fastest time.

-> Is there a function, which take the minimum given some criterium
(e.g for every program that exists)?

* When I make a cross-query, I can put the programs as a row, and some
artificial function which selects all records as a column (or vice
versa), and using the min-function for the (time)values, i get the
fastest time for every program, but not the name (participant) who set
that time.

* I can make a cross-query with the participants as a row, and the
programs as a column, and the min function over the time values to get
the personal records.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
You should group the records by the program.

SELECT Max(Time) FROM Times GROUP BY Program

should do the trick.
Cheers,
Pavel

auke wrote:

I've build a small database for my swimmingclub. It consists of 4
tables:
participants (e.g. participant 1, 2, 3 etc.)
competitions (e.g. swimming contest 1, 2, 3 etc.)
programs (e.g. 100m breaststroke, 200m butterfly)
times (participant 1 - swimming contest 1 - 100m breaststroke - 1.23,2
participant 2 - swimming contest 1 - 50m butterfly 0.30,3 )

The times table has one-many relations with the participants,
competitions, and programs table. (1 swimmer can do many programs, a
program can be swum by many many participants)

-> I a want to make a query in which the fastest time is selected for
every program (e.g. the club records).

* When I use the min-function in the times column, i get the fastest
time regardless of the program (useless)
* I can use the min-function in the times column, with the selection
criterium equal to one of the programs (50m breast, 100m butterfly).
But then I have to repeat this query for every program. Can this be
simplified?
* When I sort the times by program, and then use the minum on the
times column, i get the following error: You're trying to run a query,
in which the expression programname is not part of a statistical
function.
* I could sort by program, then by time, and take the top-values. But
if i use the top-values function I get only the fastest time.

-> Is there a function, which take the minimum given some criterium
(e.g for every program that exists)?

* When I make a cross-query, I can put the programs as a row, and some
artificial function which selects all records as a column (or vice
versa), and using the min-function for the (time)values, i get the
fastest time for every program, but not the name (participant) who set
that time.

* I can make a cross-query with the participants as a row, and the
programs as a column, and the min function over the time values to get
the personal records.

Nov 12 '05 #2

P: n/a
Thanks,

In designview the query looks like this:

1 2
Field Programname Time
Table Program Times
Total Group by Min
Sort by
Visible x x
Criteria
Or

And it works. But when I want to view the swimmersname too, e.g.

1 2 3
Field Programname Time Name
Table Program Times Names
Total Group by Min Expression
Sort by
Visible x x x
Criteria
Or

I get the error "Your're trying to run a query in which the expression
Name is not part of a statistical function". What can I do?
Nov 12 '05 #3

P: n/a
I would save the GroupBy query without the swimmers name and inner join
it with the same table on Programname and Time to get Swimmersname.

Pavel

auke wrote:

Thanks,

In designview the query looks like this:

1 2
Field Programname Time
Table Program Times
Total Group by Min
Sort by
Visible x x
Criteria
Or

And it works. But when I want to view the swimmersname too, e.g.

1 2 3
Field Programname Time Name
Table Program Times Names
Total Group by Min Expression
Sort by
Visible x x x
Criteria
Or

I get the error "Your're trying to run a query in which the expression
Name is not part of a statistical function". What can I do?

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.