Solved

Simple (ish) Query Involving a YearToDate Sales Figure

Posted on 2013-11-06
9
172 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

856 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