Annual 12 Month Summary Report on open orders

[John Zelmer]

Dear forum members,

I need to make a report which shows the number of open orders (based on Orders fields Order 'Date Start' and 'Order Date End') per month, for 12 months (based on user input date). So, for example, the question is: how many orders were open (in progress) in september 2014. Kinda like recreating history.

I've looked at the crosstab solution of FMSINC but that only works if you're dealing with one date (for instance how many orders were closed).

I know how to make the desired calculation query for one month with 4 WHERE clauses:
- start before the month, ending in the month, OR
- start before the month, ending after the month or still open, OR
- start in the month, ending in the month, OR
- start in the month, ending after the month or still open

However, I can't figure out how to make a query that does the above but then for 12 months in one query.

Any ideas on what would be the easiest way to go about this?

Thanks,

John

 

[plog]

The way to do this is with a Cartesian Product (http://en.wikipedia.org/wiki/Cartesian_product). That's fancy talk for a query with unjoined data sources.


You will need a datasource of all the months you want to report on. Preferable with start and end dates. Something like this:

ReportMonths
ReportMonthName, ReportStart, ReportEnd
September, 9/1/2014, 9/30/2014
October, 10/1/2014, 10/31/2014
...
May, 5/1/2015, 5/31/2015

You don't necessarily need that as a table, often you can create a query and derive all that data if you have a table which has all months you want to report on in it.

Once you have that, you can create your query. You bring in that ReportMonths datasource and your Orders table, but don't link them at all. You bring in [ReportMonthName] from ReportMonths and then you create a calculated field to see if each order was open in the timeframe ([ReportStart] to [ReportEnd]), if it was, you make it spit out 1, if not, 0, then you sum that calculated field. In the end you will have a totals query that tells you how many were open per month.

That calculated field will look something like this:

OpenOrders: Iif([DateStart]>=[ReportStart] AND [DateEnd]<=[ReportEnd], 1,0)

Of course that only covers one case--orders dates fall within the montly time frame. You will also need to check for all those other conditions you called WHERE clauses in your initial post.

 

[namliam]

I think I would solve this with IIFs or alternatively use a dimension date table.... but IIF is I think more doable.

Select
Sum(IIF(Startdate<dateadd("M", -12, date()) and enddate>dateadd("M", -13, date()),1,0)) as 12MonthsAgo,
sum(IIF(Startdate<dateadd("M", -11, date()) and enddate>dateadd("M", -12, date()),1,0)) as 11MonthsAgo,
sum(IIF(Startdate<dateadd("M", -10, date()) and enddate>dateadd("M", -11, date()),1,0)) as 10MonthsAgo,
...
etc..
From yourtable

Maybe an inbetween solution might be to make a table (tblMonths)
OpenMonth
202012
202011
... etc...
201512
201511
201510
201509
.. etc ...
201203
201202
201201
... etc...

Now make a query like so

Select OpenMonth, count(*)
from yourtable
join tblMonths on Openmonth >= format(startdate, "YYYYMM") 
          and OpenMonth <= format(Enddate, "YYYYMM")
where <some where clause to limit to whatever you want to see>
Group by OpenMonth

 

[John Zelmer]

Thanks Plog and namliam for quick response!

I have to process this the coming days and experiment with it to make it work. I'll post back my results.

 

[John Zelmer]

Hi again,

It took me a few hours but I got it working with your solution Plog. Even without VBA. Thank you very much! I can also use the dynamic report field naming like in the example of FMSINC. See below for what I did.

Bye,
John

1. Create a standard report month range table from 2005 till 2030.
2. Create a query (see below: Query A) with total sums per month with the Cartesian product (like you said) per city based on a startdate value in a criteria form.
3. Use that query in the crosstab query, making sure the column names range from 1 to 12, so I can name them to the right year-month value in the report (see below: Query B).

Query A:

PARAMETERS [Forms]![frmCrit]![fldDateStart] DateTime;

SELECT tb_MonthReference.ReportYearMonthNr, tb_MonthReference.MonthStartDate, Orders.ShipCity,

Sum(IIf(

([OrderDateStart]<[MonthStartDate] And [OrderDateEnd]>=[MonthStartDate] And [OrderDateEnd]<=[MonthEndDate]) Or

([OrderDateStart]<[MonthStartDate] And ([OrderDateEnd]>[MonthEndDate] Or [OrderDateEnd] Is Null)) Or

([OrderDateStart]>=[MonthStartDate] And [OrderDateStart]<=[MonthEndDate] And [OrderDateEnd]<=[MonthEndDate]) Or

([OrderDateStart]>=[MonthStartDate] And [OrderDateStart]<=[MonthEndDate] And ([OrderDateEnd]>[MonthEndDate] Or [OrderDateEnd] Is Null)

),1,0))

AS OpenOrders

FROM Orders, tb_MonthReference

WHERE (
((tb_MonthReference.MonthStartDate)>=DateAdd("m",-1,[Forms]![frmCrit]![fldDateStart])) AND
((tb_MonthReference.MonthEndDate)<=DateAdd("m",+12,[Forms]![frmCrit]![fldDateStart]))
)

GROUP BY tb_MonthReference.ReportYearMonthNr, tb_MonthReference.MonthStartDate, Orders.ShipCity
ORDER BY tb_MonthReference.ReportYearMonthNr;


Query B:

PARAMETERS [Forms]![frmCrit]![fldDateStart] DateTime;
TRANSFORM Sum(Query2.OpenOrders) AS SomVanOpenOrders
SELECT Query2.ShipCity
FROM Query2
GROUP BY Query2.ShipCity
PIVOT Year([MonthStartDate])*12+Format([MonthStartDate],"mm")-(Year(Forms!frmCrit!fldDateStart)*12+Format(Forms!frmCrit!fldDateStart,"mm"))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

 

[John Zelmer]

Hi again,

I have an additional question to this thread. Since I'm counting open orders per month I can't just add the rows' and column's values to get the total number of unique orders per city for one year and the total number of unique orders per month. I'd count orders double. Is it somehow possible, in the solution of FMSINC (where it's done with calculated fields), to only count the unique number of orders for the totals? Seems impossible...

FMSINC solution can be found searching: MS Access crosstab monthly report

Thanks in advance,
John