How to count entries by month (1 Viewer)

vcarrill

Registered User.
Local time
Today, 10:28
Joined
Aug 22, 2019
Messages
45
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:
1599861754095.png

My result:
1599861789271.png


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

Lifelong Learner
Local time
Today, 09:28
Joined
Mar 14, 2017
Messages
2,700
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

I’m here to help
Staff member
Local time
Today, 09:28
Joined
Oct 29, 2018
Messages
13,179
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

Banishment Pending
Local time
Today, 11:28
Joined
May 11, 2011
Messages
10,126
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:


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:


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

Super Moderator
Staff member
Local time
Today, 12:28
Joined
Feb 19, 2002
Messages
30,066
Have you considered the fact that the application may last more than one year? Do you want Jan 2020 data counted along with Jan2021?
 

Users who are viewing this thread

Top Bottom