Counting occurances of months

[Gally]

Hi

I have a table with a column which has dates in it.

Eg

Column a

01/07/2004
19/07/2004
28/09/2004
06/10/2004
28/10/2004
17/11/2004
24/11/2004
30/11/2004
16/12/2004
10/01/2005
13/01/2005
19/01/2005

I need to count how many records are in each month

Ie 07/2004 = 2
09/2004 = 1
10/2004 = 2
11/2004 = 4
etc

I am using a group by and a count in a query but I think I need some sort of criteria which tells it only to work it out on the month & Year.

Sorry if this is an easy one!! I am not the best at Access but I am a tryer!!!!

TA
IAIN

 

[MStef]

Hello Gally!

Look at "DemoCountMonthA2000.mdb"
Query1, I think it is what you need.

 

[manix]

Hi and thanks Mstef, for posting the example.

I have a couple of questions on using this query to suit my requirment:

1. How can I get it so the Year month combination has an "/" separating them? Preferably I would also like them to appear the other way around, so mm/yyyy!

The expressions looks like this:

yyyyM: CLng(DatePart("yyyy",[Date Raised]) & Format(DatePart("m",[Date Raised]),"00")

I have fiddled for ages but cannot seem to get it to work without an error occuring!

2. Is it possible to change the name of the fields?

I ask thihs because this query will feed a line graph, and these names and dates would look odd if published in a report, which is reviewed by management.

TIA!

 

[MStef]

Hello manix!

I don't understand what do you want exactly.
Send a short example of your MDB (table), and say
what do you need.

 

[Brianwarnock]

1 Try
Year([f1]) & "/" & Format(Month([f1]),"00")

2 yes, just change the first toMM/YYYY and for the second put say Count of Year/Month:f1 in the field row

Else select the field properties and add a caption which I believe can/is used instead of the field name if it exists

Brian

 

[manix]

Thanks guys,

I have actually adopted a much more simple approach:

Month: Format([tbl_concern].[Date Raised],"mmm")

Then it counts the occurences of entries compared to their relative month in the [Date Raised] field.

However, can someone help with two further things:

The above expression gives me the following:

Month CountOfConcern_Number
Apr 1
Aug 1
Jul 1
Oct 1
Sep 2

That is fine, but I would prefer it to include 0 values. So if in a month there are no concerns, then it still shows the month in the month column, but states 0 as the count value. Also any way they can be in month order? So have something like this:

Month CountOfConcern_Number
Jan 0
Feb 0
Mar 0
Apr 1
May 0
Jun 0
Jul 1
Aug 1
Sep 2
Oct 1
Nov 0
Dec 0


Also does anyone know the best way to filter by year. So my query can be produced for a year entered into an unbound box?

TIA

 

[Brianwarnock]

You can achive what you want by using 2 queries and an additional table table.

The table has 2 fields MthNumber and MthText
query1 has 3 fields
Month([Date Raised]) groupby
Year([Date Raised]) groupby Criteria [Enter Year] this will cause a popup for the year to be entered
[Date Raised] Count

Query2 takes in the MthTable and query1 Left joined on MthNumber and the Month fields in order to take in all records from the table
Select fields
MthText ; Nz([name of Countfield from query1],0); MthNumber ,uncheck show, sort ascending

Run query2

Brian

 

[manix]

You can achive what you want by using 2 queries and an additional table table.

The table has 2 fields MthNumber and MthText
query1 has 3 fields
Month([Date Raised]) groupby
Year([Date Raised]) groupby Criteria [Enter Year] this will cause a popup for the year to be entered
[Date Raised] Count

Query2 takes in the MthTable and query1 Left joined on MthNumber and the Month fields in order to take in all records from the table
Select fields
MthText ; Nz([name of Countfield from query1],0); MthNumber ,uncheck show, sort ascending

Run query2

Brian

Thanks again Brian, this works perfectly...to a point! Query 1 works no problem.

However there are 2 issues on Query 2:

1. Nz([name of Countfield from query1],0) does not seem to be picking up the values that Query 1 returns for the countfield, because it just shows 0 all the time.

MthText Expr1
Jan 0
Oct 0
Nov 0
Dec 0
Feb 0
Mar 0
Apr 0
May 0
Jun 0
Jul 0
Aug 0
Sep 0

2. As you can see the months won't display in the correct order, because the month numbers are stored as text in the Mthnumber field. If they were stored as numbers its gives a Mismatch error when running the query because the join between the query and the table are joins between two different data types! I have attached a screenshot. Where am I going wrong?!!!!

 

[manix]

It's OK I have figured it!

Query2 takes in the MthTable and query1 Left joined on MthNumber and the Month fields

This should be Mthtxt and Month fields are joined. This now works perfectly!

 

[Brianwarnock]

I'm pleased that you got it working but

because the month numbers are stored as text in the Mthnumber field.

not in my table, they would be numbers, and mthtext would be the text version. Month([ADatefld]) returns a number, hence you join it to mthNumber , then I was only introducing the text of the month in query2 from the table.

Brian

 

[manix]

HI,

Can I just ask with reference to the above. How can I get the results of Query 2 into number format, so that I can create a chart from them?

At the moment, I am getting months and the Countof result from Query 1 but they are not classed as numbers. Access won't let me summerise the results as a sum on Y axis because it does not see them as a number?! If I double click on the Y axis label to change how the information is summarised in the chart, it says "you can't change a date, text or key field, please select a number field". It is automatically set to COUNTofExpr1. This is no use!!!!!

Basically I want a line chart with Months on the X axis and the sum of counts for each month on Y axis. Access won't currently let me do this!

 

[Brianwarnock]

Well Manix I have never charted in ACCESS and after the last 30 minutes of trying I guess I never will.
One think though that frequently catches me out is that Nz makes the field Text, a simple way to get back to numeric is using Val
Val(Nz([name of Countfield from query1],0)) this enabled me to get a chart with numeric month values on the X axis , but I cannot get Test months which would be preferable.

Edit I lied its just worked, I am certain that it was the fact that the count was text "numbers" instead of numeric was the problem
BTW I discovered my error by exporting to Excel which flagged the field as text numbers.

Brian

 

[manix]

Well done Brian. I have found Access charting a complete pain in the ****!!!

It was the Val bit that got it all working!!!!!

Just one more thing though, when you produced the chart, where the months retained in numerical order, as they are in the query (owing to the link with the month number field)?

My months are dispalyed on the chart in alphabetical order!

 

[Brianwarnock]

Oh S**t you are correct, I was so pleased to get it working I never noticed.
I haven't a clue what to do about that, why should it change the order?
I would post this question as a new thread to see if those who use Access charting know the answer.

Sorry about the Nz/Val by the way, not quite so in touch now that I'm retired.

Brian

 

[Brianwarnock]

OK I've worked out how to do it, now how to explain.

Go into Design view for the report, click on properties,click on the sample chart and and find the row source. click on the ... alongside this opens up the SQL in the query Builder, delete one of the 2 sum on the count field, why 2 I don't know, drag in the numeric month field make it sort ascending and no show, save and open the report.

Hope you can figure this out, must go for my meal now, will be on later.

Brian

 

[manix]

Thanks Brian, you're a legend. That works perfectly now!! Though I now fear MS Access charting, I do know a bit more about how to get them working as I want them to!

Hope your meal was nice, I owe you a beer!