INSERT INTO og UNION

Skal naturligvis vaere

INSERT INTO Telefon (nummer) SELECT gsm FROM Familie WHERE gsm IS NOT NULL
UNION
SELECT [gsm partner] FROM Familie WHERE [gsm partner] IS NOT NULL

Er du sikker på, at en UNION tillader INSERT. UNION er vel en slags samling af poster. I din INSERT har du ikke noget kriterie for, hvor data skal indsættes. Bliver de indsat de rigtige steder.

Blot et par strøtanker uden de helt store databasetanker. Jeg får gæster og er væk det meste af eftermiddagen.

mugs, jeg lavede en rettelse omtrent samtidig med at du svarede.

Jeg er sikker paa at det jeg gjorde ikke er tilladt (i det mindste fik jeg syntaksfejl.)  Det jeg soeger er en metode der er tilladt og hvor jeg i en enkel query kan indsaette to hold vaerdier, i dette tilfaelde gsm og [gsm partner].

Make a query which returns the result of your UNION query then make an append query which uses the first query to append the result to the telefon table.

You could maybe also try (Not tested)

INSERT INTO Telefon (nummer) SELECT * FROM ( SELECT gsm FROM Familie WHERE gsm IS NOT NULL
UNION
SELECT [gsm partner] FROM Familie WHERE [gsm partner] IS NOT NULL )

terry, thank you for quick response.

For your suggestion # 5 I also get syntax error.  I get the error message (my translation from Dutch, the language for my Access application) "The instruction INSERT INTO contains the following unknown field name: gsm. 

The 'funny' thing is that when I reopen the query then the application has in the sentence 'INSERT INTO Telefon (nummer)' deleted '(nummer)'.  Also the application adds in this and in some other complex queries this at the end: 'AS [%$##@_Alias];'  That looks like a curse in Donald Duck.

Then you say in #4: 'Make a query which returns the result of your UNION query then make an append query which uses the first query to append the result to the telefon table.'  I made the UNION query (called Query1) and double-clicked it which produced a table containing all the values.  How do I then 'append the result to the telefon table'?  I tried 'INSERT INTO Telefon(nummer) Query1' and 'INSERT INTO Telefon(nummer) SELECT * FROM Query1'

is there any chance of you sending me a dB with the tables in and maybe a bit of test data?

ekspertenATsanthell.dk
AT = @

Then you say in #4:....

When you are in query design you should be able to select Append Query from the menu which helps you make the append query.

I just sent it.  The table Mensen is the original table that I am splitting up.  The telephone table is called 'Telefoont', thus with two o's following Dutch spelling.

Its on the way back. I figured out the names of the tables.

There are two examples, one using an extra query and the other where its all in one.

The name you mentioned 'AS [%$##@_Alias is a temporary name that Access gives it unless you give it a name yourself as I have here.(AS TempTable). So when you open the query you actually see a temporary table with this name.

And here is the query

INSERT INTO Telefoon ( nummer )
SELECT TempTable.gsm
FROM (SELECT gsm FROM mensen WHERE gsm IS NOT NULL
UNION SELECT [gsm partner] FROM mensen WHERE [gsm partner] IS NOT NULL)  AS TempTable;

Terry, that was great, it works.

I was trying to understand why it works.  The union select creates a virtual table that is called TempTable with one field which apparantly takes its name from the first query.  If the union select query had started with [gsm partner] I would have had to say:  INSERT INTO Telefoon(nummer) SELECT TempTable.[gsm partner].

Please make a 'svar' for points.

And a warning:  I shall likely have more questions as I get along.

A UNION always uses the field names from the first query so if you use other fields then you need to select those.

I think you mentioned you were located in Belgium, where at exactly?
We've had a few holidays there, like the beers :o)

I live close to Mons, which in Flamish is called Bergen.  It is an hour drive south of Brussels and close to the French border.

Mons!
Har du noget at gøre med forsvaret?

mugs snabelting mail.dk

Nej intet.  For jeg er paa pension.  Men jeg arbejdede for forskellige NATO organisationer fra 1974 til 2006.

thanks for the points