[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-03-26
5
Medium Priority
?
4,320 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 400 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 600 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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