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. 3 3048
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.
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?
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? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Hennie de Nooijer |
last post by:
Because of an error in google or underlying site i can reply on my own
issue. Therefore i copied the former entered message in this message....
|
by: Vikrant |
last post by:
On my DB2/UDB system, some tablespace 'Minimum recovery time' is
2000-11-19 or some '1999-10-04' or different, for some tablespace
theres is no 'Minimum recovery time' , what does it tell me?
...
|
by: David Sworder |
last post by:
Hi,
I'm writing an application in which a client (C#/WinForms) and server
(C#/service) interact with one another. The client establishes a "session"
with the server but for scalability reasons...
|
by: manxie |
last post by:
Dear All Readers,
I'm supposed to create a program with a switch and using voids to execute number of codes, that includes finding sum, average, maximum, and minimum, please read my code:...
|
by: Ioannis Vranos |
last post by:
Is there any mentioning in the standard of the number of bits of the
various built in types, apart from char/signed char/unsigned char types?
Or only about the minimum value ranges of them?
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |