Er der begrænsninger for antallet af UNIONs?

32 tables in a query

you could make 10 sub queries but why not have a temp table and INSERT the output of each table into that?

Jeg får de 100 tabeller fra excel filer som er linket via ODBC til Access. Der vil være opdateringer i excelfilerne. Jeg har forsøgt mig en update men har ikke fået det til at virke.
Kender du syntaksen?

I'me puzzled! What has an UPDATE to do with a UNION join?

you can use an UPDATE query to update thelinked files

OK... det gik lidt hurtigt.
100 tabeller skal samles, og indholdet opdateres løbende.
Hvordan gør man det smartest? Kan det måske laves sådanne?:

1)Kan det lade sig gøre at samle dem i en tabel og herefter opdatere denne tabel?

2)Kan jeg lave 4 (med 25x13 poster) queryes via UNION's og derefter lave en UNION som smaler de 4 forrige?

smaler = samler :-)

I dont know what the Excel files contain, but yes it is possible to "samle" all the data from the 100 tables into ONE table.

Jaaeee... men helt præcist hvordan? Er det noget der kan gøres op i point *G* ?
INSERT INTO <samle>
SELECT * From <tbl1>
???
Hvordan får jeg flere tabeller med?
Og hvad når indholdet fra de 100 tabeller ændres kan jeg lave en manuel opdatering af <samle> tabellen?

Points, you have given me them but more are most welcome :o)


I dont know EXACTLY what it is you are trying to do so its not easy to make a suggestion as to what you should do.

IF the data is in one table then it is possible to update one or more records with one UPDATE statement. This UPDATE statement can also update one or more fields in each record.


If the names of your tables were always the same then you could have the names in another table. hen you could make some code to read through this table and take each table INSERT it into <samle>.

Again, I DO NOT know what the tables contain so I dont know if this is the best solution. What happens IF some records get deleted from the Excel tables, must theyalæso get deleted from the <samle> also when you update in the Excel table, are thse to be deleted in the <samle>

So the solution depends on your requirements and I dont fully know them!

OK.... hmmm... helt fra starten...
Step 1)Det drejer sig om en time opgørelse som føres i et excel ark pr. person. ialt 100 personer. Løbende tilføjelse/ændring i data. Alle 100 excel ark ligner hinanden på kolonneoverskrifter, men indholdet af data er forskelligt.
Step 2) Jeg skal samle data til afrapportering. Jeg henter via ODBC data ind i databasen (en gang om måneden). og får derved 100 tabeller.
Step 3) Jeg skal have alt ind i en stor tabel eller query så jeg kan lave rapporter på forskellige parametre.

Hvad gør jeg?

So the data in the Excel arks is the current data? I would try and make 10 or so queries where you join 10 or so LINKED tables in each. This will be the easiest approach!

IF that doesnt work then you will have to make some VBA code to DELETE (empty) the <samle> table and then copy the data from each linked table into it.
Try to make 10 UNION queries frist and see how it goes!