Find first records

[millerlite2ky]

I'm running a query based on race results. I'm trying to find out where all drivers finished in their first race. I'm using:
Driver......Group By
Event date.....Min
Track...........Min
Fin............?

if i use min for finish, Access finds the lowest finish by the driver, and not the first finish. If I use first instead of min, it finds the first finish in which it was stored in the database (if that makes any sense).

Anyone have any ideas?

Scott

 

[Andrew Fergus]

Hi Scott,

with the current database / table setup I can't get this to work with 1 query - I think you need to do this with either 2 or 3 queries, depending on where the race "date" is stored. If the race "date" is stored in the results table then you can do it in 2 (I think this is what you had, I can't remember) but if the race "date" is held in the events table then you need to do it with 3 queries.

Assuming the race date is held in the result table (let us know if it isn't), then you can get it in 2 queries like this :

Query 1 :
Driver......Group By
Event date.....Min

Save

Query 1 gives the first race date for each driver.

Query2 assumes the race "date" is in the results table.

Query 2 :
Select query based on Query 1 and tbl_Results
force the link from Query 1 to pick up all records from Query 1 and only matching records from tbl_Results based on the date and driver
Driver (from Query 1)
Event date (from Query 1)
Final Race Position (from tbl_Results)

Save and Run.

Query 2 is the minimum data and if you want to show additional data, such as the track name, then you would need to add the necessary table and pull the details through.

BTW, if the "date" is the events table, then query 2 needs to pull through the event id from the events table and then query 3 would be based on query 2 and tbl_Results.

HTH, Andrew.

 

[millerlite2ky]

I can't seem to get my second query to work right.
My first query is as follows:

Tables:
Tracks
Results
Drivers
Years

My TrackID is joined to Results....Track
My DriverID is joined to Results.....Driver
My YearID is joined to Results.....Year
My Join Properties is set to Condition 1, to include only rows where joined fields from both tables are equal.

I'm grouping by Driver from (Table Drivers).
The MIN for EventID from (Table Results).
The MIN for Date from (Table Results).
The MIN for Year from (Table Years).

So when I run the query I have just the driver name, the event number, the date for event number, and the year for event number. This is correct because there are 2820 records and I have 2820 drivers listed in my Drivers Table.


I've tried what you said, but it keeps coming back with repeated drivers and event id's and about 100000 records. Not sure if my joins are right or what's going on.

 

[Andrew Fergus]

Hi Scott
I noticed you have included both min event_id and date - I purposely omitted the event_id to take account of events not being entered in the correct sequence. Are your events entered in sequence such that the min for the date actually comes from the race that occured on that day giving you the correct event_id? Or is there a discrepancy? Try the first query with just the driver and min date, nothing else and link that query to your second query.
Andrew.

 

[millerlite2ky]

I'm getting an error message about ambiguous outer joins or something like that.
When I try just the date join, then I get each driver and date listed, with all finish positions for that race listed, like>>

Driver 1..........10/8/2003..........40
Driver 1..........10/8/2003..........17
Driver 1..........10/8/2003..........25

etc. etc.
If i try the other way, it looks like this
Driver 1..........10/8/2003..........28
Driver 18........10/8/2003..........28
Driver 15........10/8/2003..........28

Any ideas?

 

[Andrew Fergus]

Hi Scott,

please ignore my previous posts. I have gone back and looked at the data structures from this post of yours.

I tested this using your data structure from the link above and you have to do this with 2 queries, not just one, like this :

Query number 1 :

New Query, add the results table, view totals.
Field 1 = driver, group by
Field 2 = date, min

Save

Query Number 2 :

New Query, add the results table, add query number 1, do not view totals, link query 1 to the results table on both the driver field and the date field (do not change the link types, leave them as they would normally be)
Field 1 = driver, from query number 1
Field 2 = date, from query number 1
Field 3 = finish (? <- I think this was the variable used to record the finishing position), from the results table

Save and View.

Query number 2 above gives you the bare minimum of what you need - it has the driver, the date and the result from that driver's first race.

If you need additional info that's ok - just get query number 2 working first to make sure you have the bare minimum. Once this is working, try adding the extra fields until the query breaks.

When you get the "ambiguous join" error, it usually means the links between the tables and queries in the upper half of the query design screen form a circle and Access gets confused. If this is the case undo the last change you made and see if you can introduce the additional info into either the first query (e.g. the driver name) or even a third query (to display the track name etc - this would be linked through the event "date"). An alternative way to defeat the "ambiguous link" error is to prefix the variable names in your query with their source e.g. [driver] might become [your query name].[driver] or alternatively [finish] might become [your table name].[finish].

If, when you add additional tables into the query (to get the additional info), you feel the existing links are interfering with the query, then you can delete the link(s) between the tables, just for that query (not on the relationship screen).

HTH, Andrew.

 

[millerlite2ky]

Okay, I've got it working. The problem I was having was the darn TOTALS line. After I unchecked that line, it seemed to work out well. Since my driverid in the first query was a link to the Drivers table, I showed the Drivers table in query to and linked through it. I think this was a problem also.
I'm curious about one thing you stated though. It may sound silly, but how can you "break" a query?

Thanks again for the help.

Scott

 

[Andrew Fergus]

Hi Scott

Thanks again for the help.

You're welcome and it's my pleasure.

The problem I was having was the darn TOTALS line.

So often it is the simple things that let you down.

It may sound silly, but how can you "break" a query?

It's not silly, I didn't explain myself very well. What I was trying to say was to start with a simple query and slowly add more and more bits to it until it no longer works (or you get an error message), for example, build a very simple query, test it, add another variable, test it, add another table, test it, add some more stuff etc etc etc. The purpose being to find out when it does work and when it doesn't - this way you can isolate what is causing the error.

Andrew.