Access query

The problem is that you are grouping by the PaidDate.  You will end up with a separate row for each paidDate.  If you want to group by Month:

Select Agent, Format(PaidDate("yyyy/mm") as PaidMonth, Sum(Price)
From yourtable
Group By Agent, Format(PaidDate("yyyy"/mm")

You can also write it like this

SELECT Agent
	,Month(PaidDate) AS PayMonth
	,Year(PaidDate) AS PayYear
	,Sum(Price)
FROM YOURTABLE
GROUP BY Agent
	,Month(PaidDate)
	,Year(PaidDate)

Select allOpen in new window

not working.
I have a query builder in design view:
three items
agent           price             paid date
group by     sum              group by

I can add a fourth, called month: with your queries added to this field and I get a parenthesis must be added to the sub query.
what am I doing wrong. should the query be added to the beginning of the paid date field, like:
paid date:SELECT Agent
      ,Month(PaidDate) AS PayMonth
      ,Year(PaidDate) AS PayYear
      ,Sum(Price)
FROM YOURTABLE
GROUP BY Agent
      ,Month(PaidDate)
      ,Year(PaidDate)

I guess there is a missing piece here...can you share a small sample of your data....?

sure:
excel file attached. I pulled the excel file into the dataase to create a linked table to the excel sheet.
test.xlsx

Take a look at my attachment...the problem was with the field name...it was Paid Date not PaidDate...so in order to query it (it has a space) we need to enclose it in brackers...[Paid Date]

SELECT Sheet1.Agent, Sum(Sheet1.Price) AS SumOfPrice, Month([Paid Date]) AS PayMonth, Year([Paid Date]) AS PayYear
FROM Sheet1
GROUP BY Sheet1.Agent, Month([Paid Date]), Year([Paid Date]);

Select allOpen in new window

Database9.accdb

Keep getting a message that sub queries need a parenthesis. Can you lay out the design query for me. from
2018 Sales
choosing
box 1   agent    group by
Box 2 is price   sum
Box 3 is Paid Date    group by

where would I add the query you wrote? I added it in sql view and choose run. syntax error of the paranthesis. It aso said something about data after the select statement. At any rate, it didn't like it....
I really am frustrated.

Both John and I gave you sample queries.  If you are having a problem with your version, we need to see what you have now.

PS, if you really have tables for each year, you should reconsider your design.  Spreadsheet logic separates data this way but NOT relational databases.  All the sales data should be in the same table.   Use criteria to extract a specific year if that is all you want.  Having separate tables simply makes much more work for yourself.

HI PatHartman, I really don't even dare put all the data in one table as I cannot even separate out a month, much less a year and a month!
How can I show you what I have? I can upload a screenshot with the sql query and error statements, I can upload sample data ,, which I have, I can give you whatever you need to do this. Really, all I want to do is just extract the month for reporting purposes. should I just try to do this in a report? really this has got to be simpler than this. What is wrong with my logic, in the design of the query, just three fields, and I want to extract a month 1, month 2, month 3 month 4, etc, at the end of the month or beginning of the next month for the previous month. That's it.....not a big deal. there has to be a simple line of code that just returns the value of the current month!
A parameter query? what...….oh gosh...….a whole database and this is what is sticking me!!
The reason for the using of separate tables is everything other than 2018 is historical. If I need to extract data or add data or create reports I can, but don't need to add all that data into one spreadsheet. It makes sense to me to separate out data the owners will not be using. But having it all in the tables in Access is a good place to store it and also gives me quick access. My only issue is the relationship between each year if I need to create a query with all historical data. I am unsure how I would join them. Since each table has same fields in it, I would find it hard to create that join.
But right now, I am horribly rustrated that this one little thing, just creating a query for a particular month, is so hard to do.

The database i send you works ....just fine ..what is the issue ?
P.S. i obfuscated the names...but this is the result from the query

Okay I got it to run, now I only want month 4 or month 5 or month 6
not all the months

I cannot get it to parse out and return the value of only one month, the last month, if I am running it on the first day of the month, or if I am running it on the last nday of the month, then the current month.
I cannot control when the admin will run the queries

and in this query there are multiple instances of one agent in the month. so I only want one line per agent, total sum of price and the current month or the last month, whenw=ever the admin runs this.
so I guess I wasn't making myself clear to begin with. I am sorry.

so it's amonthly query.
on instance of an agent, even though multiple rows, one total sum of price for that agent in the current or last month, depending on when the admin runs the report.

this was my original text "I have a query where I am trying to get one line for each sales agent for a particular month, with total sales for that agent for that month."

To show us your SQL, open the querydef and switch to SQL view.  Copy and paste the text into the message box here.  DO NOT POST A PICTURE.  WE CANNOT DO ANYTHING WITH A PICTURE.

e.g for April (month: 4)...from the database i send you

SELECT Sheet1.Agent, Sum(Sheet1.Price) AS SumOfPrice, Month([Paid Date]) AS PayMonth, Year([Paid Date]) AS PayYear
FROM Sheet1
GROUP BY Sheet1.Agent, Month([Paid Date]), Year([Paid Date])
HAVING (((Month([Paid Date]))=4) AND ((Year([Paid Date]))=2018));

Select allOpen in new window

I'm not sure what the point of guessing what the problem is.

The criteria for this query should be in a WHERE clause rather than a HAVING.  Where is processed prior to the data being aggregated so it minimizes the data that is initially selected and HAVING is processed after the aggregation.  So HAVING is only appropriate on data that is actually aggregated.  For example, selecting sales people who sold more than $1,000 in goods for a given month.  That query would use a WHERE to pick only the month of interest and then a HAVING to find the sales people with the desired sales value.  

With smaller recordsets, you won't see much of a difference in performance but you will when the recordset is large.

@Pat this is what the QDE produced...if we were after to optimize the query i am well aware of Where is placed and Having...but right now we just have to produce a working query

Just guessing at what is wrong with connie's query is not productive.  

If you know what good practice is (and I assume you do)  and use it when you build queries, you don't have to revisit them later to fix them.  I use the QBE almost exclusively because it saves a lot of typing but it does have quirks and this is one of them.  In a Totals query, unless you are specific, it always assumes you want a HAVING.  You just have to choose the correct option to get a WHERE.  At least this quirk is controllable.  If only I could get the QBE to stop inserting unnecessary parentheses and rewriting my SQL strings in its own image, I'd be a happy camper.

lets say its a good opportunity to try my psychic powers...;)

Hi guys,
here is my query, with the AND clause, it has syntax errors. Doesn't like the anything after HAVING. the syntax of (((Month([Paid Date])) = 04 AND ((Year([Paid Date]))=2018)); is causing the run to return syntax issues.

SELECT [2018 Sales].Agent, Sum([2018 Sales].Price) AS SumOfPrice, Month([Paid Date]) AS PayMonth, Year([Paid Date]) AS PayYear, [2018 Sales].[Paid Date]
FROM [2018 Sales]
GROUP BY [2018 Sales].Agent, Month([Paid Date]), Year([Paid Date]), [2018 Sales].[Paid Date]
HAVING (((Month([Paid Date])) = 04 AND ((Year([Paid Date]))=2018));

Update:   added a parenthsis at the end and it runs. Thank you both!!!!!  So then do I open another question or can I ask another here?  To negate the fact that I go into the query and change the month for each monthly run, is there a variable I can use in my query that will create the data for the current month if run on the 30 or 31 or 28th, and if run the on the 1, 2, 3 it would be previous month? If I should ask this as a separate question, just let me know.

This worked perfectly, so thank you both so very, very much!!! Thank you so very much!!!! I just wish I could create a query using the design view and not just sql, I would be a happy camper. My fear is that the more code we write, when the upgrades come out, it's a total rewrite in some cases. So let me know on the new question and if you'd like me to do that, I will close this and open another.

Thank you both, you have answered my questions!!!! it works and I am soooo very happy!!!!

If you have your answer, please close the question.

Pat and JOhns solutions, worked in tandem and created the data I needed.

Glad it worked for you.