How to create a query that pulls up monthly sales, prev monthly sales, y-t-d sales, etc

Have you thought about using User Defined Parameters in your query? You could put this as criteria in your query [RepName?] and a message box will open a input box. The rep name would be entered into the input box and the query would use it as the criteria to filter the results of the report. You can have multiple user defined parameters in a query. The risk is if someone enters the name incorrectly the query would return no results.

That is just one idea of many on how to tackle this problem. I really don't like the reporting feature of Access and therefore build reports in Excel using Access as a external data connection.

Not sure I understand what you're saying. possibly bc I wasn't clear (?).  But I already have parameters [Enter Last Name] that filters (?) for the each rep's weekly report.

Try to explain with an example.
List input records, and required output.

Gary Smith 1/23/2017 $450, 1/24/2017 $600, 2/3 $400, $750, 2/4 $825; goal amt = $500,000
Amanda Beard 1/4/2017 $325, 1/6/2017 $900, 1/27/2017 $750, 2/1 $600, 2/6 $815; goal amt = $750,000

I want to make query pull up totals by month, prev month, % of goal, etc.

My weekly report (by rep) has parameters [Enter Last Name] and {Enter Week Ending Date].

Do you need anything else?

oops,
I want to make query pull up totals by month, prev month, % of goal, etc. based on the parameter in the Report of [Enter Week Ending Date].

I figured out how to do week ending dates that end on Friday by using: Week Ending Date: [SDate]+(7-Weekday([SDate],7))

I've got a qry MonthySalesbyRep and PrevMonthySales byRep that brings up the correct records, I just don't know how to bring up the respective totals in a qry.

Am I making this harder to understand?

Create a query that returns the results you need for the subreport.  Create a report based on that query.  Put the "sub" report in the sales rep footer.  If you don't have a sales rep footer, define one.  Set the master/child links and Access will synchronize the subreport with the main report.

PS - NEVER prompt for parameters for reports.  The two major problems with this method are:
1. You can't validate the data entry
2. It results in multiple prompts if you open the report in preview and then print it or output to Excel or PDF or Word.
And a third:
Apparently a different programmer wrote the code that evaluates crosstab queries so crosstab queries REQUIRE that any parameters be specifically defined as a parameter.  No other query type has this requirement.

The preferred method is to capture the arguments on a form.  Then either reference the form fields in place of prompts or save the form field values into TempVars and use those in your query.  

Another method is to capture the arguments in form fields and then use the where argument of the report/form to specify the criteria rather than having it in the query.

<<Create a query that returns the results you need for the subreport.  Create a report based on that query.  Put the "sub" report in the sales rep footer.  If you don't have a sales rep footer, define one.  Set the master/child links and Access will synchronize the subreport with the main report.>>
That's what I want to do, but how to make quesry from tbl Sales that gives me sales totals by month, by prev month?

<<PS - NEVER prompt for parameters for reports.  The two major problems with this method are:
1. You can't validate the data entry>>
I did not do this knowingly.  The parameters are from the underlying qry the report is based upon.  Should I remove the parameters from the underlying query, y/n?

And then I'm sorry, but I do not understand any of your response beyond this.

or asked another way ... what is the statement or expression in QBE to total records pulled by the month given a week ending date?  what is the statement or expression in QBE to total records pulled by the previous month given a week ending date?

in qrys that pull the correct records I used:
for current month
SELECT tblSales.SalesNo, tblSales.LNameRep, tblSales.DeptName, tblSales.SDate, tblSales.Customer, tblSales.SAmt, tblSales.Items, tblSales.RMR
FROM tblSales
WHERE (((tblSales.LNameRep)=[Enter Name]) AND ((Year([SDate]))=Year(Now())) AND ((Month([SDate]))=Month(Now())));

for prev month
SELECT tblSales.SalesNo, tblSales.LNameRep, tblSales.DeptName, tblSales.SDate, tblSales.Customer, tblSales.SAmt, tblSales.Items, tblSales.RMR
FROM tblSales
WHERE (((tblSales.LNameRep)=[Enter Name]) AND ((Year([SDate])*12+DatePart("m",[SDate]))=Year(Date())*12+DatePart("m",Date())-1));

For reasons unbeknownst to me, they work, as far as I can tell.  But how do you get totals from these?  (I gave you SQL which am lsower to understand, but not quick enough to print screen, copy & paste QBE)

If you need someone to write the query for you, you will need to post a copy of the database that includes the necessary tables, queries, and main report.  We can't guess what your field names are and we shouldn't have to create a test environment to write code for you for free.

Prompts work or people wouldn't use them.  The problem is they are flawed so doing something else will always be better in the long run.  There are lots of references to prompts.  "Prompt" is a word that an English speaking person would naturally come up with as a search term.  MS suggests them because when MS offers solutions, they do the most basic thing they can to accomplish the task and do not worry about trying to use best practice.  In most cases, they don't even know what is best practice since they do not develop with Access.  We do.  MS develops with some C variant.  In this thread someone mentioned that you should use prompts and that was why I addressed them.

The point I was trying to make was that you need to create a sales rep header/footer.  Then put the subreport there.  A report can have only a single bound recordsource and it makes no sense to put bound fields in page headers/footers unless you are printing only a single record per page.

<If you need someone to write the query for you, you will need to post a copy of the database that includes the necessary tables, queries, and main report.  We can't guess what your field names are and we shouldn't have to create a test environment to write code for you for free.>
Sorry, just wanted help writing the query and obviously didn't ask it correctly or clearly.

,Prompts work or people wouldn't use them.  The problem is they are flawed so doing something else will always be better in the long run.  There are lots of references to prompts.  "Prompt" is a word that an English speaking person would naturally come up with as a search term.  MS suggests them because when MS offers solutions, they do the most basic thing they can to accomplish the task and do not worry about trying to use best practice.  In most cases, they don't even know what is best practice since they do not develop with Access.  We do.  MS develops with some C variant.  In this thread someone mentioned that you should use prompts and that was why I addressed them.>
Oh, I see.

<The point I was trying to make was that you need to create a sales rep header/footer.  Then put the subreport there.>
Hey!  That was my plan of attack even though it probably wasn't clear.  At least it's a bit of confirmation.  I put a subreport (of written pending quotes into the footer) underneath the weekly sales details in the report.  And am trying to create qry for the above mentioned 'stats' for a subreport to be placed in the header.  I'm just stuck on how to get the total by month, by prev month, etc. into a qry.

But I'll stop for now in this forum.  I don't want to take up more of your time since I can't seem to communicate clearly my confusion (and further embarrass myself).

But I sincerely do appreciate your effort!

#a42006026 Does not seem to be complicated. I need to understand the problem, from the input records and listing the required output.
I see two records, each for different person, with full name and you ask to enter last name.

I'll check back after few hours.

hnasr, never mind for now.  am too confused right now.  but thank you anyway.

We're here to help.  If you knew the answer you wouldn't need to ask so there is no need to be embarrassed.  When you say "help me write the query", we can but not without a lot of information and if you want something that will actually work in your environment and that you don't need to translate, then we need a test bed to work with.  Don't post sensitive information.  Test data is fine but we do need actual forms/queries/reports so we can create queries with the correct names.

Thanks Pat.  I need to take a break right now.  Will check in later, maybe w a dummy db.  Have a good evening.

I start almost all queries using the QBE.  I've been working with SQL since the early 80's using COBOL and DB2 and now I use VBA and SQL Server, Oracle, DB2, etc.  Before I found Access,  I used to dream of a tool like QBE to let me build queries using drag and drop.  Typing SQL from scratch is simply tedious and unnecessary for at least the select clause.  I am a consultant and I typically work with a dozen or more applications during the course of a month and I simply can't keep hundreds of tables and thousands of columns and their exact spelling in my head at all times so even for an expert, QBE can be a huge time saver.    I can do about 70% of what I need to do and when QBE starts to interfere rather than help, I switch to SQL view.

Once you figure out how to use QBE, you will be amazed at how much it can help.  Even now SQL Server has a pathetic substitute.  It is so bad in fact that when I have to write T-SQL directly, I never use their version of QBE.

If you need help building a query, you need to provide the schema for the tables involved.  If you don't, we're just guessing and you get air code which you need to interpret and implement.  If you want someone to build something that will work for you (and many people here will do your actual work for free), then the least you can do is provide a database with the necessary base objects including data.

You should feel good about figuring out QBE well enough to create the query you needed.

Got it!  Thank you.

Ended up stumbling around and finding something that seems to work.  Not sure why though.