Based on what you describe, each Roster should be a table.
You should also have a personnel table. This is where the person's ranks
should go. This is why a relational database is different and, for what
you
are describing, probably a better solution.
You will also want to have a Roster/Personnel Junction table. A junction
table is one used to resolve many to many relationships.
For example, in your situation
Each person can be in 0 to many rosters
Each roster can contain 0 to many persons
Relational databases don't like many to many relationships, so you use the
Junction table to resolve them to one to many. A Junction table would
contain the roster name and the person name.
So using queries, you can determine which rosters a person is in and which
persons are in a roster. So now, you have to change his rank only once in
the person table, because that is the only place it is; however, when you
use
a query, report, or form to view the data by roster you include the name
and
rank fields from the person table.
One thing that will help is if you use Autonumber data type fields as the
primary key fields in your table. This helps keep the relationships
consistent in case anything changes. For example, if the name of a roster
changes, you would not have to change the roster name in the junction
table
everywhere it occurs, because you have the two related with autonumber
primary keys. You just change it in the Roster table. If Mary Smith
married
Al Jones, same thing, it will have no effect on the relationships. Here
are
some example tables. I am sure you will need more fields, but it will
give
you a start.
tblPerson
PersonID - Autonumber Primary Key
PERSON_NAME
PERSON_RANK
PERSON_SERIAL_NUMBER
etc.
tblRoster
RosterID - AutoNumber Primary Key
ROSTER_NAME
etc
tblRosterPerson
RosPersID - AutoNumber Primary Key
RosterID - foreign key to tblRoster
PersonID - foreign key to tblPerson
Set it up this way, and you will be much happier in the long run. You
have
a little learning curve ahead of you, but this is way ahead of having the
one
table. The one table approach would be like using a spreadsheet. If you
want to do it like a spreadsheet, use Excel. In either case, when a
person
gets promoted (or busted) you would have to change it a bunch of places.
If
you use the above approach, everything has to be changed only one time.
Now, as you are deciding which table to put fields in, think about what
that
piece of information belongs to. Is it unique to a person or is it unique
to
a Roster?
And as a last bit, if you are considering a new data intem to add, first
decide what it belongs to, (lets say Person) then ask can there be more
than
one for that person. For example, say there are several training classes
a
person can or must complete. You would not make a new field in the person
table for each class, you would create two new tables. A table of all
available training classes and a Junction table to identify which classes
the
person attended and what the result was.
Any time you are unsure or have questions, post to these news groups.
Good Luck.
Annie Fourman said:
Actually, I'm in a military unit, working in the personnel office. When
someone's information changes, for instance, they get promoted, their
rank
needs to be changed on about 15 different documents, such as an Alert
Roster,
Alpha Roster, Awards Roster, Rating Scheme, etc. I figured it would just
be
easier to incorporate all the documents we need as reports in access, so
that
we're only imputting things one time. I've never tried to creat a 'data
base' with excel. Do you think, perhaps that would be a better choice?
Jeff Boyce wrote:
Annie
Theoretically or practically?
You will find that if you try to feed Access "spreadsheets" (data
organized
like you describe), both you and Access will have to work much harder.
If you create well-normalized tables, you'll find that Access has a
variety
of powerful and (relatively) easy-to-use features and functions.
I'm curious, though, why you think Access is the tool you need to use?
Would Excel do the job you need to get done?
Regards
Jeff Boyce
Microsoft Office/Access MVP
New user here! That's my disclaimer for any potentially dumb
questions
that
[quoted text clipped - 10 lines]
I know. Anyway, is there a reason I would want to split my
information up
between tables? Thanks in advance!!
