Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Simple (ish) Query Involving a YearToDate Sales Figure

Posted on 2013-11-06
9
Medium Priority
?
179 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

610 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