?
Solved

Simple (ish) Query Involving a YearToDate Sales Figure

Posted on 2013-11-06
9
Medium Priority
?
175 Views
Last Modified: 2013-11-06
Hi,

See attached.

I want a query that gives me a YearToDate Sales figure by salesman (using "group by").

The slightly tricky bit is that the "current month" is determined by tblParameters.ParameterValue  where ParameterNumber=3.

The end result should look something like;

Joe 54
Tim 78

Thanks
0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39626942
Try attaching the database again :-)  It didn't make it...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39626961
Based on the db in your last question for tables and field names, this should do the trick...

SELECT s.SalesMan, Sum(s.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth s INNER JOIN tblParameters p
ON  s.MonthNumber = p.ParameterValue
WHERE p.ParameterNumber=3
GROUP BY s.SalesMan

Open in new window



However, will parameter number always be 3?

This will list all months, grouped/sorted by Salesman:

SELECT s.SalesMan, Sum(s.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth s INNER JOIN tblParameters p
ON  s.MonthNumber = p.ParameterValue
GROUP BY s.SalesMan

Open in new window


And IF you create a form with a combo box for the monthnumber (the rowsource can include ParameterNumber and ParameterValue from the parameters table, with Parameter number as the unique ID), the following will give you sales by salesperson for a user-selected month:


SELECT s.SalesMan, Sum(s.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth s INNER JOIN tblParameters p
ON  s.MonthNumber = p.ParameterValue
WHERE p.ParameterNumber= Forms!YourForm!cboMonth
GROUP BY s.SalesMan

Open in new window

0
 

Author Comment

by:Patrick O'Dea
ID: 39626983
Let me clarify (and attach!)

You previous solution did not work (I may have confused things!).

The sales figures required are year to date.

The end result should look something like;

Joe 54
Tim 78

(The latest solution only returned a single months value)
Database5.accdb
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

Expert Comment

by:mbizup
ID: 39626996
Whoops -  the "=" sign looks for an exact match.

For YTD, change the = to <= to get months before and including the one specified... and you'll have to select the month based on the parameter number,

Try this:

SELECT s.SalesMan, Sum(s.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth s INNER JOIN tblParameters p
ON  s.MonthNumber = p.ParameterValue
WHERE s.MonthNumber <= (SELECT p2.ParameterValue FROM tblParameters p2 WHERE p2.ParameterNumber = 3) 
GROUP BY s.SalesMan

Open in new window

0
 

Author Comment

by:Patrick O'Dea
ID: 39627013
Thanks,
I have tried you most recent suggestion.
It looks good .. It should work ... it must work but .... it doesn't!

It only shows a SINGLE month despite the use of the "<=".

Any thoughts?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39627017
Ok... third time is the charm.

Give this a shot:

SELECT s.SalesMan, Sum(s.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth s
WHERE s.MonthNumber <= (SELECT p2.ParameterValue FROM tblParameters p2 WHERE p2.ParameterNumber = 3) 
GROUP BY s.SalesMan

Open in new window

0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 39627025
Hi,

Can you give it a try:
SELECT sbm.SalesMan, SUM(sbm.SalesValue) 
FROM tblSalesByMonth sbm 
where sbm.MonthNumber <= (select ParameterValue from tblParameters where ParameterNumber = 3) 
and sbm.MonthNumber <> (select CurrentMonth from  tblWhichMonthIGNORE)
group by sbm.SalesMan

Open in new window


If you don't want to ignore the data from tblWhichMonthIGNORE then remove the second condition... from "and sbm.....".
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39627027
Ok,

That's it. I should have spotted the error.

Have a good day and goodbye from Ireland (for the moment).
Thanks again.

11:20am.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39627031
Glad to help out.

I have some Irish ancestry :-)
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question