Solved

Simple (ish) Query Involving a YearToDate Sales Figure

Posted on 2013-11-06
9
171 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

778 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