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

You can apply the DISTINCT keyword to a query one of two ways. Either Open the Properties dialog and select Unique Records. Or open the SQL View and just type in DISTINCT after SELECT.

As to the syntax error please show us your full SQL statement.

Answer 3

Where do I enter the above..............

What I gave you is the SQL statement for a complete query.  To create the query open the query designer and switch to SQL view by means of the View icon on the ribbon.  Then paste in the SQL statement I gave you in place of what's there already.  You'll then have to change the column and table names in the statement to your actual column and table names.  Remember that if you've included spaces or other special characters in the names they must be enclosed in square brackets [like this].  If in doubt include the brackets; they are harmless if not actually needed.  You should then be able to open the query in the usual way.

Answer 4

please share some data and your expected result.

Answer 5

Hi Ken, thanks for the elaboration ,

So I have tried the following

SELECT DISTINCT [Enter year:] AS DATE,

Site[\_]Id, Bird[\_]Id 

FROM Bird[ ]Records

WHERE (YEAR(Date) = [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;

which is still coming up with an error on DATE in the first row with the statement 'The select statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect'

The table is Called - Bird Records

withe following entries

DATE

Site_ID

Bird_ID

Please can you advise what I am doing wrong

Many thanks