count open records per month

[deb]

Access 2010

I have a table, t_Orders, with the following fields.. OrderNo, OpenedDate, ClosedDate.

OrderNo, OpenedDate, ClosedDate.
12345 1/1/2017 1/10/2017
12346 1/10/2017 3/5/2017
12347 3/4/2017 4/4/2017
12348 2/20/2017 3/22/2017

I need to count how may records are open per month.

Should look something like this:

Jan2017 Feb2017 Mar2017 Apr2017
2 2 3 1

Don't even know where to start with this one.
Data above is simplified, actual data is over 50k records spanning over many years.

 

[Muhammad_Usman]

[TABLE="width: 459"]
<colgroup><col span="3"><col span="3"></colgroup><tbody>[TR]
[TD]Oeder No[/TD]
[TD]OpenDate[/TD]
[TD]ClosedDate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12345[/TD]
[TD]1/25/2017[/TD]
[TD]1/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12346[/TD]
[TD]1/10/2017[/TD]
[TD]3/5/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12347[/TD]
[TD]3/4/2017[/TD]
[TD]4/4/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12348[/TD]
[TD]2/20/2017[/TD]
[TD]3/22/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12349[/TD]
[TD]4/25/2017[/TD]
[TD]5/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12350[/TD]
[TD]1/10/2017[/TD]
[TD]6/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]For Jan =[/TD]
[TD]

=COUNTIFS($B$2:$B$8,">="&(A10),$B$2:$B$8,"<="&EOMONTH(A10,0))

For Other Month

=COUNTIFS($B$2:$B$8,"<="&EOMONTH(B10,-1)+1,$C$2:$C$8,">="&B10)+COUNTIFS($B$2:$B$8,">="&(B10),$B$2:$B$8,"<="&EOMONTH(B10,0))

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Micron]

I think the first response is confusing this with Excel when this is an Access question, for I don't believe there's a CountIf function in Access so I'll answer as an Access question.

It depends on what constitutes an "open" record. The fact that there is an open date? That there is an open but not a closed date? That a date is less than the current date? Looking at your expected results, I can't arrive at 2 for February no matter how I interpret "open". I notice that you seem to want to put the results in columns rather than rows, which could mean this is for Excel, since Access is row based data and is harder to put into columns. If "open" means there's an open date regardless of anything else, a query like

SELECT [t_Orders].OpenedDate, Count([t_Orders].OpenedDate) AS CountOfOpenedDate
FROM [t_Orders]
GROUP BY [t_Orders].OpenedDate
HAVING ((([t_Orders].OpenedDate) Is Not Null));

Basically, I suspect you might need to create a Totals query first, then if you must have the rows as columns, create a Crosstab query using the Totals query as a table. If you don't really need data in columns, then I'd forget it since it can introduce a new set of problems.
By the time you read this, I might have gone away for a few days. You can always research Totals and Crosstab queries for Access.