Forum Discussion

Zergeniel's avatar
Zergeniel
Copper Contributor
Oct 14, 2020

Endless query

Hi

  i populate a dataset (VBA) to insert data in a local table for statistics purpose. To detail the problem, i make dynamically a request to access different group by or kind of data according to informations in a form. All was working fine with multiple query on the stats table until i generate this simple query =>

SELECT sum(nb_connex) AS Cumul FROM Stats WHERE format([jour],"yyyy-mm-jj") < "2020-01-01" and [code_dossier] in (select distinct code_dossier from stats) GROUP BY [jour], [code_dossier];

 

The  query never ends in the vba, even in query mode.

The stats table is a linked one. If i use other parameters in the "in" clause or If i try this query in MySQL Workbench,the result is immediate.

endless query

3 Replies

Sort By
  • Woldman's avatar
    Woldman
    Iron Contributor
    Oct 14, 2020

    Zergeniel 

    Maybe I'm missing something, but why checking for code_dossier in the stats-table from the subquery while code_dossier itself already comes from the stats table (in the outer query)?

     

     

    • Zergeniel's avatar
      Zergeniel
      Copper Contributor
      Oct 15, 2020

      WoldmanBecause it's a dynamically constructed query and in case of "all" selected the easier way to translate it it's by an "in" clause. Some case => (code_dossier in ('EC', 'EI', etc...) or all cases => in (select ...). The "code_dossier" are selected in a multiple list... The distinct return about 250 values and the full stats table have about 210 000 rows).

      I have a case condition with 16 différents values (in my vba code) and i create a dynamic query that work fine until this case. I have to double the number of cases (for cumulative stats) so i used the already working method to add the new cases...

       

      Made from multi list choice =>
      so maybe : strDossier = "Select code_dossier from stats"
      or : strDossier = "'EI','EC','TRUC'"
      Different tests do the other parts of the query
      strReqDeb = "SELECT Format([Jour],'YYYY-MM') as Grn"
      strReq = ", sum([nb_collab]) as Som FROM [Stats] "
      strReq = strReq & "WHERE [jour] >= [Formulaires]![F_Connexions]![txtDtDebut] and [jour] <= [Formulaires]![F_Connexions]![txtDtFin] and [nb_collab]<>0"
      strReq = strReq & " and [code_dossier] in (" & strDossier & ")"
      strReqFin = " group by Format([Jour],'YYYY-MM') ORDER BY 1;"

      And then i make the final query by jooining all parts
      Me.graphic.RowSource = strReqDeb & strReq & strReqFin

       

       

      And this sample query is perfectly working and immediate

       

      • Zergeniel's avatar
        Zergeniel
        Copper Contributor
        Oct 15, 2020
        I had to make like that because the rowsource size of a chart is limited to 2048 and if i make the "in clause" with all code_dossier, it's over (about 2500 chars)

Resources