By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,690 Members | 1,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,690 IT Pros & Developers. It's quick & easy.

How to calculate

P: n/a
I'm in the military. I must create a report to track the number of
personnel assigned.

I have an Authorization table that has a Position. Each of these
positions could be authorized any number of personnel.

Example:
1. Position
2. Speciality
3. Authorized (<-- This is the column that would have more than one)

How do I keep track of the total number assigned to an authorized
position.

The problem I'm facing is when someone enters another person for that
position it creates another authorized position to that field on the
report and thus calculates more authorizations than I really have.

I posted an example of the page where the problem is I hope someone can
help me, it's at armydatabase.com/report.pdf
Thanks

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
dm******@hotmail.com wrote:
Example:
1. Position
2. Speciality
3. Authorized (<-- This is the column that would have more than one)


At first review, these three types of information should probably be in
three separate tables. For example:

The following table lists the various unit positions.

Table: TBL_POSITION
Fields:
POS_PK (an autonumber - make this the primary key)
POS_NAME (required)
POS_DESCRIPTION (a more detailed description, perhaps)

This next table lists the possible specialties, MOS/AOC (I don't know
what these are - when I was a tank officer in the Canadian army, we
referred to this as "MOC classification" for officers, or "trade" for
non-commissioned troops, plus there's also the possibility of
considering civilian) that are the minimum requirement for this
position. You could possibly have this information as part of the
position information in TBL_POSITION, above, but it occurs to me that
differing branches/MOS could have different requirements if the job is
available to more than just one trade/officer classification.

8) For example, it is common knowledge that armoured soldiers are vastly
superior to anyone in any military. A lowly armour 2Lt could do the job
of an infantry brigadier with one hand tied behind his/her back, for
example, so you might want to indicate rank/grade/whatever of whatever
MOC/Classification/Trades that can fill the position, so you'll want to
have a separate table for this:

Table: TBL_POSITION_REQUIREMENTS
Fields:
PRE_PK (autonumber - make the primary key)
PRE_POS_FK (refers to the POS_PK in the TBL_POSITION table above. Index
this, allowing duplicates)
PRE_MOS_AOC_CODE
PRE_MIN_GRADE (refers to rank)
PRE_NOTES

FInally, you need a table of the the personnel who are filling the
position(s).

Table TBL_POSITION_AUTHORIZED
Fields:
POA_PK (autonumber - make the primary key)
POA_POS_FK (refers to the POS_PK in the TBL_POSITION table above. Index
this, allowing duplicates)
POA_MOS_AOC_CODE (the ACTUAL MOS/AOC of the individual occupying the
position. This can be useful for comparing whose over/under qualified
for the job, etc)
POA_GRADE (rank - see notes on POA_MOS_AOC_CODE)
POA_FIRST_NAME
POA_LAST_NAME
POA_OTHER_NAME
POA_DATE_EFFECTIVE
POA_DATE_LEFT (the date the person left this position - this is good to
be able to get a history of people who've occupied the position and by
specifying something like POA_DATE_LEFT = NULL you'll get people who are
currently in this position)
POA_NOTES

The above is how a database should be used. A query bringing these
three tables together will give you the records for the report you want.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #2

P: n/a
OK

I did something a little different. I created a subreport. The main
report will main the authorized, I want to reference the assigned from
the subreport. Take a look at the update I posted, how do I get the
assigened column to reference the assigned number in the subreport?

armydatabase.com/report.pdf

Thanks

Nov 13 '05 #3

P: n/a
dm******@hotmail.com wrote:
OK

I did something a little different. I created a subreport. The main
report will main the authorized, I want to reference the assigned from
the subreport. Take a look at the update I posted, how do I get the
assigened column to reference the assigned number in the subreport?

armydatabase.com/report.pdf

I'm not sure what you've done or what you're asking here por even what
your data structure is. The new report doiesn't seem to have a
subreport; it looks like a report with data grouped by section.

Without the ability to compare to your first one, I can only go by
memory and it seems (thouhg I could be wrong) that the only difference
with the new one is line 334-06 has two personnel assigned to it.

The text box under ASGN, was it throwing an error before?

In any event, I'm not sure what it is you're looking for. My first post
was more of a suggested data structure. Getting back to your original
question, I can answer the question of "How do I keep track of the total
number assigned to an authorized position.", but I really need to know
how you're tracking this data. The normalized, ie, Official Royal
Canadian Armour Corps, method I presented is the best way way to do it,
but if you're not set up that way, it can probably still be done, BUT...
I need to know how the data:

"1. Position
"2. Speciality
"3. Authorized (<-- This is the column that would have more than one)"

is currently set up.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.