Counting number of vessels for each month (1 Viewer)

[Anakardian]

I have been trying to get a count of the vessels we are operating grouped by months.
The data is structured as follows:
VesselID - VesselName - DateInManagement - DateOutManagement

DateInManagement is always filled out with a date.
DateOutManagement is either with a date or null.

What I would like to get is a list for a year as follows:
Month - NumberOfVessels

As starting input I have a date field used as a parameter in other queries that will be used together with this one. The date is the end data and I would like to get data going back a year.

The purpose is to use the number of vessels in further calculations.

 

[spikepl]

Your conditions for counting are not clear

A vessel with DateIn 15 Jan 2011 and no DateOut is counted in January 2011? Also February 2011 and onwards?

For overlaps between time periods: http://www.baldyweb.com/OverLap.htm

 

[jdraw]

I agree with spikepl.

What does a NULL Date represent?

It would facilitate responses if you could supply some sample data showing the various values and the expected output.

 

[vbaInet]

I think the OP would like to count those vessels where both IN and OUT dates were filled in.

 

[Anakardian]

Sorry if I was not clear enough, I must have left my brain somewhere in a spiderweb.

What I want to get out is how many vesels there are in management in a given month.

The actual data would be something like this:
VesselID - VesselName - DateInManagement - DateOutManagement
1 - Vessel 1 - 01-01-2001 - null
2 - Vessel 2 - 24-05-2012 - null
3 - Vessel 3 - 06-12-2005 - 23-02-2012

Null means that there is no out date=vessel is still in management.

What I want to pull out is a list that counts how many vessels are in management in a given month. That means that the result should look like this for the example:
Month - NumberOfVessels
Jan-2012 - 2
Feb-2012 - 2
Mar-2012 - 1
Apr-2012 - 1
May-2012 - 2
Jun-2012 - 2

I hope this makes it clearer.

 

[Anakardian]

Having continued the search for a solution I think the only way to solve this would be a module that takes the dates for the beginning and end of each month and counts how many vessels are in between.
I had no problem making a query that gives me the dates but since the module requires vba I seem to have hit a wall or at least a nearly vertical hill

The idea seems simple but the building of it eludes me so far.
What I want the module to do is to take the beginning and end dates from a query and in the next field return the number of vessels.
This is to be repeated for each set of dates.

 

[vbaInet]

Looking at your result set and your raw data set I don't understand how they match up. How did you derive those figures?

 

[Anakardian]

Hi vbaInet

Let me try to visualise it. View attachment Visualisation.pdf

The grey line represents the month where the vessel has been in management.
The arrows mean that the grey line continues in that direction.

I hope this helps.

 

[vbaInet]

That still doesn't help much. Let's take this first line as an example:

VesselID - VesselName - DateInManagement - DateOutManagement
1 - Vessel 1 - 01-01-2001 - null

What will the count for January be?

 

[Anakardian]

For vessel 1 it would count in jan 2001 and in every month thereafter until the end of time or until an end date is entered.
Since we don't know when vessel will leave our management we can not set an end date until we are informed it has been sold or ill be moved to a different management company.

 

[vbaInet]

What do you mean by count until the end of time? Let me see the ouput for that particular record, in the same format as your post #5.

 

[Anakardian]

That would simply be this:
Month Count
jan-2001 1
feb-2001 1
mar-2001 1
apr-2001 1
may-2001 1
......
oct-2012 1
.......
may-3195 1
.......

 

[spikepl]

So a vessel is "in the count" in the month of interest if the interval from
DateInManagement and onwards in any way overlaps the month of interest. This you can determine for each vessel for each month of interest, using Poul's example that I linked to in #2

 

[Anakardian]

I wish it was that easy.
The problem is that I need a count for every month within a period.

Of course I could make a seperate query for each month but that becomes useless when I try to make a graph out of the numbers.

 

[recyan]

Had tried something similar to what you want ( I think so).
It's rather a longish way, but all the same, see if it gives you some guidelines.
Just open the form in the attached db.


Thanks

 

[Anakardian]

Thank you Recyan, that looks promising.

I ill see if I can get it built into my own project.

 

[recyan]

Just take care to open the queries in SQL view, when you are checking them, as there are some odd joins (I suppose you are already aware of that, but just in case).
Best of Luck with your project.

Thanks

 

[vbaInet]

Just fyi, here's a piece of code that will get you the number of months between two dates:

(DateDiff("yyyy", [COLOR=Blue]dteStart[/COLOR], [COLOR=Blue]dteEnd[/COLOR]) + Abs([COLOR=Blue]intMonth [/COLOR]>= Month([COLOR=Blue]dteStart[/COLOR])) + Abs([COLOR=Blue]intMonth [/COLOR]<= Month([COLOR=Blue]dteEnd[/COLOR]))) - 1

... where dteStart and dteEnd are the start and end dates, and intMonth is the integer month value you are searching against. With this in mind, you can create a table with the months, numbers 1 to 12, use the code above in your query to get the counts. You can use the Min and Max dates between the In and Out fields as input to the code.

 

[Anakardian]

I have tried to implement the suggested solution but without luck.

Since I already had the information from tblReportDates available in a query I tried using that. From what I could understand from spelling my way through your module this is what it achieves. Apart from not being in a table, I now have the starting dates of each month in a query. This is called qryFleetRiskToolGetMonths.
The data is like this:
MonthOfRiskTool
01-01-2012
01-02-2012
....

I also modified query used to get the dates of in and out of management so the out date is always filled out with the end date specified by the user if the vessel is still in management.
This gives a result similar to your qryGetStartAndEndDateOfVesselManagement.
My query is called qryFleetRiskToolPerformanceVessels.
The data is like this with a selected end date of 31-07-2012:
VesselID - VesselName - DateInManagement - DateOutManagement
1 - Vessel 1 - 01-01-2011 - 31-07-2012
2 - Vessel 2 - 24-05-2012 - 31-07-2012
3 - Vessel 3 - 06-12-2005 - 23-02-2012

Based on these two queries I modified your query qryGetVesselsUnderManagement to this:

SELECT qryFleetRiskToolGetMonths.MonthOfRiskTool, qryFleetRiskToolPerformanceGetVessels.VesselID, qryFleetRiskToolPerformanceGetVessels.VesselName
FROM qryFleetRiskToolGetMonths LEFT JOIN qryFleetRiskToolPerformanceGetVessels ON (qryFleetRiskToolGetMonths.MonthOfRiskTool<=qryFleetRiskToolPerformanceGetVessels.DateOutManagement) AND (qryFleetRiskToolGetMonths.MonthOfRiskTool>=qryFleetRiskToolPerformanceGetVessels.DateInManagement);

The error I get is that the expression is typed incorrectly or is too complex.

Any ideas on what is going wrong or if a table is required for this to work?

 

[Anakardian]

Just figured out what it was.

Always, always, always remember to save a query every time you make a change to it. Otherwise it just gives you funny ideas.