Mar 13 2022 03:10 PM
Mar 13 2022 03:10 PM
Hi, I am tasked with making a query which involves 5 salespersons selling items during the third quarter. I am very close to getting the data how I want it, however, all I need to do is have all of the sales for one individual in the range of quarter 3 be listed in one cell rather than have the sum of sales for each date be listed in different cells.
For example, using the photos below I want all of Jacobson's sales from 7/1/19 through 9/30/19 to be summed in one cell, rather than having the sum of his sales for each date between 7/1/19 and 9/30/19.
If anyone can help me solve this issue I would greatly appreciate it, Thank you!
Mar 13 2022 03:41 PM - edited Mar 13 2022 03:43 PM
Aggregate queries group results on each non-aggregated field in the query. So, if you include BOTH employee and Date in that query, it aggregates by employee then by date. That's the way such queries work. So, how do you get the aggregate for the employee only? SELECT only the employee field for the non-aggregate fields.
(BTW, before I forget, a bit of terminology that you should adopt for work with Access is that these are not cells. Only Excel has cells. Field are what Access uses. It's not just semantics, although I won't divert the discussion with a lengthy discourse on that topic, just need to point it out so we minimize confusion.)
Your query, therefore needs to return two fields: The employee and the Sum of Sales.
But you ask, how do I limit that to the appropriate date range without including the Dates?
Change the "Total" field for the Date from "Group By" to "Where". That restricts the two output fields to the employee and sum of sales, but only where the dates fall in the desired range.
And while we're at it, let's address the use of the name "Date" for the field name. Bad juju follows when one uses generic words for field names. It confuses Access. These are "SalesDates", and that isn't so confusing for Access or for your users.
Mar 13 2022 05:02 PM
Thank you so much for your help, your suggestions helped me solve the problem. Also thank you for informing me about the terms used in Access as well; I'm a student in college who is just learning access so excuse all of the wrong terminology!
Once again THANK YOU!@George Hepworth