No Duplicates

[Dave]

I have a query feeding a form from only one table.
The table has duplicates in one of the fields. (the records are not
duplcated - just this one field).
How can I set the criteria in the query that feeds the form to NOT show
multiple records if this one field has duplicates?

Hope that makes sense

Thanks,
D

 

[John W. Vinson]

I have a query feeding a form from only one table.
The table has duplicates in one of the fields. (the records are not
duplcated - just this one field).
How can I set the criteria in the query that feeds the form to NOT show
multiple records if this one field has duplicates?

Hope that makes sense

If this field has duplicates, and the other fields in the record aren't
duplicated, how do you want Access to decide WHICH other field values to show?

If it is arbitrary, and you just want to show the first record you come to,
make it a Totals query; group by the field with duplicates; and select First
as the aggregate function for the rest. The query will of course not be
updateable.


John W. Vinson [MVP]

 

[Jamie Collins]

I have a query feeding a form from only one table.
The table has duplicates in one of the fields. (the records are not
duplcated - just this one field).
How can I set the criteria in the query that feeds the form to NOT show
multiple records if this one field has duplicates?

Hope that makes sense

Thanks,
D

Example using Northwind:

SELECT T2.OrderID, T2.ProductID
FROM [Order Details] AS T2
INNER JOIN
(
SELECT T1.OrderID
FROM [Order Details] AS T1
GROUP BY T1.OrderID
HAVING COUNT(*) = 1
) AS DT1
ON T2.OrderID = DT1.OrderID
UNION ALL
SELECT T1.OrderID, NULL
FROM [Order Details] AS T1
GROUP BY T1.OrderID
HAVING COUNT(*) > 1;

Jamie.

--