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 completedinfield, 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.OpenReport 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 "completedinfield" 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 "completedinfield" is not in the report.

[Seth Schrock]

Does your report reference the completedinfield 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...unfortunately

[Seth Schrock]

Try

WHERE (Master.COMPLETEDINFIELD) Between Forms!frmSearch!txtStart And Forms!frmSearch!txtEnd 

[didacticone]

still no luck my friend

[Seth Schrock]

Okay, we are going to have to try something to get that field to be recognized. First remove your WHERE clause and then open the query in Design view (where you see the table(s) that the query is based on). Now, find the COMPLETEDINFIELD field in the table and double click on it. This should add it to the list. Uncheck the Show checkbox. Now in the criteria field, enter Between Forms!frmSearch!txtStart And Forms!frmSearch!txtEnd . See if that does it.

[didacticone]

still coming up and now the report isnt grouped by job type its grouped by the date

[Seth Schrock]

It is grouping by the date now because it has been added to the GROUP BY clause. One more thing that I have thought of. In the query properties, there is a property called parameters. Make sure that there isn't anything in there.

If that doesn't work, I'm stumped as to why that popup keeps appearing. You would probably have to then make your query not an aggregate query and then do your summing and filtering in the report.

[didacticone]

I am glad it is not just me who is baffled by this... if i was to do it in the report can you give me some help on that? i have never done and filtering in a report itself. thank you for your help

[Seth Schrock]

You would just make the control source of each control in the report have the sum function in it. Like this

=Sum(IIf([master.REPAIR],1,0))

You would then set your report's filter property using VBA, probably in the On_Open event.

Me.Filter = Me.OpenArgs

You just need to setup your filter string when you open the report that you can pass it the correct value which is the same as your WHERE clause without the word WHERE.

[didacticone]

i changed the control sources for all the fields. just confused as to how to put the where clause.

[Seth Schrock]

You would remove the WHERE clause from the query.

[didacticone]

my query is now as follows:

SELECT Master.JOBTYPE, Master.COMPLETEDINFIELD, Master.REPAIR, Master.REPLACE, Master.REMOVE, Master.INSTALL, Master.MAINTENANCE, Master.TEMPDISCONNECT
FROM Master;
 

[Seth Schrock]

That is correct. When you run it, do you get any popups?

[didacticone]

no pop ups, but the job type is not grouped in the report, and i am not sure how to query the dates i want.

[paulwilliam024]

There are some intrusting and i like this..

[Seth Schrock]

You can add groupings in the report design view. In Access 2010, it is in the Design tab, Grouping & Totals section (toward the left side).

To get the dates that you want I'm now thinking that you could use the WhereCondition argument when opening the report instead of the OpenArgs.

DoCmd.OpenReport ReportName:="yourReport", WhereCondition:="COMPLETEDINFIELD BETWEEN #" & Me.txtStart & "# And #" & Me.txtEnd & "#"

[didacticone]

Im having all sorts of problems here, i was able to group by job type, but the summation is all wrong. it is just adding and showing the same number for every job type. so for instance if there are 3 total repair jobs, 1 for 3 different categories, it is showing 3 in every category? again im sorry ive never worked with reports like this before.

[didacticone]

i actually fixed the grouping issue. now it is displaying the numbers correctly. any idea on the best way to handle inputting specific dates when i run the report?

[Seth Schrock]

What do you mean by inputting? Are you adding records?

[didacticone]

sorry, meant querying. it was actually just an oversight on my part to refer it to the form for entering dates that you had advised me to make earlier. seems to be working very well now. i think i just have one last question. if there is no value entered for a job type, so for instance valves, has 0 of (repair, replace, etc.) it is not displaying on the report. is there a way to have this display, just showing "0"'s. thank you so much for your help.

[Seth Schrock]

As this is a separate question and one that can't be answered very quickly, you will need to post this in a new thread.

[didacticone]

ok. thank you for your help. i truly appreciate it.