Solved

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

Posted on 2007-03-26
5
4,308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.​

738 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