5 Replies Latest reply: Dec 29, 2011 5:43 PM by Karl Pover RSS

    Summary field false total

      Hello Everyone,

       

      I'm new to QV and have some questions, becouse I found a problem within my report.

      I'm trying to calculate SUM on field Punktacja_Punkty from Table Punktacja. This table has the same ID field IDNagroda as the field in Table Kotly in which I gather the data (some serial numbers, users name and productID).

       

      Table KotlyTable PunktacjaTable Users

      User_ID

      IDNagrodyUser_ID
      IDNagrodyPunktacja_NagrUsers_UserName

      Kotly_ID

      Punktacja_EanUsers_Passw
      Kotly_KodPunktacja_Punkty
      Kotly_DataZa

      Kotly_Rok

      Kotly_Dystr

       

      The data from red fields and green are relational. Everything works during the data lookup ok but I wonder why SUM(Punktacja_Punkty) expression is not creating total of the filtered content. I.E. the sum for data below is only 26 instead of 226. Infact count(Kotly_ID) = 42 as it should.

       

      Kotly_IDKotly_KodPunktacja_Punkty  Users_UserName
      355618  username
      355528  username
      355738  username
      355248  username
      355358  username
      355468  username
      355978  username
      355888  username
      355093  username
      3547103  username
      3549113  username
      3548123  username
      3551133  username
      3452143  username
      3448153  username
      3447163  username
      3457173  username
      3456183  username
      3453193  username
      3451203  username
      3449213  username
      3455223  username
      3454233  username
      3450243  username
      3458253  username
      3437263  username
      3436273  username
      3439283  username
      3441293  username
      3443303  username
      3445313  username
      3442323  username
      3440333  username
      3435343  username
      3438353  username
      3444363  username
      3446373  username
      35643815  username
      35623915  username
      35604015  username
      35614115  username
      35634215  username

       

       

       

      Can you gice me a feedback how to summarize corectly and how to add the sum field i.e in TableBox Object or MultiBox Obj?

       

      Best regards and sorry if it's to simple...

       

      Marcin

        • Summary field false total
          Karl Pover

          Marcin,

           

          Can you post your QV or at least an image of what you're seeing?

           

          Karl

            • Re: Summary field false total

              Hi Karl,

               

              thanks gor replay. As attachements you will find 3 files - textBox with properities, same as TableBox and tables schema. Below I paste the script tables.

              Best regards and thx for answering.

               

               

              SQL SELECT Id as Kotly_ID,

                  kod as Kotly_Kod,

                  typkotla as IDNagrody,

                  dataZakupu as Kotly_DataZakupu,

                  rok as Kotly_Rok,

                  DataWpisu as Kotly_DataWpisu,

                  dystrybutor as Kotly_Dystrybutor,

                  userId as User_ID

              FROM db168409.Kotly;

               

              SQL SELECT ID as IDNagroda,

                  Nazwa as IDNagrody,

                  Opis as Nagrody_Opis,

                  Foto as Nagrody_Foto,

                  Punkty as Nagrody_Punkty

              FROM db168409.Nagrody;

               

              SQL SELECT ID as NagrodyZreal_ID,

                  IDNagrody as IDNagrody,

                  Rozmiar as NagrodyZreal_Rozmiar,

                  Punkty as NagrodyZreal_Punkty,

                  IDUzytkownik as NagrodyZreal_IDUzytk,

                  Data as NagrodyZreal_Data

              FROM db168409.NagrodyZreal;

               

              SQL SELECT ID as IDNagrody,

                  NazwaKotla as Punktacja_NazwaKotla,

                  KodEanKotla as Punktacja_EanKotla,

                  Punkty as Punktacja_Punkty

              FROM db168409.Punktacja;

               

              SQL SELECT ID as User_ID,

                  usern as Users_UserName,

                  passwd as Users_Passwd,

                  role as Users_Role

              FROM db168409.Users;

               

              SQL SELECT uemail as User_ID,

                  imie as UsersFull_Imie,

                  nazwisko as UsersFull_Nazwisko,

                  sapno as UsersFull_SapNo,

                  company as UsersFull_Company,

                  kodpoczt as UsersFull_KodPoczt,

                  miasto as UsersFull_Miasto,

                  adres as UsersFull_Adres,

                  telefon as UsersFull_Telefon

              FROM db168409.UsersFull;

              TxtBox-Punkty.JPGTableBox-Punkty.JPGConnections.JPG

                • Summary field false total
                  Karl Pover

                  Hi Marcin,

                   

                  The problem is that the table Punktacja is a reference table like a price list or grade table and doing a sum just on that table will sum up the amounts without respecting how many times it is referred to in the the table Kotly.  You should either do a join between the Kotly and Punktacja table in the script or add a quantity column in the Kotly table to get the sum you are looking for.

                   

                  Out of curiosity Kotly is drum and Punktacja is a grade scale?  I'm just wondering how good the polish-english translators are.

                   

                  Karl

                    • Re: Summary field false total

                      Hi Karl,

                       

                      yes the Punktacja is a grade scale, but Kotly in this context are boilers (gas boilers I was thinking of).

                       

                      afterall I wanted to summarize by the points of Kotly for each of users and also get the participation (count) the boilers by type.

                       

                      Infact I think I'll try to create one more dataset for this purpose. Do you think its better to join the tables or concatenate them??

                       

                      BR

                      Marcin