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

Hi Dan,

Many thanks

I have amended the date to 'Date Bird Observed@ It wouldnt allow me to change it to observation date .

So have set up the following which it is still rejecting on syntax

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;

see below. I am really sorry that I cant get this to work by myself and appreciate your support

Answer 3

Ken,

Many thanks for your patience, with your valued help I am now able to run the query with success. One by product of changing the Date names is that on my Form I have a [Master Date] which is automatically pre-filled in the child sub forms dates eg [Date Bird Observed]. I have look at previous back up versions and cant find how I had set that option. Does anything spring to mind?

Regards

Robin

Answer 4

You have Date as a field and then use DATE as an alias for a parameter prompt. IMO, that’s wrong three ways.

“SELECT DISTINCT [Enter year:] AS DATE,”

First change the alias to something other than a reserved word.

SELECT DISTINCT [Enter year:] AS TheYear,

Answer 5

As Duane has pointed out, you should avoid Date as an object name as, being the name of an inbuilt function, its is a reserved keyword.  Also, I think you have misunderstood the point about enclosing object names in brackets.  The brackets should be around the whole object name, not the character.  The query should consequently be like this:

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

Site_Id, Bird_Id

FROM [Bird Records]

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

Note that I have also changed the name of the column name Date to ObervationDate.  You should do similar, both in the query and in the table definition.