1

My good reference has been Convert Month Number to Month Name Function in SQL

So far I have: 

SELECT Detail.ItemCode, SUM(Detail.QuantityOrdered) AS Total_Quantity, Header.OrderDate
FROM Detail INNER 
JOIN rHeader ON Detail.SalesOrderNo = Header.SalesOrderNo
WHERE Header.OrderDate >= dateadd("m", -4, Date())
GROUP BY Detail.ItemCode, OrderDate
ORDER BY SUM(Detail.QuantityOrdered) DESC;

It filters my results and it shows only last four months result from today's month.

I'd like to have each month's sales quantity sum, and its month to name function.

For instance:

ItemCode | 10 or October | 11  |  12 |  1     
   PM    |        200    | 200 | 200 | 200

Update: I did try the following line of code, but my date is in the form of 12/26/2016. Is there anyway that I can play with it?? Thank you!

 SELECT MonthName( month , DateAdd( month , @MonthNumber , 0 ) - 1 )
Community
  • 1
  • 1
John Tipton
  • 185
  • 3
  • 11
  • 1
    Based on your question history, I believe you are using Access as the front and back end. If that is true, then your referenced page in the first sentence is a bad resource. SQL Server and Access are different platforms, using different versions of SQL (the language). They are not interchangeable. Focus on questions with [SQL] *and* [Ms-Access] tags or you are going to get examples with incorrect syntax or functions that don't exist. – MoondogsMaDawg Jan 31 '17 at 23:13
  • @ChristopherD. Thank you :) Very good point!! You are great! Thanks for the edit!! – John Tipton Feb 01 '17 at 00:06

1 Answers1

2

In an Access query you should be able to use the MonthName function to convert a month number (e.g., 10) into a month name (e.g., 'October').

For example, if you have a query that returns a date of birth (DOB)...

SELECT [DOB] FROM [Clients] WHERE [ID]=1

DOB
----------
1977-05-15

.. you can return the name of the month in which they were born using

SELECT MonthName(Month([DOB])) AS MonthOfBirth FROM [Clients] WHERE [ID]=1

MonthOfBirth
------------
May
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you so much for your comment! I have tried Select MonthName( month , DateAdd( month , @MonthNumber , 0 ) - 1 ). But I do not have any field to use 'month'. My date field is like 12/25/2016. Could you please give your input on this? Thank you! :) – John Tipton Jan 31 '17 at 22:25
  • Thank you so so much!! Much appreciated! Hope you have a great night :) – John Tipton Feb 01 '17 at 00:05