How to ignore duplicates in an access query

Question

For example I have a nature database where I record each species identified on a given date in a specific site.

How would I run a query to list those species identified in a given year or a given site, where there will be lots of duplication.

As a sub question can I run a query on a year from a date field rather than performing a ' > x and < y' ?

Thanks in advance

Answer 1

You are missing brackets around the Date Bird Observed column name: 

SELECT DISTINCT  [Enter year:]  AS  [Observation Year],

Site_Id, Bird_Id 

FROM [Bird Records] 

WHERE (YEAR([Date Bird Observed]) =  [Enter year:] 

OR  [Enter year:]  IS NULL)

AND (Site_Id =  [Enter site name:]   

OR  [Enter site name:]  IS NULL)

ORDER BY Site_Id, Bird_Id;

You'll probably now see why experienced Access developers never uses spaces or special characters in object names, but instead uses CamelCase or represent a space with an underscore character as you've done with Site_ID.

Another possible problem could be that the Site_Id column was created with the 'lookup field' wizard when designing the table.  This feature is also eschewed by experienced developers as what you see in the column will not be its actual numeric value, but, confusingly, a text value from a referenced Sites table or similar.  If this is the case then, to use simple parameter prompts, you would need to include the Sites table in the query, joined on Site_Id, and apply the criterion to the site name text column from the Sites table.

A better solution, however would be to reference an unbound combo box in a dialogue form as the parameter.  The combo box would be set up so that its first column, Site_Id is hidden and the visible column would be the text SiteName column or similar.  The value of the common box would then be the hidden Site_Id, so you can use the Site_Id column in the query, without the need to include the Sites table in the query.

You'll find examples of combo boxes like this in the section on 'retrieving data from the database' in my DatabseBasics demo in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In the first form in this section the section on the right of the form includes two combo boxes set up in this way, with the parameters referenced in a report's query with:

WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity

  OR Forms!frmReportDialogue!cboCity IS NULL)

AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer

  OR Forms!frmReportDialogue!cboEmployer IS NULL)

Answer 2

1. Open the query in design view.

Click in an empty part of the upper pane of the query design window.

In the Property Pane, set the Unique Values property to Yes.

2. Let's say you have a field named BirthDate.

In an empty column of the query design grid, enter the expression:

BirthYear: Year([BirthDate])

Clear the Show check box of this column.

In the Criteria row, enter (for example)

1995

or

Between 1991 And 2000

or

<=1990

Answer 3

Hi Hans ,

Many thanks for a quick response. I have tried to apply, but it is coming up with a run time error. I cant work out what I am doing wrong. I attach a screen shot .

Answer 4

Use the DISTINCT predicate to restrict the rows returned to distinct rows, e.g. to return lists of species per site in 2022

SELECT DISTINCT 2022 AS Observation year,

    Site, Species

WHERE YEAR(ObservationDate) = 2022

FROM Observations

ORDER BY Site, Species;

To do the same for a specific site:

SELECT DISTINCT 2022 AS Observation year,

    Site, Species

FROM Observations

WHERE YEAR(ObservationDate) = 2022

    AND Site = "Name of site goes here"

ORDER BY Species;

Rather than hard coding the year and site into the query, including them as parameters which prompt for the values would give you greater flexibility in a single query:

SELECT DISTINCT [Enter year:] AS Observation year,

    Site, Species

FROM Observations

WHERE (YEAR(ObservationDate) = [Enter year:]

    OR  [Enter year:] IS NULL)

    AND (Site = [Enter site name:]

    OR  [Enter site name:] IS NULL)

ORDER BY Site, Species;

This would make each parameter optional and allow you to return a result table by site, or by year, or by both combined.

In a developed application it would be more usual to make each parameter a reference to a control on an unbound dialogue form from which the query, or better still a form or report based on the query, is opened with a command button.  If using the query as the RecordSource for a report, however, omit the ORDER BY clause and use the report's internal sorting and grouping mechanism to order the rows returned.

Answer 5

Hi Ken,

Many thanks for a quick response. I'm struggling to apply this correctly, Where do I enter the above, is it in the Criteria field and if so do I leave the rest empty? Sorry to be a tech incompetent.