INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Personnel Strength based on Arrival/Departure Date

Personnel Strength based on Arrival/Departure Date

(OP)
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: Strength List) 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.




RE: Personnel Strength based on Arrival/Departure Date

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.

RE: Personnel Strength based on Arrival/Departure Date

(OP)
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

RE: Personnel Strength based on Arrival/Departure Date

Sounds overly complicated.

I would build a small function to simplify things

CODE

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

CODE

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

CODE

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

CODE

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

CODE

CountOfPKPersonnel	Service
1	USA
1	USAF
2	USMC
3	USN 

And for 30 I get

CODE

CountOfPKPersonnel	Service
1	USAF
2	USMC
3	USN 
and for 60 I get

CODE

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.

RE: Personnel Strength based on Arrival/Departure Date

It would be nice to know the data jon6035 has.

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

PKPersonnel    ArrivalDate DepartureDate   Service     Mustered
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
 

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.

RE: Personnel Strength based on Arrival/Departure Date

(OP)
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.

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

 
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.

RE: Personnel Strength based on Arrival/Departure Date

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. smile

[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]

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close