No duplicates

[Thomp001]

I am working with several compliated queries in which I need to eleminate duplicates in my query based solely on the data from one field.

Is their a way to write into SQL or the design view to eleminate all records that are duplicated in a single field.

 

[Mile-O]

Make this the criteria for your duplicate data field.

In (SELECT [YourField] FROM [tblYourTable] As Tmp GROUP BY [YourField] HAVING Count(*)>1 )

 

[Thomp001]

No Duplicates

I tried the query but it seems to put me into some to put me into some kind of loop.
5 minutes later and it is still running the query.

Here is the SQL as I ran it.

SELECT DISTINCT Mission2.entry_nbr, Mission2.AIRBILL_NBR, Mission2.TRACKING_NBR, Mission2.arrival_work_dt, Mission2.pri, Mission2.cst, Mission2.rls, Mission2.PLS, Mission2.bts_reason
FROM Mission2
WHERE (((Mission2.AIRBILL_NBR) In (Select [AIRBILL_NBR] FROM [Mission2] as TMP Group By [AIRBILL_NBR] Having Count(*)>1)) AND ((Mission2.pri) Is Not Null) AND ((Mission2.cst) Is Not Null));

 

[Pat Hartman]

Mile-O-Phile's solution will work (although >1 may need to be changed to =1), but if your table is large you may run into performance issues because Jet does not optimize sub selects very well. You might also consider a totals query that is then joined to your main query.

query1:
Select YourField, Count(*) As ValueCount
From YourTable
Group By YourField;

query2:
Select .....
From YourTable as t Inner Join query1 as q ON t.YourField = q.YourField
Where q.ValueCount = 1;

I believe the difference is that Jet runs the sub-select query for every row in [YourTable] and in the two query method, the totals query only needs to be run once rather than for every record of [YourTable]. In certain situations (updating a table with certain types of calculated results), the sub-select is the only answer regardless of efficiency but not this one.