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

[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?

 

[Jeff Boyce]

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

 

[david.isaacks]

How can I change MoNo to Month Name is this SQL?

 

[John Vinson]

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]