• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4341
  • Last Modified:

Access Query - Query to display only first duplicate record and all non-duplicates

I have a database (Access 2000) of Bird sightings, and am trying to create a Query that can produce 'year lists', by showing all the individual species of birds seen in a specific year.
As I may have seen a particular species more than once in a year, it may appear mulltiple times if I simply search by Date.
How can I create a query that will display ALL records for a year, but where there is a Duplication i.e. 2x Crow, only dispaly the FIRST instance of this record.  I have tried the 'Group' function, but this does not work.
I don't want to delete the duplicate's as they are all valid for the database, but not for the 'Year List'.
The Duplicates wizard only trells me where the duplicates are.

The Fields of interest are
Species Name - Date - Location - Grid_Ref - Abundance

Thanks
Andrew
0
Andrew_Wallbank
Asked:
Andrew_Wallbank
  • 2
  • 2
2 Solutions
 
TheSloathCommented:
Create a query that GROUPS BY the [Species Name] and MIN [DateSpotted].
Create another query and add the above query and your table, link the [Species Name] fields and the new [MinDateSpotted] field to the [DateSpotted] field in the table.

This should then only list the first sighting of each Species.

If you want to see it by year, then add another fields to the first query and use the function Year([DateSpotted])
0
 
Andrew_WallbankAuthor Commented:
Excellent, the first bit worked.  I was setting the Species name as FIRST.  Right idea, backward thinking.

That gives me a list of individual species, as expected.

When I create the second Query however it doubles all of the records, so I have obviously linked the fields incorrectly.  Are you syaing that both the [Species Name] and [MinDateSpotted] fields in the query should be linked directly to the [DateSpotted] field in the table? or have I mis-understood?
0
 
paix120Commented:
Is abundance a numerical field? And is Date Spotted a Date/Time type? If so, go to your query's SQL view and paste in this query:

SELECT Table1.Species, Year([DateSpotted]) AS YearSpotted, Min(Table1.DateSpotted) AS FirstDateSpotted, Sum(Table1.Abundance) AS SumOfAbundance
FROM Table1
GROUP BY Table1.Species, Year([DateSpotted]);

Replace Table1 with your table name and run the query. It will give you a list of bird species sorted alphabetically and grouped by year, along with the first date spotted that year, and the total abundance. If I have misunderstood any of your fields, you can switch to design view and edit or feel free to ask me for any further help.

Hope this helps!

0
 
TheSloathCommented:
If you would like the first time a species was 'ever' registed as spotted then use my suggested query. However paix120 has give you a query to see the first spotting in each year for a species - which is probably what u want: I was being slow.

Note that the SumOfAbundance is the total number seen in a year, not on the first day they were seen.

Good luck
0
 
Andrew_WallbankAuthor Commented:
Yes, Abundance is a numerical field, and Date Spotted a time/date field.

TheSloath.
I was after both results really, but would have worked on the First spotted each year after I had figured out how to get the thing working in first place, from your comment.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now