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!
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!