lowest value

R

rooney

I have a database of swimmers and times by stroke and by meet. I have written a query to retrieve the times since a certain date. I want to write an equation to get the lowest time by stroke and by swimmer. How would I do that? Thanks for the help in advance.
The fields are
swimmer
meet
stroke
time
 
S

StCyrM

Good evening.

Take a peek at the Min() function in Help.

Best regards

Maurice St-Cyr
 
R

rooney

Thanks. That's what I thought it would be but I am unable to get anywhere with "Help" and my help books are at the office. I must not have the syntax correct because I get error message when I try it.
 
P

PC Datasheet

Go to your table in design view and change the name of the time field to
swimtime. "Time" is a reserved word and should not be used as a field name.

Create a query with the following fields in the order shown here:
stroke
swimmer
swimtime

Click on the Sigma button on the toolbar at the top of the screen to change the
query to a totals query.

In the swimtime field, change Group By to Min.

Run the query and you should get what you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com


rooney said:
I have a database of swimmers and times by stroke and by meet. I have written
Click to expand...
a query to retrieve the times since a certain date. I want to write an equation
to get the lowest time by stroke and by swimmer. How would I do that? Thanks
for the help in advance.
 
R

rooney

I've had to revise and am still struggling with this.

In order to eliminate 0 times, inactive athletes and to include only meets within a certain date, I have written a query. The query is based on 3 tables:

Athlete (Joined to Results by Athlete)
Results (Joined to Meet by Meet)
Meet

Table - Results
fields - Athlete
swimtime criteria >0
Stoke
Distance

Table - Athlete
fields - Lastname
Firstname
Status - criteria = active

Table - Meet
fields - Meetdate criteria between 6/14/03 and 6/3/04

I couldn't group by minimum so I tried running a query based on this query and grouping by minimum - still got all the swimtimes. I know I'm doing something wrong - any ideas?

Thanks!


Table Athelete - fields - Status criteria = active
 
B

Bonnie

Use the Dmin value in the query.

LowestStroke: DMin("[stroke]", "TableName")

-----Original Message-----
I have a database of swimmers and times by stroke and by
Click to expand...
meet. I have written a query to retrieve the times since
a certain date. I want to write an equation to get the
lowest time by stroke and by swimmer. How would I do
that? Thanks for the help in advance.
 
Top