Selecting Data from June Months for each of 6 Years

[Curtis A]

Task: I am trying to figure how many individuals were assigned in June of each of the 6 Years presented. I have the [BegDate] when the individual arrived in our Organization and the [EndDate] of departure. I need to know how many individuals by the Service category were assigned from 1 -30 June 2019; 1-30 June 2020, etc. I have 152 individuals that are still currently assigned [CurrentYN], though their [BegDate] varies. The other 87 individuals are no longer assigned; however, I do need to know if they were assigned during any of the individual June months. I just need a total number by Service of folks we had on the books during those specific June months.



I am questioning if the below formula is correct for June 2019?
SELECT Service.ServiceID, [DTHC Employees].ServiceID, [DTHC Employees].BegDate, [DTHC Employees].EndDate, IIf([BegDate] Between #7/20/2005# And #6/30/2019# And [EndDate]>#6/30/2019#,1,0) AS Jun2019
FROM Service INNER JOIN [DTHC Employees] ON (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID);

I am questioning if the below formula is correct for June 2020?
SELECT Service.ServiceID, [DTHC Employees].ServiceID, [DTHC Employees].BegDate, [DTHC Employees].EndDate, IIf([BegDate] Between #7/20/2005# And #6/30/2020# And [EndDate]>#6/30/2020#,1,0) AS Jun2020
FROM Service INNER JOIN [DTHC Employees] ON (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID);

Thank you much for any corrections. Curtis

[kd2017]

Maybe something like this?

SELECT fields
FROM table
WHERE BegDate <= #6/30/2019# AND (EndDate Is Null OR EndDate >= #6/1/2019#)

This where clause should select any date window (BegDate to EndDate) that overlaps any part of June 2019. This assumes those still currently assigned will have an EndDate value of Null and select those too.

I'm not sure what 7/20/2005 is?

[madpiet]

Easiest way is to get the Year and the Month for each record, and then you can just group it and it's super easy:

SELECT Year([BegDate]) AS BegYear, Month([BegDate]) AS BeginMonth, Count([DTHC Employees].EmployeeID) AS CountOfEmployeeID
FROM [DTHC Employees]
WHERE Month([BegDate])=6
GROUP BY Year([BegDate]), Month([BegDate])

[kd2017]

Easiest way is to get the Year and the Month for each record, and then you can just group it and it's super easy:

SELECT Year([BegDate]) AS BegYear, Month([BegDate]) AS BeginMonth, Count([DTHC Employees].EmployeeID) AS CountOfEmployeeID
FROM [DTHC Employees]
WHERE Month([BegDate])=6
GROUP BY Year([BegDate]), Month([BegDate])

That will only select records with a beginning date in June. Unless I misunderstood the problem I don't think that's exactly what OP is after.

[Welshgasman]

That will only select records with a beginning date in June. Unless I misunderstood the problem I don't think that's exactly what OP is after.

That is what I believe the O/P is after?

Not sure what this is about?

Between #7/20/2005# And #6/30/2019# And [EndDate]>#6/30/2019#,1,0) AS Jun2019

and the 2020 code will include the 2019 data, would it not if they were still working there?

What has July 20th 2005 have to do with anything?

[kd2017]

That is what I believe the O/P is after?

In other words: Given a team assignment beginning date and ending date how does one select all the team members who were present in June? If John Doe started on the team in April and left the team in July then he was must have been on the team during June. How do you select John Doe?

WHERE BegDate <= #6/30/2019# AND (EndDate Is Null OR EndDate >= #6/1/2019#)

That was my interpretation. Of course I acknowledge I could have misunderstood myself.

[CJ_London]

up to the OP to clarify what 'assigned' means in this statement

I am trying to figure how many individuals were assigned in June of each of the 6 Years presented.

does it mean started an assignment? or does in mean was in assignment for the whole of the month? or does it mean was in assignment for all or some of the month?

this statement

just need a total number by Service of folks we had on the books during those specific June months.

implies it is the second or third of those interpretations

This in my suggestion for the last interpretation

PARAMETERS [enter latest year] Short;
TRANSFORM Count([DTHC Employees].EmployeeID) AS CountOfEmployeeID
SELECT Service.Service
FROM [counter], Service INNER JOIN [DTHC Employees] ON (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID) AND (Service.ServiceID = [DTHC Employees].ServiceID)
WHERE ((([DTHC Employees].BegDate)<=CDate([enter latest year]-[ctr] & "-06-30")) AND (([DTHC Employees].EndDate) Is Null Or ([DTHC Employees].EndDate)<=CDate([enter latest year]-[ctr] & "-06-01")))
GROUP BY Service.Service
ORDER BY Service.Service, [enter latest year]-[ctr]
PIVOT [enter latest year]-[ctr];

It has a parameter to enter the last year required (which could be got from a form). There is counter table with values 0-5 (for the 6 years and could be extended for more years) and the month is hard coded, but could be changed.

it returns this result from the data provided

Service	2015	2016	2017	2018	2019	2020
Air Force			1	3	4	6
Army	7	9	9	9	9	12
CEHS Civilians						2
DTHC Civilians	6	6	6	6	6	10
Navy	1	1	2	2	2	4
PERS						1

see attachment - query is called 'qryJune'

when executed it prompts for the latest year - in this case 2020

[Curtis A]

Sorry for the delay in responding; I don’t have access during the day. 1^st Note: Thank you for those that posted. 1.) The question reference 7/20/2005: My db has been redesigned several times over the years and there are ind’s that have been assigned here prior to this our Reporting Period and still are assigned; so they are included in my data. If you look at the [BegDate] and those having an [EndDate] of 7/14/2024, 97 have been assigned prior to 6/30/2019 and are still on the books; I myself have been assigned since 10.7.1996. 2.) Clarification: My task is to know how many individuals were on duty anytime during 1-30 June 2019; if their [EndDate] was 7/1/2019 or later, they were not assigned; they don’t meet the criteria. The same would apply to the period 1-30 June 2020; anyone assigned during this period, if their [EndDate] is after 6/30/2020, they would not be considered in this time period. And so forth. Yes, some individuals will be included in each of the 6 year’s “1-30 June” reporting period due to their [BegDate] and [EndDate].
In the new Version (V2) of the db attached; I was used “kd2017” suggestion with June2019, using the “Where” sql below. (all queries in my new Version (V2) db pertaining to that post are named “qrykd2017….” with the grand Summary for each of those individual 6 Years “qrykd2017 June2019-June2024 Sum”. To challenge a test, I then manually entered the data to each of the 6 year [fields] to the tbl “DTHC Employees” in my attached new version (V2) db, based on selecting those ind’s having been assigned during the specified “[BegDate] and [EndDate]” periods; then did a group query, titled “qryJune2019-June2024 Sum” which seems to match the Summary query titled “qrykd2017 June2019-June2024 Sum”.
WHERE ((([DTHC Employees].BegDate)<=#6/30/2019#) AND (([DTHC Employees].EndDate) Is Null Or ([DTHC Employees].EndDate)>=#7/1/2019#));
I tried the “qryJune” suggestion from (CJ_London) in the new version (V2) db as attached, and am not getting the thought or results I need. Could be my error! I also tried the “qryMadplet’ suggestion from (madplet) in my (V2) db and am also not getting the thought. Hoping my thread to this activity is helpful! See attached (V2) db. Thank you! Curtis.

[CJ_London]

Clarification: My task is to know how many individuals were on duty anytime during 1-30 June 2019; if their [EndDate] was 7/1/2019 or later, they were not assigned; they don’t meet the criteria. The same would apply to the period 1-30 June 2020; anyone assigned during this period, if their [EndDate] is after 6/30/2020, they would not be considered in this time period

unfortunately still not clear to me.

provide an example: This

'if their [EndDate] was 7/1/2019 or later, they were not assigned' (assuming 7/1/2019 is 1st July)

means someone assigned on say 1st Jan 2019 and ended on 1st July 2019 does not meet the criteria for June 2019

similarly this

'The same would apply to the period 1-30 June 2020; anyone assigned during this period, if their [EndDate] is after 6/30/2020, they would not be considered in this time period'

So someone assigned on say 1st Jan 2020 and ended on 30th June 2020 does not meet the criteria for June 2020

but you want 'to know how many individuals were on duty anytime during 1-30 June 2019' - so how do your two examples not meet the criteria?

these are all the permutations of possible assignments periods (ignoring null end dates)

person....Jan...Feb...Mar...Apr....May...June...Ju ly...Aug...Sept
A............[--------------------------------]
B.....................[------------------]
C............................[---------------------------------]
D................................................. .....[--------------------]
E................................................. ..............[--------------]
F................................................. ....[-]

which of these meet your criteria?

Based on what you have said you want, A, C D and F would meet the requirement, but your first example it would only be A and F for 2019 (since C and D have end dates after 1st July) and for 2020 you would only include A, C and F (since D started in June and ended after the end of June.

I've not looked at other responders examples as to how they have interpreted the requirement, but my example is based on what you have said you want (A, C, D & F)

I tried the “qryJune” suggestion from (CJ_London) in the new version (V2) db as attached, and am not getting the thought or results I need.

My end result was based on your summary query - please clarify the actual requirement - from your data - what results do you actually want?