Manning Database

[pompeyboyUSA]

To be honest I am not sure if this is possible, I have a plan that I want to
produce, but cannot seem to get it to work.
Here goes....
I work for the Military (so apologies if I don't go into too much detail)
and I want to produce a database of manpower for a unit 500 strong - fairly
low key stuff. Each position has a unique number and a Position ID assigned
to it - each person has a unique service number and personal details. There
should only be one person in each of the positions at any one time. People
do come and go, and change positions with promotions/postings etc. I would
like to be able to choose a position, and assign a person into that position
at the click of a mouse (without having to go into the table and cut and
paste) - the idea being auto filling details. I hope that makes sense! I
have built databases before, and would class myself as intermediate. Any
help in the right direction would be very much appreciated.

 

[Dirk Goldgar]

To be honest I am not sure if this is possible, I have a plan that I want
to
produce, but cannot seem to get it to work.
Here goes....
I work for the Military (so apologies if I don't go into too much detail)
and I want to produce a database of manpower for a unit 500 strong -
fairly
low key stuff. Each position has a unique number and a Position ID
assigned
to it - each person has a unique service number and personal details.
There
should only be one person in each of the positions at any one time.
People
do come and go, and change positions with promotions/postings etc. I
would
like to be able to choose a position, and assign a person into that
position
at the click of a mouse (without having to go into the table and cut and
paste) - the idea being auto filling details. I hope that makes sense! I
have built databases before, and would class myself as intermediate. Any
help in the right direction would be very much appreciated.

This sounds pretty straightforward in terms of the data design. The user
interface for editing the information could be made simple or fancy.

From the sound of it, you need two tables:

People (or Soldiers)
----------------------------
ServiceNumber (primary key)
(other details of the person)


Postions
------------
PositionNumber (primary key?)
PositionID (primary key?)
(other details of the position)
StaffedBy (relates to ServiceNumber)


I'm not sure what's the difference between PositionNumber and PositionID,
but presumably one of these will be the primary key of the Positions table.

The two tables are related 1-to-many (1 person to potentially many
positions) on the ServiceNumber/StaffedBy fields. You could, incidentally,
just call the field "ServiceNumber" in both tables; I just thought
"StaffedBy" expressed the relationship better.

You can build a query that left-joins the Positions table to the People
table on the StaffedBy/ServiceNumber fields, including all the records from
Positions and only the matching records from People. The query would
include all the relevant fields from both tables. If you base a form on
this query, then selecting a different value for StaffedBy will auto-lookup
and display the details of the new person you've put into that position.

For basic staff assignments, I'd suggest using a combo box or bound to the
StaffedBy field. The combo box's RowSource property would be a query based
on the People table. You can set up the combo box so that it shows the
person's name but stores the service number, if you like, or you can show
the service number -- that all depends on what information you have in hand
when you go to make the assignment.

Another alternative is to use a list box instead of a combo box. You don't
have *too* many people to list in the box, though of course you won't be
able to see them all in the list at one time.

 

[Jerry Whittle]

If the business rule is that an Airman (or soldier, sailor, Marine) can only
have one, and only one Position AND a Position can only have one Airman, you
can make this happen with one form using a combo box. One the form for the
Position create a combo box that uses the Airman table as the Row Source.
Store the Service Number primary key for the Airman in that field. Next put
unbound fields on the form which you want to see from the Airman table. In
the record source for these field use something like:

=DLookUp("[First_Name]","Airmen","[PK_Field] =" &
[Forms]![FormName]![cboServiceNumber])

That will fill in all the info. Of course you won't be able to change any of
the Airman information such as Rank from that form. You need to go to a form
based on the Airman table for that.

Also there's the not so little problem of moving a person from on position
to another or trying to assign the same person to more than one position. The
Service Number field in the Position table would need to be a unique index to
prevent the same Airman being added to the table more than once. Of course
this will throw up an error message only after you try to leave that record
or close up the form. Not the best. You could work the combo box row source
to exclude people already in the Position table through a sub-query.

 

[pompeyboyUSA]

This sounds pretty straightforward in terms of the data design. The user
interface for editing the information could be made simple or fancy.

From the sound of it, you need two tables:

People (or Soldiers)
----------------------------
ServiceNumber (primary key)
(other details of the person)


Postions
------------
PositionNumber (primary key?)
PositionID (primary key?)
(other details of the position)
StaffedBy (relates to ServiceNumber)


I'm not sure what's the difference between PositionNumber and PositionID,
but presumably one of these will be the primary key of the Positions table.

The two tables are related 1-to-many (1 person to potentially many
positions) on the ServiceNumber/StaffedBy fields. You could, incidentally,
just call the field "ServiceNumber" in both tables; I just thought
"StaffedBy" expressed the relationship better.

You can build a query that left-joins the Positions table to the People
table on the StaffedBy/ServiceNumber fields, including all the records from
Positions and only the matching records from People. The query would
include all the relevant fields from both tables. If you base a form on
this query, then selecting a different value for StaffedBy will auto-lookup
and display the details of the new person you've put into that position.

For basic staff assignments, I'd suggest using a combo box or bound to the
StaffedBy field. The combo box's RowSource property would be a query based
on the People table. You can set up the combo box so that it shows the
person's name but stores the service number, if you like, or you can show
the service number -- that all depends on what information you have in hand
when you go to make the assignment.

Another alternative is to use a list box instead of a combo box. You don't
have *too* many people to list in the box, though of course you won't be
able to see them all in the list at one time.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thank you Dirk, and Jerry. Now I am getting somewhere! I have been trying
to get this one going for days, and ended up chasing my tail and confusing
myself! Now I feel I am on the right track.

I have manged to be able to link the two tables (Post Details and Soldier
details), I have produced the query and the form, but I cannot seem to enter
the service number via the list box - I get the dink sound and am unable to
enter anything - is that to do with the form being based on a query??

BTW the position Number and ID - The Position Number is the Primary Key, the
Position ID is just the name of the position - sorry for any confusion.

Jerry, I am in the British Army (was based in DC for 3 years - hence the
name) - not sure if you still want to help - LOL

 

[Dirk Goldgar]

Thank you Dirk, and Jerry. Now I am getting somewhere! I have been
trying
to get this one going for days, and ended up chasing my tail and confusing
myself! Now I feel I am on the right track.

I have manged to be able to link the two tables (Post Details and Soldier
details), I have produced the query and the form, but I cannot seem to
enter
the service number via the list box - I get the dink sound and am unable
to
enter anything - is that to do with the form being based on a query??

BTW the position Number and ID - The Position Number is the Primary Key,
the
Position ID is just the name of the position - sorry for any confusion.

It sounds like either your query is not updatable, or you've bound the combo
box to the wrong field. Open the query in datasheet view and see if there's
a blank row at the bottom for adding new records. If not, the query isn't
updatable.

If the query isn't updatable, make sure that (a) the linking fields are both
indexed, and (b) both tables have a primary key. Also make sure that the
query's SQL doesn't include the DISTINCT keyword (as in "SELECT DISTINCT
....") and that it doesn't do any grouping (no "GROUP BY" clause). You *may*
also need to explicitly define the relationship between the tables, in the
Relationships window.

If the query is updatable, then make sure that the ControlSource property of
the list box is the StaffedBy or ServiceNumber field from the Post Details
table, not the one from the Soldier table.

If all else fails, please post the SQL of the query on which your form is
based.

 

[pompeyboyUSA]

It sounds like either your query is not updatable, or you've bound the combo
box to the wrong field. Open the query in datasheet view and see if there's
a blank row at the bottom for adding new records. If not, the query isn't
updatable.

If the query isn't updatable, make sure that (a) the linking fields are both
indexed, and (b) both tables have a primary key. Also make sure that the
query's SQL doesn't include the DISTINCT keyword (as in "SELECT DISTINCT
...") and that it doesn't do any grouping (no "GROUP BY" clause). You *may*
also need to explicitly define the relationship between the tables, in the
Relationships window.

If the query is updatable, then make sure that the ControlSource property of
the list box is the StaffedBy or ServiceNumber field from the Post Details
table, not the one from the Soldier table.

If all else fails, please post the SQL of the query on which your form is
based.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk,

Got it to work, the problem was in the relationship, it took me some time
but through trial and error it now seems to work, There is a lot more work
to do - reports to build, would you be willing to guide me when I need it -
carrying on thru this post?
Thank you so much.

 

[Dirk Goldgar]

Got it to work, the problem was in the relationship, it took me some time
but through trial and error it now seems to work, There is a lot more
work
to do - reports to build, would you be willing to guide me when I need
it -
carrying on thru this post?

I'm certainly willing to help you, as time permits, but I think you'd be
better served to start new threads for distinct questions. It's better for
you, because someone else may answer a new question before I can find the
time, and it's better for the Access community because more narrowly defined
discussion topics can be found more easily by a search. Also, you may have
one question that should be posted in the Forms or FormsCoding groups, and
another that should be posted in the Reports group, and so on. Oh, and
don't forget, you may find someone who has a better answer than I might
give.

I'll certainly keep an eye out for your posts, in the groups I frequent.

Thank you so much.

I'm glad to help.