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

[tltanhueco1990]

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

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);

The output is a blank query. Might I ask why the 'MonthName(Month(admission_date)), Month([admission_date])' part?

 

[Minty]

When you aggregate (Count, Min. Max, Sum Etc) a field in a query you have to group by all of the non-aggregated fields.

I'm struggling to see why it isn't working so please upload a sample database (Compact and repair it and then zip it) with the minimum of data in a table to allow us to write you a query.

 

[tltanhueco1990]

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)

Thank for sharing, ebs!

A rather strange part is that I have no table for the month of the admission date within the relationship.

 

[tltanhueco1990]

When you aggregate (Count, Min. Max, Sum Etc) a field in a query you have to group by all of the non-aggregated fields.

I'm struggling to see why it isn't working so please upload a sample database (Compact and repair it and then zip it) with the minimum of data in a table to allow us to write you a query.

Here's the full DB I'm trying to identify. I can't seem to zip it, so I'll send the file here directly.


I have 3 queries to work on:

a. Counting the number of Diseases by Year.
b. Counting the number of Admission by Year and Month.
c. Counting the number of Admission by Year from every Province.

​

 

[ebs17]

In relationships, I find a monthly, yearly or even weekly table to be completely superfluous.

Useful and often quite helpful are auxiliary tables such as a calendar table. Such a calendar table would have as primary key a continuous date for a sufficiently large period. Now comes the advantage of the auxiliary table: It is precalculated once, it does not have to adhere to rules of normalization. So you can keep formats for the date in additional columns like year, month, week, weekday, quarter, ...
These columns are also indexed.

As a result, one can always get the necessary formats into one's query via a datum and also work out these formats index-supported.

 

[tltanhueco1990]

I’m supposed to follow this relationship table. Now, I ponder where to get MonthName(). A close professor of mine just stated that I just need 3 statements:

COUNT
GROUP BY
ORDER BY

 

[Minty]

This is the first query You need:

SELECT Year([admission_date]) AS [Year], admissions.diagnosis, Count(admissions.patient_id) AS NoOfAdmission, MonthName(Month([Admission_Date])) AS Month_Text
FROM admissions
GROUP BY Year([admission_date]), admissions.diagnosis, MonthName(Month([Admission_Date])), Month([admission_date])
HAVING (((Year([admission_date]))=2019))
ORDER BY Month([admission_date]);

Paste this into the SQL of a new query. it produces

You have no data for 2023 in the sample other wise you can add the year(Date()) criteria to get this years date.

Your original query was very wrong.

 

[Minty]

Look at how the query presents itself in the Query by example editor.

You should be able to work out how to remove or add the groupings you want for the other queries.
If this is college course-work that's down to you.

 

[tltanhueco1990]

Look at how the query presents itself in the Query by example editor.

You should be able to work out how to remove or add the groupings you want for the other queries.
If this is college course-work that's down to you.

This will be most helpful! Thank you so much!

 

[tltanhueco1990]

This is the first query You need:

SELECT Year([admission_date]) AS [Year], admissions.diagnosis, Count(admissions.patient_id) AS NoOfAdmission, MonthName(Month([Admission_Date])) AS Month_Text
FROM admissions
GROUP BY Year([admission_date]), admissions.diagnosis, MonthName(Month([Admission_Date])), Month([admission_date])
HAVING (((Year([admission_date]))=2019))
ORDER BY Month([admission_date]);

Paste this into the SQL of a new query. it produces
View attachment 106942

You have no data for 2023 in the sample other wise you can add the year(Date()) criteria to get this years date.

Your original query was very wrong.

The 1st 2 queries are done, thanks to your amazing support! Is counting the number of admission by year from every province like this:

SELECT Year([admission_date]) AS [Year], province_names.province_name, Count(admissions.patient_id) AS Total
FROM province_names
GROUP BY Year([admission_date]), province_names.province_name, MonthName(Month([Admission_Date])), Month([admission_date])
HAVING (((Year([admission_date]))))
ORDER BY Month([admission_date]);

I followed the pattern. It would seem that the 'province_name' column inside the "province_names' table aren't being read.

 

[Minty]

You aren't including the admissions table in your query. I'm amazed that you could even save this as a valid query.

In the query window, you need to include all the tables you need to be able to query those fields.
This is query building 101. You can't just randomly write SQL without understanding what it is doing.

Access's Query By example window makes this very easy for you.

Drag the tables in that you need and if necessary adjust the joins to suit your needs.
The above would generate

SELECT *
FROM province_names INNER JOIN (patients INNER JOIN admissions ON patients.patient_id = admissions.patient_id) ON province_names.province_id = patients.province_id;

Now apply your grouping totals and selection criteria.

 

[tltanhueco1990]

You aren't including the admissions table in your query. I'm amazed that you could even save this as a valid query.

In the query window, you need to include all the tables you need to be able to query those fields.
This is query building 101. You can't just randomly write SQL without understanding what it is doing.

Access's Query By example window makes this very easy for you.
View attachment 106943

Drag the tables in that you need and if necessary adjust the joins to suit your needs.
The above would generate

SELECT *
FROM province_names INNER JOIN (patients INNER JOIN admissions ON patients.patient_id = admissions.patient_id) ON province_names.province_id = patients.province_id;

Now apply your grouping totals and selection criteria.

We actually needed the JOIN function? Noted! Thanks!