Finding a minimum value in a query

[Trystyn]

Hi,

I'm looking for some help on how I would go about doing the following:

I have a query YearLink which links an primary key of a bird species table to the years they were seen so it looks like this:

1 2000
1 2001
1 2004
2 2000
2 2002
3 2000
4 2003

and so on.

What I would like to do is have an expression in the third field of the query which returns true or false depending on whether the year is the earliest sighting of the bird by looking for the earliest year for that particular bird ID:

1 2000 True
1 2001 False
1 2004 False
2 2000 True
2 2002 False
3 2000 True
4 2003 True

This would save me having to input whether a species seen was a first sighting as it could then be looked up by finding the True values.

Thanks for your time.

 

[plog]

Assuming BirdId and BirdYear are columns in YourTableNameHere, this would be the SQL for your query:

SELECT BirdId, BirdYear, IIf([BirdYear]=DMin("BirdYear","YourTableNameHere","BirdId=" & [BirdId]),"True","False") AS EarliestYear
FROM YourTableNameHere;

 

[Trystyn]

Assuming BirdId and BirdYear are columns in YourTableNameHere, this would be the SQL for your query:

SELECT BirdId, BirdYear, IIf([BirdYear]=DMin("BirdYear","YourTableNameHere","BirdId=" & [BirdId]),"True","False") AS EarliestYear
FROM YourTableNameHere;

Thank you, thats brilliant. Nailed it in one.

 

[Trystyn]

Further to this, I have a form which views all the birds seen in any given year. Master field Year, child BirdYear.

When I load the form or flick between records, the requery is taking a great deal of time. Is there anything I can use to prevent this?

My thoughts were to try copying the values of "EarliestYear" above into a table as they are generated with each new or edited record so that the database should only requery at this point but I dont know if this is the best approach or how to do it?

 

[plog]

Is this Form thing a seperate issue?. Because no where do you mention displaying EarliestYear. Is that field used in this form system? If not, I wouldn't use the query that generates it as a datasource for your form.

 

[Trystyn]

Is this Form thing a seperate issue?. Because no where do you mention displaying EarliestYear. Is that field used in this form system? If not, I wouldn't use the query that generates it as a datasource for your form.

Sorry, I adopted EarliestYear from your own code above. This tells me for any given year in the form whether the species seen was a 1st in that year. It all runs perfectly except for the speed, it requeries each time I jump from one record to the next.

 

[plog]

I'm having a hard time visualizing this, can you provide a screen shot of the form?

 

[Trystyn]

Ok

So previously, I was manually inputing the Yes/No column. Now using your SQL code, I query this column for the yes/no dependent on whether it's the earliest year.

The list of birds is linked to the highlighted year field in the main form and switching from one year to the next now takes a very long time.

Hope this clarifies.

 

[plog]

I've got another idea for this query, but I'm unsure if it will have a performance impact. Its not too difficult, so you might give it a shot.

Going back to your initial post you said you had a YearLink query that lists BirdId and BirdYears. Using that create a query like this:

SELECT BirdID, Min(BirdYear) As FirstYear FROM YearLink GROUP BY BirdID;

That will give you the first year each BirdId is seen. Call this query something like BirdFirstYear. Then instead of the prior definition I gave you for FirstYear use this query. What you do is bring it into the query that underlies that form and link the BirdID from each table, then compare the FirstYear field from BirdFirstYear to the Year field currently in the query underlying that form. If they are the same, then thats the first year for that bird, if not, then no.

Again, I know this is another way to accomplish the same thing, I'm just uncertain it would be faster.

 

[Trystyn]

I've got another idea for this query, but I'm unsure if it will have a performance impact. Its not too difficult, so you might give it a shot.

Going back to your initial post you said you had a YearLink query that lists BirdId and BirdYears. Using that create a query like this:

SELECT BirdID, Min(BirdYear) As FirstYear FROM YearLink GROUP BY BirdID;

That will give you the first year each BirdId is seen. Call this query something like BirdFirstYear. Then instead of the prior definition I gave you for FirstYear use this query. What you do is bring it into the query that underlies that form and link the BirdID from each table, then compare the FirstYear field from BirdFirstYear to the Year field currently in the query underlying that form. If they are the same, then thats the first year for that bird, if not, then no.

Again, I know this is another way to accomplish the same thing, I'm just uncertain it would be faster.

I will give this a shot when I get a bit of time. I have managed to get around the problem last night by using an update query. This copies the values obtained from your original code back into the manual entry field of my table when I exit my input form. As a result I can leave the form and it's source query from the above screen shot alone.

Thank you again for your input on this. It's much appreciated.