Finding the fatest performer in a race using 2 tables

[wcrimi]

I am a complete beginner. So please be patient.

I have one table that contains all the runners that are entered to run in one of several races on a given day. In another table I have their names and a rating of all their performances to date.

Table 1

Name - Race

John - 1
Joe - 1
Steve 1
Mike - 1
Cindy - 2
Jan - 2
Patty - 2
Pam - 2
Donna - 2


Table 2

Date - Name - Rating

Date - John - 88
Date - John - 91
Date - John - 94
Date - John - 92
Date - Joe - 76
Date - Joe - 79
Date - Joe - 78
Date - Joe - 75
Date - Joe - 80
Date - Joe - 79
Date - Steve - 95
Date - Steve - 94
Date - Mike - 91
Date- Mike - 90
Date - Mike - 91
Date - Mike - 89

I want the query to be able to look up each runner in Race 1 (using table 1), find their most recent race in table 2 (the first one in the list under that name), compare the ratings of all the runners in that race, and then tell me who ran fastest last time out.

In this case race #1 should return John 88, Joe 76, Steve 95, and Mike 91 and determine that Steve 95 is the best in race #1.

Then it should do the same thing for Race #2, race #3, race #4 etc.... and give me all the top rated runners for the day.

I might want to do a similar thing where I don't just look at the last race. I may want to average the last two, take the best of the last 2, do a weighted average of the last 3 etc..

Thanks

[June7]

There is no race identifier showing table 2.

To pull the best score record for each date, try TOP N parameter: http://allenbrowne.com/subquery-01.html#TopN

[wcrimi]

Thanks. Let me look into that.

Just a note, where I am saying "date", there are a lot of different dates. These guys may not have run against each other in the past. So I just need the latest race for each runner in each race today regardless of what the date was.


There is actually a lot more data in each table, including the race # in table #2, but that information is irrelevant for this query because they are past races. John may have run in race #1, #2,... #7, #8 etc... in the past, but he is running in race #1 today. I just want to compare him with other runners in race #1 today. I don't care which race they were in the past, just what the rating was.

[wcrimi]

Here's another issue I am having.

I have a table that looks like this

Date Race# Name Finish Consistency Purse

2014/11/1 1 A 2 3 0
2014/11/1 1 B 6 2.5 0
2014/11/1 1 C 3 3 0
2014/11/1 1 D 1 4 100
2014/11/1 1 E 5 1.5 0
2014/11/1 1 F 4 3 0

2014/11/1 2 A 1 3 75
2014/11/1 2 B 2 4.5 0
2014/11/1 2 C 4 3 0
2014/11/1 2 D 5 4 0
2014/11/1 2 E 3 2 0

2014/11/1 3 A 7 3 0
2014/11/1 3 B 6 3.5 0
2014/11/1 3 C 4 3 0
2014/11/1 3 D 5 4 0
2014/11/1 3 E 2 5 0
2014/11/1 3 F 3 2 0
2014/11/1 3 G 1 2.5 125

I want to know when the runner with the highest Consistency rating in that specific race was also the winner of the race (finish =1), total amount won when that occurred, and the total number of races there were (there are multiple races on multiple days).

In my example, the highest rated runner won in Race #1 but not race #2 or race #3. He earned $100. There a total of 3 races.

[June7]

Did you resolve the first question?

Now you need the record with the maximum Consistency but only if that is the first place record. Try the same query technique.
SELECT * FROM tablename WHERE ID IN (SELECT TOP 1 ID FROM tablename AS Dupe WHERE Dupe.[Race#] = tablename.[Race#] ORDER BY Dupe.[Race#], Dupe.Consistency DESC) AND Finish = 1;

Really should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Also, avoid reserved words as names.

[wcrimi]

Did you resolve the first question?

Now you need the record with the maximum Consistency but only if that is the first place record. Try the same query technique.
SELECT * FROM tablename WHERE ID IN (SELECT TOP 1 ID FROM tablename AS Dupe WHERE Dupe.[Race#] = tablename.[Race#] ORDER BY Dupe.[Race#], Dupe.Consistency DESC) AND Finish = 1;

Really should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Also, avoid reserved words as names.

I gave up on the first one for now.

I'm still at the stage where I am building basic queries from my database. So far so good on that. But taking it to the next stage is problematical because I don't know the commands, syntax, or my way around the product yet. I just know database concepts from work I did many years ago using other software. Hopefully I can make some progress with this one. Thank you for your help.

[wcrimi]

Let's try this one.

This is going to be a multi part question, but let's get past the first question first.

1. I have a table with Today Track, Today Date, Today Race Number, Runner Name, Previous Track, Previous Date, Previous Race Number.......other miscellaneous fields.
2. There are multiple days, multiple races each day, multiple runners in each race, and each runners has multiple previous races.

3. I am specifically interested Previous Track in each runner’s most recent race.

4. I only want to select a record if the Previous Track is not equal to the Today Track in the runner’s most recent race (it will be the first one listed under his name).
5. I want to continue doing that for the entire table of all the Today Track, Today Date, Today Race, Runner Name


I tried doing this using Group and First, but it wasn’t doing it for just a runner's most recent race. It was returning the first one in the group it found that met that criteria (which could be his 2nd, 3rd, 4th race back)
I hope his makes sense.

[June7]

This is basically the same situation. Still looking for the TOP 1 record for each runner then filter criteria of: [Previous Track] <> [Today Track]

Build query to return the top (most recent) record for each runner. Then use that query in another query to apply the filter criteria.

[wcrimi]

This is basically the same situation. Still looking for the TOP 1 record for each runner then filter criteria of: [Previous Track] <> [Today Track]

Build query to return the top (most recent) record for each runner. Then use that query in another query to apply the filter criteria.

Thanks. That worked.

I think it's the query within a query thing that keeps throwing me off because I keep trying to do multiple things within 1 query.