Building Group By Query Utlizing Form Fields as Crtieria

state90

Registered User.
Local time
Today, 11:59
Joined
Sep 16, 2014
Messages
65
I have set this issue aside for a few weeks now in order to protect my sanity. Out of the gate, I probably require some patience from those willing to help me through this issue!

Using sample fields as a starter to explain the problem and avoid getting bogged down by the details, I can apply the answer (hopefully) to my actual data. From my previous attempts at understanding this I believe this is more of a help me with the SQL to make this happen than anything else.

Let's say I have five fields in my source data. Three I want to use only as criteria selections on a form, one filed I want the query results to Group By and one field is an Amount field that I want summed.

Here are the fields:
* Region (Field in data I only want to use as Criteria on form)
* Country (Field in data I only want to use as Criteria on form)
* Month (Field in data I only want to use as Criteria on form)
* Category (Field I want the query to group by and show totals for. There are six possible options)
* Amount (Field I want summed, by category for the criteria selected)

I have no idea why I am havign a hard time with this...but I am. As an example of the functionality I am looking for and the results I am actually getting if Region is not selected the results summed should be total global results or if Europe is selected then the totals will be only for those line items in the data where the Region = "Europe". The query results should only show totals by Category (six possible options) so six lines in the results. What I am getting, however, depending on the criteria selected is 1,800 rows more or less. I cannot figure out how to apply the criteria while ignoring the field in the grouped by. I have tired to uncheck the field so it doesn't show (I think the SQL for that changes to LIST BY). If I need to post the actual data I can but I am hoping a simplified approach works better because the data is more than five fields and pretty bulky.

Thanks for any and all help!
 
Sounds like you are GROUPING by fields you don't want to GROUP BY. So....don't GROUP BY those fields. Only GROUP BY Category.
 
Thanks plog. Here is the part where I am hoping for patience! I do not know SQL. I know how to view the SQL behind the query but not how to use SQL from scratch if that makes sense. So, that being said, when I create a query I do it the "normal" way. I select Create > Query Design > select the table(s) > Link the tables > select the fields I need to include in the query > Under Design, select Totals so I can Group By or Sum. In doing it this way, I would select the fields listed above. Under Region, Country and Month fields I have tried unclicking the "Show" checkbox, I have also tried using Where. For Category I use Group By and for Amount I Sum. I fully expected this to lead me to the results I wanted (showing and grouping by only the Category field and a sum of Amount for each of those categories. Is what you are suggesting something I need to do in SQL coding?
 
You can do this in Design view. Under each field, in the Total drop down instead of Group By, you want it to read Where.
 
And then have the form feed the Where clause correct? That is what I did. Let me try again from scratch and see what happens.
 
plog,

Now we get to some of the actual details. Below is the VBA behind the report that is fed by the query. Do you see any issues in this VBA that could be keeping me from grouping as preferred?

Private Sub Command126_Click()


Dim vFields
Dim vValues
Dim where As String
Dim i As Integer

vFields = Split("Function Country_Code Cost_Center Geography Region Budget_Region Legal_Entity")
vValues = Array(Me.CboFuncRollup, Me.Text120, Me.Text124, Me.CboGeoGroup, Me.CboRegion, Me.CboBudRegion, Me.CboLegalEntity)

For i = 0 To UBound(vFields)
If vValues(i) <> "All" Then
where = where & "AND " & vFields(i) & "='" & vValues(i) & " "
End If
Next
If Len(where) > 0 Then where = Mid(where, 5)

DoCmd.OpenReport " EXP_By_Cat_Summ", acViewReport, where
Forms("Frm_PARAMETER_Metrics").Visible = False

End Sub
 
No, but the code has nothing to do with grouping. Its all about filtering and it won't work if the report is based on the query we have been discussing.

That query only has 2 fields: the field that was GROUP BY and then the SUM field. It doesn't have any of the fields in vFields available to it. When you try to apply a filter to one of those fields it will have no idea what you are referencing.

You should rework your report to be based on the underlying table, then apply the GROUPING in the report, not the query. That way it will have the vFields available to it and allow you to filter it properly.
 
So, if I am understanding this correctly, don't have the report pull from a query but rather the source data table directly instead?
 
Correct. Then add the appropriate Group, don't put anything in the Detail section, instead put it in the Group Header/Footer.
 
Okay....let me try it. Thanks plog. Much appreciated.
 
Okay.....Thank you for the solution to the reporting Group By. That worked perfectly! Now I need to figure out how to tie it all together. I don't know the proper etiquette as it relates to this but I am assuming it requires a new thread. Here is where it stands....

The report is grouping by the six categories. I used a DSum based on form input to sum the Amount field properly (I didn't go into this part in my initial question because I was focused on the report group by but I actually have four summed amount fields on the report based on the form input of current month. The DSum checks for the month and sums Amount if the Month field is either current month, prior month, two months ago or three months ago. The problem is that for each category, it is giving the same summed total. For example, Category Travel = $11,445,774 as does Professionaol Services, Personnel, etc..... While I have the DSum summing properly for the respective months, it is not summing to include the category as part of the equation (i.e., Sum July-2015 for Travel as one grouping total and Personnel for Jul-2015, etc). Should I call this thread solved...which you did and I thank you again....and start a new thread?
 
Yes, I'd start a new thread. Mostly because you've lost me. In that new thread, show what you have and what you hope to end up with. Demonstrate your issue with data.
 
Ha! I am good like that! I will start a new thread. Thank you again for resolving the Group By portion. Now I just need it to factor in all the criteria.
 

Users who are viewing this thread

Back
Top Bottom