Sum Query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • didacticone
    Contributor
    • Oct 2008
    • 266

    Sum Query

    Hi all,

    My goal is to run a report for every month. The information i want in the report is the sum of a field. Actually 6 different fields. I have a query with the following code

    Code:
    SELECT Sum(IIf([master.REPAIR],1,0)) AS SumOfREPAIR, Sum(IIf([master.REPLACE],1,0)) AS SumOfREPLACE, Sum(IIf([master.remove],1,0)) AS SumOfREMOVE, Sum(IIf([master.install],1,0)) AS SumOfINSTALL, Sum(IIf([master.maintenance],1,0)) AS SumOfMAINTENANCE, Sum(IIf([master.TEMPDISCONNECT],1,0)) AS SumOfTEMPDISCONNECT, Master.JOBTYPE
    FROM Master
    WHERE (((Master.COMPLETEDINFIELD) Is Not Null And (Master.COMPLETEDINFIELD) Like [Please Enter Month ex- 9/*/*]))
    GROUP BY Master.JOBTYPE;
    This works great when i run the query, but i need to report on these numbers using a specific date range. Since the completedinfiel d, field is using a where clause it will not show and therefore cannot use it in my report. Is there a better way to do this? Thanks for the help.

    I have attached a sample of my report.
    Attached Files
  • Seth Schrock
    Recognized Expert Specialist
    • Dec 2010
    • 2965

    #2
    What I would do is create a form that has two textboxes in which you can enter a starting and ending date. Then have your query reference the form for your criteria. If you need the date range on your report, you can pass the values to the report using the report's OpenArgs property or just reference the form in your report's OnLoad event and assign the values to a control in your header or something like that.

    Comment

    • didacticone
      Contributor
      • Oct 2008
      • 266

      #3
      I think in concept i understand what your saying. Should the textboxes be linked to fields in the underlying table? And by doing this it would filter my report based on that date range? Could you possibly give me an example? I appreciate your help.

      Comment

      • Seth Schrock
        Recognized Expert Specialist
        • Dec 2010
        • 2965

        #4
        Neither the form nor the two textboxes would be bound. For ease of illustration, I'll name my form frmSearch and my two textboxes txtStart and txtEnd. Your query would then be
        Code:
        SELECT Sum(IIf([master.REPAIR],1,0)) AS SumOfREPAIR
        , Sum(IIf([master.REPLACE],1,0)) AS SumOfREPLACE
        , Sum(IIf([master.remove],1,0)) AS SumOfREMOVE
        , Sum(IIf([master.install],1,0)) AS SumOfINSTALL
        , Sum(IIf([master.maintenance],1,0)) AS SumOfMAINTENANCE
        , Sum(IIf([master.TEMPDISCONNECT],1,0)) AS SumOfTEMPDISCONNECT
        , Master.JOBTYPE
        FROM Master
        WHERE COMPLETEDINFIELD Is Not Null 
        And COMPLETEDINFIELD Between Forms!frmSearch!txtStart and Forms!frmSearch!txtEnd
        GROUP BY Master.JOBTYPE;
        As far as getting the date range into the report, I would recommend the OpenArgs method. Just put a button on your form (frmSearch as I named it) that runs the report using the DoCmd.OpenRepor t method and add the text you want in the OpenArgs argument. Then, in your report's On_Load event, you can assign this value to a textbox or a label's caption.
        Code:
        Me.txtHeader = Me.OpenArgs

        Comment

        • didacticone
          Contributor
          • Oct 2008
          • 266

          #5
          I have created the form and the button to open the report as you said. When i click the button the report displays a box the says "completedinfie ld" with a text box under it and i can hit ok or cancel. It does seem as though it is querying the date range properly. Is there a reason this box is coming up. Have i dont something wrong?

          Comment

          • Seth Schrock
            Recognized Expert Specialist
            • Dec 2010
            • 2965

            #6
            Make sure that the field name is typed correctly. Basically, when a query doesn't recognise a field, it asks for its value if it hasn't already been set in the query.

            Comment

            • didacticone
              Contributor
              • Oct 2008
              • 266

              #7
              my understanding is that when using a where clause it cannot be displayed in the report? is this correct and why this is possibly happening?

              Comment

              • Seth Schrock
                Recognized Expert Specialist
                • Dec 2010
                • 2965

                #8
                The fields do not display. They are only criteria. However, if the same field is included in the SELECT portion of the query, then it does display. However, since you are using an aggregate query (Sum function), you would also have to include it in the GROUP BY clause which would group by the date. I think that you could group it based on the month, but this still doesn't allow you filter your results inside the report.

                Comment

                • didacticone
                  Contributor
                  • Oct 2008
                  • 266

                  #9
                  That was my initial conundrum. I want that field to be included but as you said i would have to include it in the group by which is something i do not want to do. any ideas?

                  Comment

                  • Seth Schrock
                    Recognized Expert Specialist
                    • Dec 2010
                    • 2965

                    #10
                    That is why I think that it would be easier to filter your results in the WHERE clause of the query. The other option would be to have the controls in your report do the summing of the data. Personally, I don't prefer this method as it seems to take longer to do that sort of thing in a report than in a query. I like to have my data just as I want it when it gets to the report instead of working with it after.

                    Can you post what you tried when you got the popup in post #5?

                    Comment

                    • didacticone
                      Contributor
                      • Oct 2008
                      • 266

                      #11
                      I did exactly what you said in post 4. I took your code and created a new query based on that. then created the new form with the text boxes. And everything is working. the dates seem to be filtering fine. i did the open arg method to get my text into the report. but that box just pops up because "completedinfie ld" is not in the report.

                      Comment

                      • Seth Schrock
                        Recognized Expert Specialist
                        • Dec 2010
                        • 2965

                        #12
                        Does your report reference the completedinfiel d field? Can you post your SQL code? It is more likely that the popup is from the query and not the report. However, since the report is based on the query, running the report will prompt the query to show the popup even though you didn't specifically open the query.

                        Comment

                        • didacticone
                          Contributor
                          • Oct 2008
                          • 266

                          #13
                          Code:
                          SELECT Sum(IIf([master.REPAIR],1,0)) AS SumOfREPAIR, Sum(IIf([master.REPLACE],1,0)) AS SumOfREPLACE, Sum(IIf([master.remove],1,0)) AS SumOfREMOVE, Sum(IIf([master.install],1,0)) AS SumOfINSTALL, Sum(IIf([master.maintenance],1,0)) AS SumOfMAINTENANCE, Sum(IIf([master.TEMPDISCONNECT],1,0)) AS SumOfTEMPDISCONNECT, Master.JOBTYPE
                          FROM Master
                          WHERE COMPLETEDINFIELD Is Not Null And COMPLETEDINFIELD Between Forms!frmSearch!txtStart And Forms!frmSearch!txtEnd
                          GROUP BY Master.JOBTYPE;

                          Comment

                          • Seth Schrock
                            Recognized Expert Specialist
                            • Dec 2010
                            • 2965

                            #14
                            Try making your WHERE clause be
                            Code:
                            WHERE COMPLETEDINFIELD Between Forms!frmSearch!txtStart And Forms!frmSearch!txtEnd

                            Comment

                            • didacticone
                              Contributor
                              • Oct 2008
                              • 266

                              #15
                              same thing...unfortu nately

                              Comment

                              Working...