Simple SQL Query required (or not so simple?)

See simplified database attached.

See tblWhichMonth.  This very (very) simple table simply indicates which month we are currently processing.

See tblSalesByMonth which has a "sales" figure for each of the 12 months of the year.

I need a query that show my "Year To Date" sales figure.  That's it!

So for instance, if the tblWhichMonth.CurrentMonth = 4 then the total sales will be the sum of the first 4 "SalesValue" in the tblSalesByMonth
Database5.accdb
Patrick O'DeaAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Just add on Salesman as a column, and include it in the group by clause:

SELECT s.SalesMan, Sum(s.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth s, tblWhichMonth m
WHERE s.MonthNumber <= m.CurrentMonth
GROUP BY s.SalesMan

Open in new window

0
 
mbizupCommented:
Try this  - uses a SUM over records where the Month in the Sales table is less than or equal to the month in the WhichMonth table:

SELECT Sum(tblSalesByMonth.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth, tblWhichMonth
WHERE (((tblSalesByMonth.MonthNumber)<=[tblWhichMonth].[CurrentMonth]))

Open in new window

0
 
mbizupCommented:
Simplified a little with aliases and removing the extra ()'s that access adds:

SELECT Sum(s.SalesValue) AS SumOfSalesValue
FROM tblSalesByMonth s, tblWhichMonth m
WHERE s.MonthNumber <= m.CurrentMonth

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Patrick O'DeaAuthor Commented:
Thanks mbizup,

Great stuff so far but can I be allowed to complicate things slightly.

See attached.
Note that I have added a new field called "Salesman".

Can you give me some SQL that would give the the year to date sales (as before) and also group the sales by rep.

E.g.
Joe 48
Tim 53
Database5.accdb
0
 
Patrick O'DeaAuthor Commented:
Thanks again mbizup,

If it's okay can I tweak the question again.
If you prefer I can raise another question??

I have removed tblWhichMonth and replaced it with a more real life table called tblParameters.   The month number will now be determined by

tblParameters.ParameterValue where tblParameters.ParameterNumber =3

How do I include this in my "select" statement.
Database5.accdb
0
 
mbizupCommented:
Yeah - could you post another question please (this one is a bit different in structure)?  

Thanks!
0
 
Patrick O'DeaAuthor Commented:
New question on it's way!

By the way,  I can't help wonder what time zone you are in ..... I did not expect a response until much later in the day!
0
 
Patrick O'DeaAuthor Commented:
First class answer.  Thanks!
0
 
mbizupCommented:
US East coast, but I'm usually up very early.  Its about 05:25 my time right now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.