How can I get my query to return the Month Name instead of the Month number?

  • Thread starter Thread starter david.isaacks
  • Start date Start date
D

david.isaacks

SELECT X.[Facility VISN], X.MoNo, Count(*) AS Ct
FROM [SELECT 3 AS MoNo, [Facility VISN] FROM Output_MAR_2006_FP
UNION ALL
SELECT 2 AS MoNo, [Facility VISN] FROM Output_FEB_2006_FP
]. AS X
GROUP BY X.[Facility VISN], X.MoNo
ORDER BY X.[Facility VISN], X.MoNo;

How can I get my query to return the Month Name instead of the Month
number?
 
If you have a date/time field, you can use Format() to see MonthName.

By the way, if your database has tables with names like "Output_MAR_2006_FP"
and "Output_FEB_2006_FP", you have a ... spreadsheet!

A well-normalized relational database doesn't have data embedded in the
table names. The implication of your naming convention is that you have a
different table for each month/year. While about the only way to do it in a
spreadsheet, this approach in Access will make you (and Access) work much
harder than necessary. Plus, you won't be able to (easily) use the features
and functions that Access can offer.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP
 
SELECT X.[Facility VISN], X.MoNo, Count(*) AS Ct
FROM [SELECT 3 AS MoNo, [Facility VISN] FROM Output_MAR_2006_FP
UNION ALL
SELECT 2 AS MoNo, [Facility VISN] FROM Output_FEB_2006_FP
]. AS X
GROUP BY X.[Facility VISN], X.MoNo
ORDER BY X.[Facility VISN], X.MoNo;

How can I get my query to return the Month Name instead of the Month
number?

With MonthName([X.MoNo]) - or if that doesn't work, with

Format(DateSerial(2000, [MoNo], 1), "mmmm")

John W. Vinson[MVP]
 
Back
Top