run query based on other query in vba

[diegomarino]

Hi


i have to run a query that's based on onother query. I'm trying to get rid of all query and i want to put everything in my code.
tha two query are:

strSql = "SELECT Portafoglio.CandidatoID, Portafoglio.TrattativaID, Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([liquidità]) AS Totale, Switch([totale]<100000,""<100k"",[totale] Between 100000 And 299999,""100k-300k"",[totale] Between 300000 And 499999,""300k-500k"",[totale] Between 500000 And 699999,""500k-700k"",[totale] Between 700000 And 1000000,""700k-1mln"",[totale] Between 1000000 And 1999999,""1mln-2mln"",[totale] Between 2000000 And 4999999,""2mln-5mln"",[totale] Between 5000000 And 9999999,""5mln-10mln"",[totale]>=10000000,"">=10mln"") AS [Taglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) AS Ordinamento " & vbCrLf & _"FROM Portafoglio " & vbCrLf & _"WHERE (((Portafoglio.TrattativaID)=[Maschere]![home].[sottomascheraspostamento2]![sottomascheraspostamento1].[form]![CCSceltaTrattativa]));"

this is the first query in whitch i made some calculated fields.

then the second query (that's the one i use to populate the form) is

strSql = "SELECT [DB 1T ptf banca taglio clienti].TrattativaID, [DB 1T ptf banca taglio clienti].CandidatoID, [DB 1T ptf banca taglio clienti].[Taglio cliente], Count([DB 1T ptf banca taglio clienti].[Taglio cliente]) AS [ConteggioDiTaglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) AS Ordinamento " & vbCrLf & _"FROM [DB 1T ptf banca taglio clienti] " & vbCrLf & _"GROUP BY [DB 1T ptf banca taglio clienti].TrattativaID, [DB 1T ptf banca taglio clienti].CandidatoID, [DB 1T ptf banca taglio clienti].[Taglio cliente], Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9) " & vbCrLf & _"HAVING ((([DB 1T ptf banca taglio clienti].TrattativaID)=[Maschere]![home].[sottomascheraspostamento2]![sottomascheraspostamento1].[form]![CCSceltaTrattativa])) " & vbCrLf & _"ORDER BY Switch([taglio cliente]=""<100k"",1,[taglio cliente]=""100k-300k"",2,[taglio cliente]=""300k-500k"",3,[taglio cliente]=""500k-700k"",4,[taglio cliente]=""700k-1mln"",5,[taglio cliente]=""1mln-2mln"",6,[taglio cliente]=""2mln-5mln"",7,[taglio cliente]=""5mln-10mln"",8,[taglio cliente]="">=10mln"",9);"

now, how can i run the first query and then run the second based on the first query in my vba code?
ps i tried to use the instant sql formatter...don't know if in a correct way

[ranman256]

just run the query:
docmd.openquery "qsQuery2"

[diegomarino]

so i have to run the first query and then the second?
i know the open query command, but i want to run the statement only in vba, not having a query saved....if i understood you are saying to open a saved query

ps maybe a recordset is the answer

[Micron]

AFAIK you cannot query a recordset but you can filter it. However, if that's all you're doing you might as well provide all filter criteria at the beginning.
Or you could create an actual query in vba then query it then delete the query. That would not be my first choice.
Or you could try creating a temporary query def object and query that. By temporary I'm not saying you create a query by using code then delete it when you are done as just described. I am referring to a query def object that only exists in memory. You do this by not providing a name for the query def. See
https://docs.microsoft.com/en-us/off...def-method-dao and pay close attention to the part that describes a query def where the name parameter is an empty string (zls).
Or you could populate a temp table, query that and then delete the temp table records (better than constantly deleting and recreating the table).

[diegomarino]

thanks for the answer, this is what i've done precisely and works pretty good. thanks again

[diegomarino]

this thread is solved, but i have a question.
it is faster to use query def and make a query based on a querydef, or it is better to make a table that i empty and i refill to use as the base query on which i build my final query?
i mean both as safety and speed

[Micron]

Depending on the record count, you might not notice significant differences in speed. Take a look here and see if it helps with your situation.
http://www.mendipdatasystems.co.uk/s...s-6/4594478795

The general consensus seems to be that repeatedly creating and deleting a table is a recipe for corruption (in case you'd like to know), which is why I didn't suggest it as a solution. As for one being safer than the other, I don't think it matters. Both approaches, properly designed and befitting the operation at hand, should be equally safe. Sometimes a temp record table is the only way, say when having to deal with improperly normalized data.