Solved Multiple MonthName() in a Single Query using MS Access?

[tltanhueco1990]

Hello to everyone who's in the query forums! I'm a new user here. Really new, that I have to ask if it's possible to do multiple MonthName() in a single query.

I'm trying to produce an output where the numerical months turn to MonthName(), but alas. Only 1 month is being shown. Here's what I've done so far:

SELECT Year(#11/3/2023#) AS [Year], MonthName(1) AS [Month], Count(*) AS Total
FROM admissions
GROUP BY Month([admission_date])
ORDER BY Month(admission_date);

End goal is to count the number of admitted patients(admissions) by year(2023) and month(MonthName()).

 

[Minty]

You could try something like

SELECT Year([admission_date]) AS [Year], Format ([admission_date], "mmm") as [Month], Count(*) AS Total
FROM admissions
GROUP BY Year([admission_date]), Format ([admission_date], "mmm")
WHERE  Year([admission_date]) = 2023
ORDER BY Month(admission_date);

Or even simply use

Format ([admission_date], "yyyy-mmm") as [Year-Month],

 

[tltanhueco1990]

You could try something like

SELECT Year([admission_date]) AS [Year], Format ([admission_date], "mmm") as [Month], Count(*) AS Total
FROM admissions
GROUP BY Year([admission_date]), Format ([admission_date], "mmm")
WHERE  Year([admission_date]) = 2023
ORDER BY Month(admission_date);

Or even simply use

Format ([admission_date], "yyyy-mmm") as [Year-Month],

Interesting suggestion! Let me try this out~ thanks!

 

[tltanhueco1990]

You could try something like

SELECT Year([admission_date]) AS [Year], Format ([admission_date], "mmm") as [Month], Count(*) AS Total
FROM admissions
GROUP BY Year([admission_date]), Format ([admission_date], "mmm")
WHERE  Year([admission_date]) = 2023
ORDER BY Month(admission_date);

Or even simply use

Format ([admission_date], "yyyy-mmm") as [Year-Month],

Hi, Minty! An update to your suggestion:

I have a syntax error with the FORMAT and WHERE part. I forgot to share that I'm using MS Access 2019.

 

[arnelgp]

the query (FinalQuery) on the demo is Crosstab query.
so at a glance you can see total count of admittance by month.

and also see Query2.

 

[tltanhueco1990]

the query (FinalQuery) on the demo is Crosstab query.
so at a glance you can see total count of admittance by month.

Didn't expect a fellow Filipino here! Hey there! Let me check the query of OsMan.

For now, isn't MS Access powered by JetSQL?

 

[arnelgp]

JetSQL

i'm not sure, or maybe ACE engine?

 

[tltanhueco1990]

i'm not sure, or maybe ACE engine?

I believe I recall it was JetSQL.

For now, I plan to do a single column showing all of the months in word format. The PIVOT shows separate tables from your OsMan example. Might I ask if the guide from sql-practice or W3Schools actually helpful in learning MS Access syntax? Most of the other websites only have guides for SQL Server or MySQL.

 

[Minty]

Sorry written on the fly, the where clause is in the wrong place

SELECT Year([admission_date]) AS [Year], Format ([admission_date], "mmm") as [Month], Count(*) AS Total
FROM admissions
WHERE  Year([admission_date]) = 2023
GROUP BY Year([admission_date]), Format ([admission_date], "mmm")
ORDER BY Month(admission_date);

 

[tltanhueco1990]

Sorry written on the fly, the where clause is in the wrong place

SELECT Year([admission_date]) AS [Year], Format ([admission_date], "mmm") as [Month], Count(*) AS Total
FROM admissions
WHERE  Year([admission_date]) = 2023
GROUP BY Year([admission_date]), Format ([admission_date], "mmm")
ORDER BY Month(admission_date);

It's OK! No rush~ I know how busy you must be at this time. I thank you for doing your best to assist!

 

[tltanhueco1990]

You could try something like

SELECT Year([admission_date]) AS [Year], Format ([admission_date], "mmm") as [Month], Count(*) AS Total
FROM admissions
GROUP BY Year([admission_date]), Format ([admission_date], "mmm")
WHERE  Year([admission_date]) = 2023
ORDER BY Month(admission_date);

Or even simply use

Format ([admission_date], "yyyy-mmm") as [Year-Month],

Let me try uploading a photo of the sample output:

 

[tltanhueco1990]

the query (FinalQuery) on the demo is Crosstab query.
so at a glance you can see total count of admittance by month.

and also see Query2.

Here’s the sample month column I need as an output.

Microsoft website states that the use of MonthName(Month, [abbreviation]) is the right way of converting numerical to word context, but I can’t seem to make it work under the SELECT statement.

 

[The_Doc_Man]

For now, isn't MS Access powered by JetSQL?

I forgot to share that I'm using MS Access 2019.

The Access Connectivity Engine (ACE) came out in 1992. Before then, you had the Joint Engine Technology engine (JET). However, ACE is merely a specific version/update of JET that became the default Access engine in Ac2007.

Access Database Engine - Wikipedia

en.wikipedia.org

 

[tltanhueco1990]

The Access Connectivity Engine (ACE) came out in 1992. Before then, you had the Joint Engine Technology engine (JET). However, ACE is merely a specific version/update of JET that became the default Access engine in Ac2007.

Interesting. If ACE is a specific version of JET, does that mean Access really is powered by JetSQL, not AceSQL?

 

[The_Doc_Man]

In the sense that even Microsoft's published literature calls the current engine "ACE" I would say "no." MS can call it whatever they like, and if they feel it deserves another name than JET, it IS their engine after all.

 

[tltanhueco1990]

In the sense that even Microsoft's published literature calls the current engine "ACE" I would say "no." MS can call it whatever they like, and if they feel it deserves another name than JET, it IS their engine after all.

That's new information for me! Thank you! Same goes for you, @arnelgp !

 

[Minty]

Final version ?

SELECT Year([admission_date]) AS [Year], MonthName(Month([admission_date])) as [Month], Count(*) AS Total
FROM admissions
WHERE  Year([admission_date]) = 2023
GROUP BY Year(admission_date),  MonthName(Month(admission_date))
ORDER BY Month(admission_date);

 

[tltanhueco1990]

Final version ?

SELECT Year([admission_date]) AS [Year], MonthName(Month([admission_date])) as [Month], Count(*) AS Total
FROM admissions
WHERE  Year([admission_date]) = 2023
GROUP BY Year(admission_date),  MonthName(Month(admission_date))
ORDER BY Month(admission_date);

Thanks again for responding!

I also had this syntax error with the MonthName() part where it states the message:

"Your query does not include the specified expression 'Month(admission_date)' as part of an aggregate function."

Which is strange because I have an 'admission_date' on my admissions table, but no specific column for the months.

 

[Minty]

Oh fiddlesticks

SELECT Year([admission_date]) AS [Year], MonthName(Month([admission_date])) as [Month], Count(*) AS Total
FROM admissions
WHERE  Year([admission_date]) = 2023
GROUP BY Year(admission_date),  MonthName(Month(admission_date)), Month([admission_date])
ORDER BY Month(admission_date);

 

[ebs17]

SELECT Year([admission_date]) AS [Year], MonthName(Month([admission_date])) as [Month], Count(*) AS Total
FROM admissions
WHERE  Year([admission_date]) = 2023
GROUP BY Year(admission_date), Month(admission_date)
ORDER BY Month(admission_date)