Update Fields in One Table With Data from Another

[Stewart]

I am brand new to Access and know nothing about VBA so I would like to
avoid programming if possible. I have a database with separate tables
representing each of the different positions in a military
court-martial (Accused, Judge, Trial Counsel, Paralegal, Defense
Counsel, etc.). Each table is populated with data for the person
filling that position. No person will be in more than 1 of the tables.
I am using this database to generate over 100 different reports that
mirror the trial documents and have endless combinations of Rank, name,
unit, position...

The one thing all participants have in common is a Pay Grade that
coincides with a military rank. I have a table called tblRank with 3
fields: PayGrade, RankAbbreviated, & RankSpelledOut. The Primary Key
is PayGrade. Each tblPosition has fields representing
PositionPayGrade, PositionRank, PositionRankSpelledOut, none are the
primary key and there are many additional fields. I have a combo box
set up to select the pay grade from tblRank.PayGrade for each position.
Upon selection of the PayGrade on a form, I want Access to update the
tblPosition's PositionRank & PositionRankSpelledOut fields from the
data in tblRank.

I am new to Access and know little about VBA so I would like to avoid
writing VBA programming if possible. Any Help would be greatly
appreciated.

Thanks,
Stewart

 

[Guest]

You do not want to do this. Probably. Let me explain. Normally you would just
link your tblRank to the other tables by Pay Grade instead of storing them in
the other tables. One rule of relational database design is to not store the
same information in more than one place. An E8 in PositionPayGrade field of
the tblPosition table means Senior Master Sergeant (assuming USAF like I
was). You can look it up in tblRank as needed. This is how it 'should' be
done.

But there are exceptions. For example an invoice when you sell something.
You wouldn't want to recompute the prices and product names if you need to
reproduce an invoice 5 years later. In a slightly similar vein, every once in
a while the military rank names get changed. For example about 20 years ago
the USAF changed E4s from Sergeant to Senior Airman. I think that the Army
might have messed around with Specialists too.

However for the most part, military ranks are pretty static. I'd recommend
going with the 'book' answer of linking your tables instead of duplicating
data.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[jahoobob via AccessMonster.com]

Assumming that you will have a pool of personnel to fill the Judge, Trial,
Counsel, etc. I would create one table of the personnel with Name, Rank,
Serial Number (I had to say it that way), Position, etc. Serial Number would
be the primary key and rank would be a foreign key which would correspond to
Rank table that include the info in your PayGrade table. You could then
create queries for each job to be used by combo
I would then have as my main table tblCourtsMartial which would hold each
trial's data - the Judge, Trial Coiunsel, etc. would be stored as Serial
Number. You could then create queries of the personnel table for each job to
be used by combo boxes to enter the data in the main table. If there could
be more than one person filling a position at trial you could create a
separate table to accomodate this. It would be tied to the tblCourtsMartial
by the main table's primary key. You would enter the accused's info directly
into the main table since, hopefully, he/she wouldn't be one of that group.
If you want to maintain the pay grade info historically, you would need to
store that data in the main table.
Hope this helps,
Bob