Solved

Simple (ish) Query Involving a YearToDate Sales Figure

Posted on 2013-11-06
9
166 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now