How to count entries by month (1 Viewer)

[vcarrill]

Hello,

I need advice on the following:

I have a table with a field called "Month" as a Text field, then the entries are January, February, March, etc. I am trying to see how many applicants I had by month.

I did the following:

My result:

First, not sure what 231 is since this is not the sum of the counts and the months are not in chronological order.

How do I correct this? Appreciate your advice as always, thank you.

 

[Isaac]

Instead of what you currently have for the 2nd column in the query, try doing a Count or a Sum. Probably a count, but I'm not sure, because I'm not sure what the rest of your table design & data looks like. If it's just a matter of counting every record, then you can probably count on any other column. If it's a column which already contains aggregated data, you might need to use Sum.

 

[theDBguy]

Also, since you're using Text for the month's name, it is sorting alphabetically. If you want it to sort chronologically, you'll have to use actual dates or the numeric equivalent of each month.

 

[plog]

231 are the number records with blank [Month] fields. Now a bunch of notes:

1. [Month] is a poor name choice because it is a reserved word:

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

You should prefix it with what that Month represents (e.g. ApplicationMonth)

2. No need to format a field your just going to count. Remove that.

3. You should build a field to sort on to get those "chronological". This would involve using the month value in constructing a string and then using CDate to make a date field out of it:

MS Access: CDate Function

This MSAccess tutorial explains how to use the Access CDate function with syntax and examples. The Microsoft Access CDate function converts a value to a date.

www.techonthenet.com

4. This isn't really chronological because a month value alone means nothing. I was born in February, my sister in January--who's older? Me by 2 years 11 months and a day. Sounds as if you haven't properly built your database if it cannot handle multiple years of data.

 

[Pat Hartman]

Have you considered the fact that the application may last more than one year? Do you want Jan 2020 data counted along with Jan2021?

 

[vcarrill]

Have you considered the fact that the application may last more than one year? Do you want Jan 2020 data counted along with Jan2021?

Hello, I would think this could be filtered?

 

[Pat Hartman]

Not if you only store month.