425,863 Members | 879 Online
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
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