Hjælp til, at samle alle data i en tabel

maria>IF you have other related tables then this could be a problem with Primary/foreign keys. But if all your eggs are in one basket then you COULD try making a UNION query. This will show ALL records from the tables you include. Then you can make a new EMPTY table which has the same layout as the originals. Then you make an append query taking the result of your UNION query and appending it to your NEW table!

an example of a UNION query

SELECT * FROM [kunde 1]
UNION
SELECT * FROM [kunde 2]
UNION
.....

IF there are duplicate records which you want to include then you use

UNION ALL......

Men terry dataen skal stå under hinanden...og dehar alle den samme primære nøgle der hedder kontakt-id

when you say "stå under hinanden" a UNION join will show records under each other!

Do you use the primary key as a relationship to other tables? If you do NOT then you dont need to include this in the UNION select. Then when you append them to the new table all records will get a new primary key!

you now have to include the fields because you must NOT include kontakt-id!

SELECT fld1, fld2, fld3 ....... FROM [Kunde 1]
UNION
SELECT fld1, fld2, fld3 ....... FROM [Kunde 2]
UNION
.....

TRY making a UNION join with ALL fields just so you can see what it does!

You have to enter the SQL directly in SQL query window!

hmm jeg kan ikke få det til at virke i foresp. kommer det til, at stå hen af og der står

Firmanavn kunde 1, firmanavn kunde 2

you MUST NOT write kunde 1
Normally there should NOTbe spaces in the table names!
Write
[kunde 1]

For example>
SELECT Firmanavn FROM [kunde 1]
UNION
SELECT Firmanavn FROM [kunde 2]

You have to enter the SQL directly in the SQL view, you can not use the query designer when making UNION queries

If you right click on the are where you normally add your tables yiu get a menu where you can choose SQL specific and then UNION. If you select union you will now be in SQL view where you can directly enter the SQL for the union query

well I think that is me for the night :o)

Terry det virker med den kode du har skrevet her SELECT Firmanavn FROM [kunde 1]
UNION
SELECT Firmanavn FROM [kunde 2]

Men hvordan ser den øjagtig ud når det er alle data fra tabellerne!!

It looks the same as if you had selected all records from one table and then all records from another table on top of each other, not beside each other.

If there are 20 columns in the tables then there will be 20 columns in the result, ready to copy to your NEW table (with 21) columns. And if yiu are still awake NOTICE I write 21! the first column is your autonumber which you do NOT append to as this gets done automatically!

ok jeg har fundet ud af det - jeg er bare lidt små dum;-) - Jeg har lavet den både som en tilførelsesforsp. og som du beskrev.. Men med dit eks. ved jeg ikke helt hvordan jeg får dataen over i en ny tabel!!

Takker Terry, men jeg vil gerne hvis du kan beskrive hvordan jeg får dataen med over i en ny tabel

You now have a UNION query which selects records from all of your tables.
You should also have a new table with the same structure as your original tables.
Now make a new query and choose the UNION query as the source. (from the list of tables and queries).

Now you need to alter the query to an append query. In the menu there is a little arrow pointing down. You can choose the type of query here, choose append query, and then choose the table you want to append to.

Now using the mouse you mark ALL fields (not the *) in the table and drag them over to the rows and columns area of the query. You should now see that the row named "Field:" contains all of the row names from the table, and because the names of the fields in the new table are the same then the row named "Append To:" will have the same name.

Then save the query, and you are ready to try it :o)
Hope that helps.

records and then COMPACT the dB. When you compact the dB it also resets the autonumber fields to 0.

Terry hver gang jeg kører tilføjelsesforsp. så kopiere den de samme info ind - Er der evt. et alternativ??

Maria, what are you expecting it to do? This is what you asked for isnt it?
"Nu vil jeg gerne samle daten fra de 3-8 tabeller i en"
and once you have run the append query ONCE then it is done!!!!

Perfekt - Bare misforstået lidt

OK :o)