Union query producing duplicate records

[Jennifer]

I have a union query that is giving me duplicate records. How do I set for
unique records only.

SELECT ID
FROM 1qryLOAfromHR;
UNION Select ID
FROM 1qryLOANARC;

 

[Sylvain Lafontaine]

Remove the first ;

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[KARL DEWEY]

Try this --
SELECT ID
FROM 1qryLOAfromHR
GROUP BY ID
UNION Select ID
FROM 1qryLOANARC
GROUP BY ID;

A union query does not use more than one semicolon as you had.

 

[Jennifer]

Still giving me duplicates. Not every record but some are duplicated.

 

[KARL DEWEY]

Maybe you have some leading spaces.
Try this as criteria in a new query --
Like " *"

 

[Jerry Whittle]

SELECT DISTINCT ID
FROM
(SELECT ID
FROM 1qryLOAfromHR
UNION
SELECT ID
FROM 1qryLOANARC)
ORDER BY 1;

What data type is ID? If it's text you might have some leading or trailing
spaces.

 

[John W. Vinson]

A union query does not use more than one semicolon as you had.

Actually it works with or without the semicolon, Karl.

 

[John Spencer]

If it is displaying duplicates I would suspect that there are some leading or
trailing spaces on id.

You might try the following and see if you are still seeing duplicates
SELECT Trim(ID) as Trimmed
FROM 1qryLOAfromHR
UNION
Select Trim(ID)
FROM 1qryLOANARC
ORDER BY Trimmed

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