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

Check the two Link properties of the subform control on the main form.

Answer 3

The LinkMasterFields and LinkChildFields properties of a subform control can include multiple fields separated by a semi colon, e.g.

[Bird ID];[Master Date] for the LinkMasterFields property

And:

[Bird ID];[Date Bird Observed] for the LinkChildFields property

Answer 4

Ken

Many thanks

Regards

Robin