No dup records

[Guest]

I need to be able to show all records only one time. this is my sql
statement. If there are 3 of the same case number it will not display any of
them, How do i fix it so it will display one of the 3.

SELECT [MyTable Query].[Case Number], [MyTable Query].[Last Name], [MyTable
Query].[First Name], [MyTable Query].DOS, [MyTable Query].Units, [MyTable
Query].[Service Code], [MyTable Query].[Sub Facility], [MyTable
Query].Provider, [MyTable Query].[Expr1009], [MyTable Query].[Reference
Number], [MyTable Query].[Rem Source]
FROM [MyTable Query]
WHERE ((([MyTable Query].[Case Number]) In (SELECT [Case Number] FROM
[MyTable Query] As Tmp GROUP BY [Case Number] HAVING Count(*)=1 )) AND
(([MyTable Query].[Sub Facility])="03"))
ORDER BY [MyTable Query].[Case Number];

 

[David F Cox]

If you are grouping together duplicate records you will just see one result.
No need for count test.

If there is some non-duplicated data then specifying fields as min or max or
first can get you to just one response.

 

[Guest]

In the db there are 1000 records belong to diferent sub facilites as in sub
facility 03 there are 57 records with 47 records that are not duplicated and
10 records that are and are not includes in the 47 records. Of the 10 records
one have 4 of the same the others have 2 of the same.
--
Tom

If you are grouping together duplicate records you will just see one result.
No need for count test.

If there is some non-duplicated data then specifying fields as min or max or
first can get you to just one response.

I need to be able to show all records only one time. this is my sql
statement. If there are 3 of the same case number it will not display any
of
them, How do i fix it so it will display one of the 3.

SELECT [MyTable Query].[Case Number], [MyTable Query].[Last Name],
[MyTable
Query].[First Name], [MyTable Query].DOS, [MyTable Query].Units, [MyTable
Query].[Service Code], [MyTable Query].[Sub Facility], [MyTable
Query].Provider, [MyTable Query].[Expr1009], [MyTable Query].[Reference
Number], [MyTable Query].[Rem Source]
FROM [MyTable Query]
WHERE ((([MyTable Query].[Case Number]) In (SELECT [Case Number] FROM
[MyTable Query] As Tmp GROUP BY [Case Number] HAVING Count(*)=1 )) AND
(([MyTable Query].[Sub Facility])="03"))
ORDER BY [MyTable Query].[Case Number];