How to select the first (oldest) record from a table of very similar records

[John Immelman]

I use Access at as bird sighting database. Each sighting takes data from 4 tables - bird type/name, location of sighting, activity (road trip etc) when sighting made, and month and year of sighting.
The system has developed over years and is very successful.
BUT birders also want to record "lifers', birds that are sighted for the first time. The problem is that the Sighting table has many records where the same bird type has been recorded and I am unable to create a query that will only list the first (oldest) sighting of each bird.
The Sighting query is shown below. I want to create a list that only shows the first sighting of an Antarctic Tern (no problem as only one record), Apostlebird, Atlantic Common Tern (also no problem), Australian Darter (only the first wanted), etc etc.
Can anyone suggest how I filter the query to achieve a "Life" list?

[ArviLaanemets]

Try something like

SELECT sl.*
FROM qrySightingList sl
WHERE sl.SightingDate = (SELECT MIN(sl0.SightingDate) FROM qrySightingList sl0 WHERE sl0.BirdCommonNameID = sl.BirdCommonNameID)

NB! In case the first sighting of some bird was on several locations at same date, your get all those sightings for this bird!
And I hope SightingDate is really a field of date type! In case this field is a character string, you have to add a date field to your table, and to update this field with calculated dates - e.g with 1st of month in SightingDate field, or with date calculated from ActivityDescription field in case this is possible (for cases where ActivityDescription is like "**Social Birding", the only way will be based on field SightingDate, or on combo of fields SightingYear and SightingMonth) . And after that replace SightingDate in my example with name of this calculated date field. This is because there is no way you get date strings ordered correctly, except for case they are in format "yyyymmdd" (e.g "Aug 2020" will be less than "Jan 2020", or than "Jan 2000").

[John Immelman]

Try something like

SELECT sl.*
FROM qrySightingList sl
WHERE sl.SightingDate = (SELECT MIN(sl0.SightingDate) FROM qrySightingList sl0 WHERE sl0.BirdCommonNameID = sl.BirdCommonNameID)

NB! In case the first sighting of some bird was on several locations at same date, your get all those sightings for this bird!
And I hope SightingDate is really a field of date type! In case this field is a character string, you have to add a date field to your table, and to update this field with calculated dates - e.g with 1st of month in SightingDate field, or with date calculated from ActivityDescription field in case this is possible (for cases where ActivityDescription is like "**Social Birding", the only way will be based on field SightingDate, or on combo of fields SightingYear and SightingMonth) . And after that replace SightingDate in my example with name of this calculated date field. This is because there is no way you get date strings ordered correctly, except for case they are in format "yyyymmdd" (e.g "Aug 2020" will be less than "Jan 2020", or than "Jan 2000").

Thank you for the suggestion. I will explore your idea further.

[John Immelman]

Try something like

SELECT sl.*
FROM qrySightingList sl
WHERE sl.SightingDate = (SELECT MIN(sl0.SightingDate) FROM qrySightingList sl0 WHERE sl0.BirdCommonNameID = sl.BirdCommonNameID)

NB! In case the first sighting of some bird was on several locations at same date, your get all those sightings for this bird!
And I hope SightingDate is really a field of date type! In case this field is a character string, you have to add a date field to your table, and to update this field with calculated dates - e.g with 1st of month in SightingDate field, or with date calculated from ActivityDescription field in case this is possible (for cases where ActivityDescription is like "**Social Birding", the only way will be based on field SightingDate, or on combo of fields SightingYear and SightingMonth) . And after that replace SightingDate in my example with name of this calculated date field. This is because there is no way you get date strings ordered correctly, except for case they are in format "yyyymmdd" (e.g "Aug 2020" will be less than "Jan 2020", or than "Jan 2000").

Thank you for the suggestion. I will explore your idea further.

You assumed correctly, the Sighting date fields were in text format. I created a new column in tblSightingList, concatenated the SightingMonth and Year fields (again) and formatted them as Date, yyyy mmm, called SightingDateFormatted.
Now I have the problem that you anticipated - the same bird is listed more than once if it was seen on the same Activity (same Activity Date).
On reflection I do not need to have the first (oldest) Sighting, although this would be preferred, I just need one, any one.
How would you suggest that I create a Life List than shows all the birds just once, but includes at least one Location, or two?

[ArviLaanemets]

How would you suggest that I create a Life List than shows all the birds just once, but includes at least one Location, or two?

It is difficult to suggest anything, without having any clue about the table, where all sightings are registered. The picture you presented is of query result table, and I suspect the query gets data from several tables.

In case the actual sightings table has autonumeric field as primary one, then you can try to add an additional condition for WHERE clause to select the sighting with lowest value of PK of all entries with this lowest sighting date for given location (I don't think it will be simple, and unless your sighting table isn't big, this query may be a slow one). Probably you have to make the query for min dates a saved one, with sighting ID included, and create another query to select records with lowest sighting ID as next step. In case you don't have any field which can identify the record being inserted earlier/later than other records, tossing a coin, or using ordering of any field you like, and selecting from top there, is only way remaining for you.
Really the current situation is a good example why not enter calculated values as main data into your tables. Had you entered a real sighting dates instead (stripped) text ones, then probably most of cases with several sightings in same month would be in different days of this month. And you'd need a single date field instead 3 text ones, as to get the year or month from date whenever there is a need for this, is a simplest thing in Access.

Looking at your query image, I did see something weird too! E.g. in 5th row at top, the SightingDate is "Mar 2020", but in ActivityDescription is a date "2017 Dec/Jan"!

[John Immelman]

It is difficult to suggest anything, without having any clue about the table, where all sightings are registered. The picture you presented is of query result table, and I suspect the query gets data from several tables.

In case the actual sightings table has autonumeric field as primary one, then you can try to add an additional condition for WHERE clause to select the sighting with lowest value of PK of all entries with this lowest sighting date for given location (I don't think it will be simple, and unless your sighting table isn't big, this query may be a slow one). Probably you have to make the query for min dates a saved one, with sighting ID included, and create another query to select records with lowest sighting ID as next step. In case you don't have any field which can identify the record being inserted earlier/later than other records, tossing a coin, or using ordering of any field you like, and selecting from top there, is only way remaining for you.
Really the current situation is a good example why not enter calculated values as main data into your tables. Had you entered a real sighting dates instead (stripped) text ones, then probably most of cases with several sightings in same month would be in different days of this month. And you'd need a single date field instead 3 text ones, as to get the year or month from date whenever there is a need for this, is a simplest thing in Access.

Looking at your query image, I did see something weird too! E.g. in 5th row at top, the SightingDate is "Mar 2020", but in ActivityDescription is a date "2017 Dec/Jan"!

You have eagle eyes! Those entries had mistaken Activities, and have now been fixed.

Regarding the 'real' dates and time, this is not practical when out in the bush doing birding. We carry a bird identifying book and a list of birds. Once identified (and this takes time) in the case of a new bird, the Location is noted next to the bird name on the list, and we move on. At the end of the activity, sometimes during, the Sightings are entered into the database, with a month date close enough. In case the whole process is done on an iPad or similar, and this is unlikely, the an actual date and time will not be available.

My last resort is to create a new check box field in the SightingList where I specify, on entering, whether this Sighting is a Lifer or not. Then I can search for Lifers, but it takes time to check whether the bird has been seen before.
Oh well, life is not perfect.
Thansksfor the help.

[ArviLaanemets]

My last resort is to create a new check box field in the SightingList where I specify, on entering, whether this Sighting is a Lifer or not. Then I can search for Lifers, but it takes time to check whether the bird has been seen before.

OK. When you add a check box or boolean field to your table, then you can run the query like the one I explained at start (the one returning all entries with earlier month per location), changed to Update query, to update this field in your table with checked/True for all records having a match in query. After that you run a simple updatable query on your sightings table with records ordered by BirdName, and the filter set for check/boolean to be true. For every BirdName, you uncheck all records except one. This is all you need to get all historical data correct.
Probably you also have to create a saved parameter query to return the Lifer row for bird with it's name specified as parameter (or count of entries for this bird). When you are entering new sightings, and you suspect this may be 1st sighting for this bird, you can easily check your suspect by running this query.
You also can have a use for query, which returns a list of bird names/ID's, which don't have any row with this check field checked/True. Something like (on fly):

SELECT DISTINCT ylt.BirdCommonNameID, ylt.BirdCommonName
FROM (YourListingTable ylt LEFT JOIN (SELECT yltc.BirdCommonNameID FROM YourListingTable yltc WHERE YourCheckField = True) As LiferList ON LiferList.BirdCommonNameID = ylt.BirdCommonNameID) 
WHERE LiferList.BirdCommonNameID Is Null

[isladogs]

As an alternative to using a subquery for this, you could consider a non equi-join instead. For the general idea see e.g. Efficient Querying: Finding Maximum Values for Each Distinct Value in a Column (nolongerset.com)

[John Immelman]

OK. When you add a check box or boolean field to your table, then you can run the query like the one I explained at start (the one returning all entries with earlier month per location), changed to Update query, to update this field in your table with checked/True for all records having a match in query. After that you run a simple updatable query on your sightings table with records ordered by BirdName, and the filter set for check/boolean to be true. For every BirdName, you uncheck all records except one. This is all you need to get all historical data correct.
Probably you also have to create a saved parameter query to return the Lifer row for bird with it's name specified as parameter (or count of entries for this bird). When you are entering new sightings, and you suspect this may be 1st sighting for this bird, you can easily check your suspect by running this query.
You also can have a use for query, which returns a list of bird names/ID's, which don't have any row with this check field checked/True. Something like (on fly):

SELECT DISTINCT ylt.BirdCommonNameID, ylt.BirdCommonName
FROM (YourListingTable ylt LEFT JOIN (SELECT yltc.BirdCommonNameID FROM YourListingTable yltc WHERE YourCheckField = True) As LiferList ON LiferList.BirdCommonNameID = ylt.BirdCommonNameID) 
WHERE LiferList.BirdCommonNameID Is Null

I have discovered that converting the 'text' date to a real date in the SightingList works fine - I can do away with the added date field.
Your Queries all worked as planned - now I have a qryLiferList that includes all first time Sightings. The last Query highlighted 3 firsts that I had inadvertently 'unchecked' so I was able to get these included.
Now to the last stage:
the frmSightingListAdd uses 3 buttons to search tables for 'matches' and then populates the respective fields. I want to use your suggestion that if not sure about being a Lifer, I can do a search of the LiferList and return Yes/No. Because I am normally sure what has been seen before, I would not check every entry, only when not sure - unless this can be automated, every entry checked and Yes added if no other found. Does this make sense?
Or should I add a button 'Check if a Lifer' and then manually check the Yes/No box?
A new image is attached to help explain.

[ArviLaanemets]

I want to use your suggestion that if not sure about being a Lifer, I can do a search of the LiferList and return Yes/No. Because I am normally sure what has been seen before, I would not check every entry, only when not sure - unless this can be automated, every entry checked and Yes added if no other found. Does this make sense?
Or should I add a button 'Check if a Lifer' and then manually check the Yes/No box?

I don't think having this option build into your DB will be very useful - having an option to check, is the bird sighted before will be probably enough.

But maybe you need a better preparing of your sighting tours? Of course I haven't a full overview not about how you register your sightings, nor what data you have really stored. E.g.
Are you registering repeated sightings of species in sighting tour, and when yes, then are you simply counting them, or you have a separate entry in whatever media (paper table, a table in tablet or laptop, whatever) you use in your field trip;
In your DB, have you registered also species you don't have any sightings jet;

Anyway, whatever media you use in your field trip (paper, text file, excel file, ...), my advice is have a new copy of this media at least for every new field trip. Then:
In this copy, you can register all trip-specific info once (start and end DATES of trip, and probably location information too);
When you use paper media, before you are going out, you can print out a list of all currently registered species in your DB. In case there may be species not sighted before, this list mus contain a column where is indicated, is given specie sighted before or not - otherwise all entries entered manually are probably for new species;
In case you use tablet or laptop, you can create a new copy of your registration table whenever you need this (a separate table for every day, or a separate table for different locations in this trip, etc.);
In case instead of simple text file on your tablet or laptop, you use something more sophisticated (e.g. Excel workbook), you can have there separate sheets with registers e.g. specie names, along with ID's for those names, families of those species, etc., and e.g. with Excel, you can simply select species from Data Validation List, instead typing some long text and probably making a lot of typing errors. Having in table where you register your sightings a field, where common name id is calculated based on species name you selected, the formula returns an error in case this name was not found - which will alert you about possible first sighting when you are entering data into your DB from this trip media later.

[John Immelman]

I don't think having this option build into your DB will be very useful - having an option to check, is the bird sighted before will be probably enough.

But maybe you need a better preparing of your sighting tours? Of course I haven't a full overview not about how you register your sightings, nor what data you have really stored. E.g.
Are you registering repeated sightings of species in sighting tour, and when yes, then are you simply counting them, or you have a separate entry in whatever media (paper table, a table in tablet or laptop, whatever) you use in your field trip;
In your DB, have you registered also species you don't have any sightings jet;

Anyway, whatever media you use in your field trip (paper, text file, excel file, ...), my advice is have a new copy of this media at least for every new field trip. Then:
In this copy, you can register all trip-specific info once (start and end DATES of trip, and probably location information too);
When you use paper media, before you are going out, you can print out a list of all currently registered species in your DB. In case there may be species not sighted before, this list mus contain a column where is indicated, is given specie sighted before or not - otherwise all entries entered manually are probably for new species;
In case you use tablet or laptop, you can create a new copy of your registration table whenever you need this (a separate table for every day, or a separate table for different locations in this trip, etc.);
In case instead of simple text file on your tablet or laptop, you use something more sophisticated (e.g. Excel workbook), you can have there separate sheets with registers e.g. specie names, along with ID's for those names, families of those species, etc., and e.g. with Excel, you can simply select species from Data Validation List, instead typing some long text and probably making a lot of typing errors. Having in table where you register your sightings a field, where common name id is calculated based on species name you selected, the formula returns an error in case this name was not found - which will alert you about possible first sighting when you are entering data into your DB from this trip media later.

All birders (novice birders are called "Twitchers") look for better ways to record what they see once they have identified it. But for practical reasons - reading a mobile screen in bright sun is difficult, very often no communications, etc, etc - the paper option is still the most common. We print out our existing list of Lifers before starting a road trip. During the 3 to 5 weeks of travel we tick the interesting birds we see (not all common ones) and add new ones at the end. When home we enter into the database.

Now for the last step: can you help me use DCOUNT to messagebox that the BirdCommonNumberID entered is a Lifer ie. it does not exist in the tblSightingList. I have tried this vb as the Before Update Event:

Private Sub BirdCommonNameID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblSightingList", "BirdCommonNameID") = 0 Then
MsgBox "This bird is a Lifer", vbInformation
Cancel = True
End If
End Sub


but this does do what I need. Please offer suggestions.

[Welshgasman]

Perhaps look at the syntax for DCount()?
https://support.microsoft.com/en-us/...a-11a64acbf3d3

[John Immelman]

Perhaps look at the syntax for DCount()?
https://support.microsoft.com/en-us/...a-11a64acbf3d3

You are correct, my DCOUNT syntax was not right. After much Googling I settled on:

If DCount("BirdCommonNameID", "tblSightingList", "BirdCommonNameID='" & Me.BirdCommonNameID & "'") > 0 Then
MsgBox "Name Is Already In Database!"

but this does not give any response so I am still stuck.

[Welshgasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.


Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.