Filter query results - help please!

[BadgerLikeSpeed]

Hi,
Probably (another) simple question, but I have a query running on 2 other queries. Basically I have a query that returns the crew on board a vessel during a given date range, and another table giving the emergency drills carried out during that time. The third query takes the date of the drill and returns the crew who were on board at the time.
The crew lists are input every week, to be distributed, with a sign on/off date. Often the time spent on board is over a week in length, so each crew member may be entered more than once. But a crew member may sign off at the start of a week, and sign on again before the end so I can't just filter the query by name, and multiple crew could sign on/off on the same day, so filtering by day is not an option either.
What I'd like to do is take 3 fields, Name, Day and DrillType and remove any records where all 3 are duplicated.
Is this possible?

 

[jzwp22]

Have you set the query to return unique records or unique values only?

From the design grid view open the property sheet for the query and set the unique records property or the unique values property to yes.

In SQL text, you would just need to add either DISTINCTROW (unique records) or DISTINCT (unique values) key word

SELECT DISTINCTROW field1...
FROM tablename

or

SELECT DISTINCT field1...
FROM tablename


There is a distinction between unique records and unique values. For the unique records, all values in all fields between two (or more) records records would have to be the same in order to show only one. It does not matter what fields you choose to display via the SELECT clause. The unique value property, on the other hand, displays only 1 record for all records that have the same value for those fields as specified in the SELECT clause

For example, let's say that you had the following data:

EmpID|EmployeeName
1|Harry
2|Ron
3|Albus
4|Harry

If you wanted your query to return just the employee name, the unique records property (SELECT DISTINCTROW EmployeeName) would return all 4 records since the EmpID values are different for the 4 records

The unique values property (SELECT DISTINCT EmployeeName) woud return only 3 records (Harry, Albus, Ron)