[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register 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,314 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 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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