put all the queries into one?

[xq]

i've created a bunch of queries which will update all the tables in certain way(no result return), but there r just too many, even myself can't remember which one is for which, is there a way to put all the queries in order into one query, then when i open this query, it will run query each by each.(will procedure statement do any good?)
thank u for any help!

 

[mrf1xa]

The easiest way to do this is to create a macro, and list all the queries to be run in that macro. then create a button and point the onclick event of the button to your macro.

You can also use the SetWarnings statement at the start of the macro to tuen of all the warning messages while the queries run, if this is what you want- but remember to trun them back on again as the last line of the macro!

Alternatively you can do all this in VB code behind the onclick event of the button.

Feel free to shout if any of this doesn't make sense.

Nigel Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[Petemush]

It'd probably be safer to do it in VB since if you get an error during the macro and you've turned the warnings off, the macro will exit with the warnings still turned off.

Cheers,

Pete

 

[Aristarco]

I had a simmilar problem but I used the virtual queries (as I call 'em, no technical reference to use that name but my common [?] sense). The problem is I haven't tested them with too many queries. I grouped them with the AS SQL instruction. Look at this code:


SELECT MUNICIPAL.Cve_Mpio, FirstQuery.Municipio, FirstQuery.Obras, SecondQuery.Inversión
FROM ([SELECT fonden_porMunicipio2.Municipio, Sum(fonden_porMunicipio2.Obras) AS Obras
FROM fonden_porMunicipio2
GROUP BY fonden_porMunicipio2.Municipio, fonden_porMunicipio2.Tipo
HAVING ((fonden_porMunicipio2.Tipo) Like "Obra&quot]. AS FirstQuery LEFT JOIN [SELECT fonden_porMunicipio2.Municipio, Sum(fonden_porMunicipio2.Inversión) AS Inversión
FROM fonden_porMunicipio2
GROUP BY fonden_porMunicipio2.Municipio]. AS SecondQuery ON FirstQuery.Municipio = SecondQuery.Municipio) LEFT JOIN MUNICIPAL ON FirstQuery.Municipio = MUNICIPAL.MUNICIPIO;


If you use the Design View to see the graphic representation of this code, you'll see FirstQuery and SecondQuery as tables related to the query containing them.

Before I get to this solution, I used to build temporary QueryDefs, then a big QueryDef using the temporary ones. So I can recommend this method too. Use VBA if you want to debug or trap the error in the making. Both methods can be applied using VBA, so try 'em.

No liability involved in the loss of your data while trying these methods... Just kidding man!
To boldly code, where no programmer has compiled before!

 

[xq]

Thanks, guys. my queries in the query just look like a big mess, i'm just wondering if there is a better way to tidy it up. and i want to ask Aristarco, my queries just updating tables, some queries wonn't return anything(UPDATE, INSERT, SELECT, I've used), will ur code work on my one as well?

 

[Aristarco]

To the very best of my knowledge, yes. This coding technique would work hassle free with your update queries. Just start testing to see. Or send a copy of your code to see if I can help. To boldly code, where no programmer has compiled before!