A2K - how do i make this SQL retrieve distinct values?

[Deano]

SELECT id, department FROM tblAssets UNION SELECT 0 AS id, "{All
departments}" AS department FROM tblAssets; UNION SELECT 0 AS id, "{All
departments}" AS department FROM tblAssets;

This gives me for example,
{All departments}
HI
ENG
ENG
I would like it to return only unique values so in the above results I
would like only 1 ENG.

I'm playing around with the SQL but not having much luck.

Many thanks again
Martin

[MGFoster]

Use UNION ALL instead of just UNION - that gets rid of duplicates.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Deano wrote:

SELECT id, department FROM tblAssets UNION SELECT 0 AS id, "{All
departments}" AS department FROM tblAssets; UNION SELECT 0 AS id, "{All
departments}" AS department FROM tblAssets;

This gives me for example,
{All departments}
HI
ENG
ENG
I would like it to return only unique values so in the above results I
would like only 1 ENG.

[Deano]

MGFoster wrote:

Use UNION ALL instead of just UNION - that gets rid of duplicates.

Cheers I gave that a go but it doesn't work. If I use ALL in both
places where a union keyword appears I get multiple occurrences....

[Deano]

Chuck Grimsby wrote:

In this example, YOU are putting in the duplicate value. You should
stop that. <Grin>

Assuming however, that the duplicates are comming from whatever tables
you are pulling from, you can use a grouping query inside your UNION
to eliminate the duplicates from the tables. The UNION query itself
will eliminate the duplicates.
oK thanks I will give that a go though I'm not sure about the syntax

If you are having duplicates, examine the fields very closely.
You'll probably discover that they aren't exactly the same. Access
can only eliminate duplicates that are exact matches (across the
entire row).

In case they are definitely the same. Hmm. I might first use a saved
query to produce the unique values and use THAT in the SQL - we shall
see...