Sum Query

[didacticone]

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

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.

[Seth Schrock]

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.

[didacticone]

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.

[Seth Schrock]

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

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.

Me.txtHeader = Me.OpenArgs

[didacticone]

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?

[Seth Schrock]

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.

[didacticone]

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?

[Seth Schrock]

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.

[didacticone]

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?

[Seth Schrock]

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?

[didacticone]

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.

[Seth Schrock]

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.

[didacticone]

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;

[Seth Schrock]

Try making your WHERE clause be

WHERE COMPLETEDINFIELD Between Forms!frmSearch!txtStart And Forms!frmSearch!txtEnd

[didacticone]

same thing...unfortu nately