Personnel Strength based on Arrival/Departure Date 1

[jon6035]

I have personnel database. I need to count strength in increments today, 30, 60, 90, 180

These are the fields that I think are important to the question:
PKPersonnel
ArrivalDate
DepartureDate
Service (Values would be USMC, USN, USA)

I need to select everyone that is Mustered (it's a checkbox) or will arrive in the future. (this part seems pretty easy.)

I think it could be an IIF Statement(grouping them into categories) so that I could sum them later.

I could also stack up the different groups into a UNION query. Todays Total, Those personnel that will still be here at each increment.

In the end, I would have results that look like this:
Service Total Available
USMC 23 21
USN 12 10

Further on, I would need to break it out between Officer/Enlisted and then again by MOS or skill, that's why I think if I just get the Personnel Key's and the totals in the increments, I can use other queries/reports to get greater fidelity.

Hopefully it makes sense. I found this old thread (thread701-770674) and can't seem to translate it to my needs. I have read Allen Browne's page on Sub Queries, and it seems like it is the answer, the thread referenced uses it, but I can't seem to crack it.

 

[MajP]

This is not totally clear to me. How do you use the dates field? Are they actual dates that only get filled in when the person arrives and leaves, or are they planned arrival and departure dates? Not sure how you are calculating the total and available. Explain the calculation as if you were doing it by hand, that would help determining how to do it in a query. So without knowing, my guess would be to to calculate the on hand strength 30 days out I would determine all personnel whose arrival date is earlier than today+30 and departure date is greater than today + 30. Is that the correct logic? Not sure what the difference from available and the total is.

 

[jon6035]

They are dates that are filled in when a person arrives, and is scheduled to depart. My main concern is the "available", the total is meant to be authorized strength, i.e. that I have 23 Marines, and the on hand reports using the Arrival/Departure how many I actually have. The total will be calculated in a separate query.

Your logic is correct, in my opinion.

I do realize my question is improperly written here, but you question sparked an idea.

I will set an IIF statement in the 2nd query to flag 1 for arrivals/on hand in that period, -1 for departures. I will stack up a union query, and the report will do the totals, once I add in the totals through ELookUp on the report.

r/s,

jon

 

[MajP]

Sounds overly complicated.

I would build a small function to simplify things

Public Function GetDaysOut(NumberDays As Integer) As Date
  GetDaysOut = DateAdd("d", NumberDays, Date)
End Function

You pass in a number of days and it returns the days out from today.

Then if you data is like this

PKPersonnel	ArrivalDate	DepartureDate	Service
1               8/26/2015	8/29/2015	USA
2               8/26/2015	8/29/2015	USMC
3               8/26/2015	9/30/2015	USAF
4               8/26/2015	9/30/2015	USN
5               8/26/2015	10/30/2015	USMC
6               8/26/2015	10/30/2015	USN
7               8/26/2015	10/30/2015	USN
9               8/1/2015	8/26/2015	USMC
10              8/29/2015	9/30/2015	USMC

You could build simple queries.

So for today it would be

SELECT 
 Count(tblPersonnel.PKPersonnel) AS CountOfPKPersonnel, 
 tblPersonnel.Service
FROM 
  tblPersonnel
WHERE 
  tblPersonnel.ArrivalDate < GetDaysOut(0) 
  AND 
  tblPersonnel.DepartureDate > getDaysOut(0)
GROUP 
 BY tblPersonnel.Service
ORDER 
 BY tblPersonnel.Service;

and 30 days out is simply changing the value of the function parameter

SELECT 
 Count(tblPersonnel.PKPersonnel) AS CountOfPKPersonnel, 
 tblPersonnel.Service
FROM 
  tblPersonnel
WHERE 
  tblPersonnel.ArrivalDate < GetDaysOut(30) 
  AND 
  tblPersonnel.DepartureDate > getDaysOut(30)
GROUP 
 BY tblPersonnel.Service
ORDER 
 BY tblPersonnel.Service;

so running today I get

CountOfPKPersonnel	Service
1	USA
1	USAF
2	USMC
3	USN

And for 30 I get

CountOfPKPersonnel	Service
1	USAF
2	USMC
3	USN

and for 60 I get

CountOfPKPersonnel	Service
1	USMC
2	USN

I believe all the counts are correct. Please double check. If you notice I have Marine 9 that left yesterday and is not in today's count. Also Marine 10 has not arrived yet but will be here 30 days out and shows up in the 30day on hand.

 

[Andrzejek]

It would be nice to know the data jon6035 has.

"I need to select everyone that is Mustered (it's a checkbox)"

[pre]
PKPersonnel ArrivalDate DepartureDate Service [red] Mustered[/red]
1 8/26/2015 8/29/2015 USA Yes
2 8/26/2015 8/29/2015 USMC Yes
3 8/26/2015 9/30/2015 USAF No
4 8/26/2015 9/30/2015 USN Yes
5 8/26/2015 10/30/2015 USMC No
6 8/26/2015 10/30/2015 USN Yes
7 8/26/2015 10/30/2015 USN Yes
9 8/1/2015 8/26/2015 USMC No
10 8/29/2015 9/30/2015 USMC Yes
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[jon6035]

MajP, Thanks for the function. That seems like it will do it. It looks like I will be able to reuse one query multiple ways.

Andy, the data exists on a closed network. However, I do see that I was lazy in my initial post. Your interpretation of my data is correct. I have people that are Mustered, but I have inbounds in the same table, that aren't mustered, and people that have left. They are all separated by a "CompanyLU" number value field. So to expand and try to learn, and I should probably change my level to novice.

[pre]
PKPersonnel ArrivalDate DepartureDate Service Mustered CompanyLU MOSLU
1 8/26/2015 8/29/2015 USA Yes 1 45
2 8/26/2015 8/29/2015 USMC Yes 1 45
3 8/26/2015 9/30/2015 USAF No 1 45
4 8/26/2015 9/30/2015 USN Yes 2 23
5 8/26/2015 10/30/2015 USMC No 3 23
6 8/26/2015 10/30/2015 USN Yes 3 23
7 8/26/2015 10/30/2015 USN Yes 3 23
9 8/1/2015 8/26/2015 USMC No 3 23
10 8/29/2015 9/30/2015 USMC Yes 3 23

[/pre]
I use a query as the source for this set of questions, so I can limit my results first. Eventually, it will be easily limited based a billet number being assigned.

Thank you for all the help, and I will let you know how it goes.

 

[Andrzejek]

jon6035,

Do you see how MajP showed your data? And how my example was formatted?

Please consider using TGML tags to show your data the same way - a LOT easier to see.

[ignore][pre][/ignore]
[pre]
PKPersonnel ArrivalDate DepartureDate Service Mustered CompanyLU MOSLU
1 8/26/2015 8/29/2015 USA Yes 1 45
2 8/26/2015 8/29/2015 USMC Yes 1 45
3 8/26/2015 9/30/2015 USAF No 1 45
4 8/26/2015 9/30/2015 USN Yes 2 23
5 8/26/2015 10/30/2015 USMC No 3 23
6 8/26/2015 10/30/2015 USN Yes 3 23
7 8/26/2015 10/30/2015 USN Yes 3 23
9 8/1/2015 8/26/2015 USMC No 3 23
10 8/29/2015 9/30/2015 USMC Yes 3 23
[/pre][ignore][/pre][/ignore]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.