select distinct

If you make a query with an INNER join on Kunder then you will see that which exist in BOTH tables

If you use a UNION JOIN you can see ALL Kunde in both tables.

SELECT DISTINCT Kunde From Table1
UNION ALL
SELECT DISTINCT Kunde From Table1

SELECT DISTINCT Kundenavn FROM [Table1] WHERE Kundenavn IS NOT NULL UNION ALL SELECT DISTINCT Kundenavn FROM [Table2] WHERE Kundenavn IS NOT NULL

I have tried with this and it returns all the Kundenavn from BOTH tables!
Table1
Kundenavn
*********
Lars Larsen
Hans Nielsen
Lene Hansen
Jesper Petersen
Katrine Ellekilde

Tabel2
Kundenavn
*********
Lene Hansen
Hans Nielsen
Lars Larsen
Kirsten Degn
Lisbeth Kjær
Niels Nielsen

The resultset that I need is this:
Lars Larsen
Hans Nielsen
Lene Hansen
Jesper Petersen
Katrine Ellekilde
Kirsten Degn
Lisbeth Kjær
Niels Nielsen


But what I get with SELECT DISTINCT . . . UNION ALL, is this:
Lars Larsen
Hans Nielsen
Lene Hansen
Jesper Petersen
Katrine Ellekilde
Lene Hansen
Hans Nielsen
Lars Larsen
Kirsten Degn
Lisbeth Kjær
Niels Nielsen

The Kundenavn which is the same in both tables are "returned" twice!
And I have 8 tables that I SELECT Kundenavn from!
And I use the field Kundenavn as the name of my spreadsheets, but I don't want to (read: can't!) have spreadsheets that have the same name in a workbook.

Drop the ALL in UNION ALL

I though you wanted to see them ALL :o)

Ahhh...I have to drop the ALL clause, better test it before awarding you with the points! ;)

Terry: Du får da stadig samme kunder med fra begge tabeller! Det rigtige må være:
SELECT DISTINCT
SELECT Kunde From Table1
  UNION
SELECT Kunde From Table2
Atlså at du samler alle kunder i én klamamse og efterfølgende "kører" DISTINCT.

Hi staticdata
A UNION select without the ALL should if I'm not mistaken, should automatically give a DISTINCT. Using ALL should show ALL records.

Well, du har nok ret, men det er da hurtigt testet - så det gør puppetmaster nok.

Well, terry havde ret (og det tog ikke lang tid at test......kun laaaang tid at tildele point!)

thanks, and a happy new year to you both