Solved

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

Posted on 2007-03-26
5
4,307 Views
Last Modified: 2012-05-05
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
Comment
Question by:Andrew_Wallbank
  • 2
  • 2
5 Comments
 
LVL 9

Assisted Solution

by:TheSloath
TheSloath earned 100 total points
ID: 18795988
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
 
LVL 2

Author Comment

by:Andrew_Wallbank
ID: 18796091
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
 
LVL 3

Accepted Solution

by:
paix120 earned 150 total points
ID: 18826362
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
 
LVL 9

Expert Comment

by:TheSloath
ID: 18831346
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
 
LVL 2

Author Comment

by:Andrew_Wallbank
ID: 18832455
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

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question