Prevent showing duplicates based on a field

[Brucels]

I have an Access table in which I record information about photography of art
objects in a museum collection. Any object may have been photographed a
number of times over a period of years. I would like to create a query which
displays only one instance for each object. Setting UniqueRecords and
UniqueValues doesn't seem to accomplish this. Any suggestions for what I can
do would be greatly appreciated.

Thanks,
Bruce

 

[Guitarzann]

Inside the Report Design, you can select the "Hide Duplicates"-"Yes" within
the properties of the control box.

 

[Daryl S]

Brucels -

I expect you are pulling more fields than just the objectID. Even with
Unique Records or Unique Values, if you are also displaying, for example, the
date of the photograph, then two different dates will cause two different
records to display.

If you want only one record per object, then you need to decide what is most
important to show in the other fields, or remove the other fields from your
query. For example, a query that would show just the objectID and the most
recent PhotoDate, you would have something like this:

SELECT objectID, max(PhotoDate) from tblPhotos
GROUP BY objectID;

If you are in Query Design mode, select View Totals from the menu or click
on the large SUM icon. You want Group By in the objectID field, but you
must select something else in all the fields you want displayed that could
have more than one value (e.g. PhotoDate, Photographer, ???).

Hope that helps!

 

[John Spencer]

To expand on that

SELECT objectId, First(PhotoDate), First(Photographer), First(OtherField)
FROM YourTable
GROUP BY ObjectID

You could also use a query with a correlated sub-query in the where clause

SELECT *
FROM YourTable
WHERE PhotoDate =
(SELECT Max(PhotoDate)
FROM YourTable as Temp
WHERE Temp.ObjectID = YourTable.ObjectID)

Or one of a few other techniques. It kind of depends on WHAT you want
returned and of the multiple records for any one object which one you want to see.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Brucels]

Thanks to all who replied with suggestions. I must admit that I ended up
taking the coward's way out: I used Microsoft's instructions for deleting
duplicate records from a table by creating a copy of the structure of the
table that contains duplicates, making primary keys of the fields that
contain duplicates, and running an append query from the original table to
the new table.

Not elegant, but it solved my immediate challenge.

Bruce