Counting each month when only have start/End Date? 2

[cashe]

Hello all,

If its not possible could someone please tell me

I have an Access 2K db which holds consultant, location and project information. Each consultant has a start date and an end date. The consultant will work on one project. There are about 600 consultants and 55 projects.

Management want a report which shows the number of consultants that worked on each project for each month of the year. Row Fields = Location, Project. Column Field = Months of year

How can you count the number of consultants on each project by location for each month in 2002 when you only have a start and an end date?

e.g If consultant A StartDate = 1.1.02 and EndDate=15.4.02. He needs to be counted in the totals for Jan, Feb, March and May

Thanks in advance

Cashe

 

[allanon]

You will need a query for each month

For Jan go like this:

SELECT Count MyTable.ConsultantId
FROM MyTable
WHERE MyTable.ConsultantStartDate <= #Jan 31, 2002#
AND MyTable.ConsultantEndDate >= #Jan 1, 2002#
Group By MyTable.ConsultantId

That will get you any consultant that worked in Jan. Repeat this for the other months. Place the results into a table and then you can use them for anything.

 

[MichaelRed]

my table
ProjId EmpId StartDt EndDt
ProjA 200201234 2/14/02 9/22/02
ProjA 258110305 8/15/02 9/2/02
ProjB 261647386 8/1/02 9/1/02
ProjB 444582173 1/12/02 2/2/02
ProjC 666121245 2/3/02 4/6/02

myquery (SQL)
SELECT tblLeaves.ProjId, tblLeaves.EmpId, tblLeaves.StartDt, tblLeaves.EndDt, IIf(Month([StartDt])<=1 And Month([EndDt])>=1,True,False) AS Jan, IIf(Month([StartDt])<=2 And Month([EndDt])>=2,True,False) AS Feb, IIf(Month([StartDt])<=3 And Month([EndDt])>=3,True,False) AS Mar, IIf(Month([StartDt])<=4 And Month([EndDt])>=4,True,False) AS Apr, IIf(Month([StartDt])<=5 And Month([EndDt])>=5,True,False) AS May, IIf(Month([StartDt])<=6 And Month([EndDt])>=6,True,False) AS Jun, IIf(Month([StartDt])<=7 And Month([EndDt])>=7,True,False) AS Jul, IIf(Month([StartDt])<=8 And Month([EndDt])>=8,True,False) AS Aug, IIf(Month([StartDt])<=9 And Month([EndDt])>=9,True,False) AS Sep, IIf(Month([StartDt])<=10 And Month([EndDt])>=10,True,False) AS Oct, IIf(Month([StartDt])<=11 And Month([EndDt])>=11,True,False) AS Nov, IIf(Month([StartDt])<=12 And Month([EndDt])>=12,True,False) AS [Dec]
FROM tblLeaves
WITH OWNERACCESS OPTION;

myquery (reesults)
ProjId EmpId StartDt EndDt Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
ProjA 200201234 2/14/02 9/22/02 0 -1 -1 -1 -1 -1 -1 -1 -1 0 0 0
ProjA 258110305 8/15/02 9/2/02 0 0 0 0 0 0 0 -1 -1 0 0 0
ProjB 261647386 8/1/02 9/1/02 0 0 0 0 0 0 0 -1 -1 0 0 0
ProjB 444582173 1/12/02 2/2/02 -1 -1 0 0 0 0 0 0 0 0 0 0
ProjC 666121245 2/3/02 4/6/02 0 -1 -1 -1 0 0 0 0 0 0 0 0

(although it is hard to decipher in the narrow width format)


I would think a report based on this query with appropiate sorting and grouping would provide the information required. Another approach might just use this as the source for an agggregate.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[allanon]

Hi MichaelRed. Your method will create a very useful table except for one small problem. It does not take into account year spanning. If a consultant worked from Jan 01, 2001 to Jan 31, 2002 you would have a problem. Only the jan would get a true response which is true of course for 2002. What if the guy worked from june 1, 2001 to jan 1, 2002. Now none of the months are true. you cannot test for month. In keeping with your idea you would have to rewrite each of the IIF statements to test for dates. ie:

SELECT tblLeaves.ProjId, tblLeaves.EmpId, tblLeaves.StartDt, tblLeaves.EndDt, IIf((([StartDt])<=#jan 31, 2002# And ([EndDt])>= #jan 1, 2002#), True,False) AS Jan, etc.......


Anyhow, something like that. I thought of this also but I thought maybe it was a little more confusing. It is a good solution thou for doing it all in one step.

 

[PaulBricker]

One more option. I created a table tblTemp and a delete query, qryDeletetblTemp. Then I ran this code. It starts by deleteing everything in tblTemp.

Function returnmonths()
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Dim dteVal As Date
Dim i As Integer, x As Integer, y As Integer, z As Integer
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryDeletetblTemp&quot;
DoCmd.SetWarnings True
Set rst = CurrentDb.OpenRecordset(&quot;tblConsultant&quot;, dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset(&quot;tblTemp&quot;, dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
i = 1
x = DateDiff(&quot;m&quot;, rst!StartDate, rst!EndDate) + 1
y = 0
Do Until i > x
dteVal = DateAdd(&quot;m&quot;, y, rst!StartDate)
rst2.AddNew
rst2!consultantID = rst!consultantID
rst2!ActiveMonths = Format(dteVal, &quot;mmmm&quot & &quot;, &quot; & Format(dteVal, &quot;yyyy&quot
rst2!ProjectID = rst!ProjectID
rst2.Update
y = y + 1
i = i + 1
Loop
rst.MoveNext
Loop
End Function

Then it puts in values like this

ConsultantID  ActiveMonths   ProjectID
1                     January, 2001       A103
1                     February, 2001      A103
1                     March, 2001         A103
2                     February, 2001      A103
2                     March, 2001         A103
3                     December, 2001      A221
3                     January, 2002       A221
3                     February, 2002      A221

You could run the Report off tblTemp at this point.

Paul

 

[MichaelRed]

Management want a report which shows the number of consultants that worked on each project for each month of the year. Row Fields = Location, Project. Column Field = Months of year

Appears to not be concerned re the YEAR of the work. If this is a real issue, just include parameter(s) for the year. I would assume that you will not need more than a few years, so this with a union query would get it together.

From another perspective, I find it difficult to get much more than one year on a report (line) specifying months, so it might be acceptable to just generate seperate reports for each year?



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cashe]

WOW great. Thanks very much for your help guys.

MichaelRed
Your method works great and I've used it for the reports with some slight modifications. However If the consultants EndDate is in Dec 2003 then he is not included in the months that he has worked in 2002. I will try and add the Year parameters.

Allanon
I was doing it your way to start with but things got a bit messy. I have about 12 different managers wanting there own reports for the sections they are responsible. There is about 70 sections.

Paul
I've learned a lot from some of your answers in previous threads so thanks! But this time you lost me (Sorry)

I will add my final solution to this thread once I have finished

Thanks again

Cashe ;-)

 

[cashe]

Still working on it.

Allanon
I used your sugestion in MichaelRed's solution and its giving me correct output. Fantastic!!

The SQL code now looks like

SELECT tblAssigned.Location, tblAssigned.[Section ABB], tblAssigned.[Project name], tblAssigned.[Assigned ID], tblAssigned.[Mobilised Date], tblAssigned.[Demobilised Date], IIf(([Mobilised Date])<=#1/31/2002# And [Demobilised Date]>=#1/1/2002#,&quot;1&quot;,&quot;0&quot AS Jan, IIf(([Mobilised Date])<=#2/28/2002# And [Demobilised Date]>=#2/1/2002#,&quot;1&quot;,&quot;0&quot AS Feb, IIf(([Mobilised Date])<=#3/31/2002# And [Demobilised Date]>=#3/1/2002#,&quot;1&quot;,&quot;0&quot AS Mar, IIf(([Mobilised Date])<=#4/30/2002# And [Demobilised Date]>=#4/1/2002#,&quot;1&quot;,&quot;0&quot AS Apr, IIf(([Mobilised Date])<=#5/31/2002# And [Demobilised Date]>=#5/1/2002#,&quot;1&quot;,&quot;0&quot AS May, IIf(([Mobilised Date])<=#6/30/2002# And [Demobilised Date]>=#6/1/2002#,&quot;1&quot;,&quot;0&quot AS Jun, IIf(([Mobilised Date])<=#7/31/2002# And [Demobilised Date]>=#7/1/2002#,&quot;1&quot;,&quot;0&quot AS Jul, IIf(([Mobilised Date])<=#8/31/2002# And [Demobilised Date]>=#8/1/2002#,&quot;1&quot;,&quot;0&quot AS Aug, IIf(([Mobilised Date])<=#9/30/2002# And [Demobilised Date]>=#9/1/2002#,&quot;1&quot;,&quot;0&quot AS Sep, IIf(([Mobilised Date])<=#10/31/2002# And [Demobilised Date]>=#10/1/2002#,&quot;1&quot;,&quot;0&quot AS Oct, IIf(([Mobilised Date])<=#11/30/2002# And [Demobilised Date]>=#11/1/2002#,&quot;1&quot;,&quot;0&quot AS Nov, IIf(([Mobilised Date])<=#12/31/2002# And [Demobilised Date]>=#12/1/2002#,&quot;1&quot;,&quot;0&quot AS [Dec]
FROM tblAssigned
WHERE (((tblAssigned.[Section ABB])=&quot;74&quot)
WITH OWNERACCESS OPTION;

These reports will probably be getting used after I have left. Therefore is there a way to make the date parameters user input? Or is this some thing they will have to live with?

Thanks for all your help


Cashe

 

[tedlan]

Actually (modifying the SQL from the previous replyer), I think this would be a little more accurate:

SELECT ProjId, EmpId, Sum(iif(#1/31/2002# between StartDt And EndDt,1,0)) AS Jan, Sum(iif(#2/28/2002# between StartDt And EndDt,1,0)) AS Feb, Sum(iif(#3/31/2002# between StartDt And EndDt,1,0)) AS Mar, Sum(iif(#4/30/2002# between StartDt And EndDt,1,0)) AS Apr, Sum(iif(#5/31/2002# between StartDt And EndDt,1,0)) AS May, Sum(iif(#6/30/2002# between StartDt And EndDt,1,0)) AS Jun, Sum(iif(#7/31/2002# between StartDt And EndDt,1,0)) AS Jul, Sum(iif(#8/31/2002# between StartDt And EndDt,1,0)) AS Aug, Sum(iif(#9/30/2002# between StartDt And EndDt,1,0)) AS Sep, Sum(iif(#10/31/2002# between StartDt And EndDt,1,0)) AS Oct, Sum(iif(#11/30/2002# between StartDt And EndDt,1,0)) AS Nov, Sum(iif(#12/31/2002# between StartDt And EndDt,1,0)) AS Dec
FROM tblLeaves
WHERE (Year(StartDt])<=2002 or Year(EndDt)>=2002) and EndDt > StartDt
GROUP BY ProjId, Empld
WITH OWNERACCESS OPTION;

 

[allanon]

I am not sure if you wil like this answer BUT here is what I tried and it works good.


1) create a form. On this form place a textbox called txtYear and a button called whatever you like.

2) create a query for now called Query2. In this query have something like SELECT * FROM tblAssigned

3) in the click event for the button place this code:

    Dim qry             As DAO.QueryDef
    Dim strSQL          As String
        
    Set qry = CurrentDb.QueryDefs("Query2")
    
    
    strSQL = "SELECT tbl_0050013.firm, "
    strSQL = strSQL & "tbl_0050013.claim, "
    strSQL = strSQL & "tbl_0050013.amount, "
    strSQL = strSQL & "tbl_0050013.batch_date, "
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#1/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/02/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#1/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Jan, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#2/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/03/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#2/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Feb, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#3/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/04/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#3/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Mar, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#4/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/05/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#4/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Apr, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#5/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/06/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#5/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS May, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#6/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/07/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#6/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Jun, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#7/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/08/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#7/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Jul, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#8/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/09/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#8/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Aug, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#9/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/10/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#9/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Sep, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#10/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/11/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#10/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Oct, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#11/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/12/&quot; & Me.txtYear))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#11/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Nov, &quot;
    strSQL = strSQL & &quot;IIf((tbl_0050013.batch_date)<=#12/&quot;
    strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/01/&quot; & CStr(CInt(Me.txtYear) + 1)))), &quot;00&quot;) & &quot;/&quot; & Me.txtYear & &quot;# &quot;
    strSQL = strSQL & &quot;And tbl_0050013.batch_date>=#12/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot;) AS Dec &quot;
    strSQL = strSQL & &quot;FROM tbl_0050013; &quot;
    
    qry.SQL = strSQL
    qry.Close

you will have to modify this code to select your fields from your table. ie: wherever you see tbl_0050013.batch_date you will replace that with your dates. please note that I only had one date to work with. be careful to replace the dates in the correct spots. I will do the first iif for you here:

strSQL = strSQL & &quot;IIf(([Mobilised Date])<=#1/&quot;
strSQL = strSQL & Format(DatePart(&quot;d&quot;, DateAdd(&quot;d&quot;, -1, CDate(&quot;01/02/&quot; & Me.txtYear))), &quot;00&quot & &quot;/&quot; & Me.txtYear & &quot;# &quot;
strSQL = strSQL & &quot;And [Demobilised Date]>=#1/01/&quot; & txtYear & &quot;#,&quot;&quot;1&quot;&quot;,&quot;&quot;0&quot;&quot AS Jan, &quot;


What will happen here is that you have the user enter the year. Click the button. The query2 gets created with the proper year. You do not have to worry about leap years or anything else with this. After query2 is created you can use it for whatever you want.

Let me know if you need more help.

 

[allanon]

I just noticed the section thing. Just use another textbox and add that in the strSQL variable in the where clause.