Ally:
As promised I'm addressing your detailed questions below:
1. I think I am a little confused about the way of updating tables (adding
non-existing degree in Degrees table, non-existing SubjectArea in
SubjectAreas table, and corresponding row in the DegreeSubjectAreas table).
Yes, I am going to insert as many rows as I can think of into the Degrees,
SubjectAreas, and DegreeSubject Areas tables at the outset so that people who
later help me enter the data we collects from more than 200 employees will be
able to select a Degree/SubjectArea combination from the combo box in the
Employee form (including EmployeeName, Educations, Position, Certifications,
ProfessionalOrgnizations, etc. ) when adding/updating an employee's
information. However, it would be pretty hard for people who are iliterate
with any database or any computer softwares to switch from Access application
(Employee from) to Access Object screen to find correct tables(tblDegrees,
tblSubjectAreas and tblDegreeSubjectAreas) and update each or part of these
three tables based on different situation whenever she/he encounters a new
degree, or new subject or new both that an employee has. Because if these
tables are not correctly updated, you would not be able to find the right
combination to choose from out of the combo box in the Employee form. The
more difficult task for data entry people is to how and when to update which
tables at any given situation when they cannnot find any right combination
from the Employee form. I am not sure whether this is what you suggest to do?
I may misunderstand of what you mean...or do you mean the No. 2 below? Please
get back to me...?
So far we've mainly talked about the logical model, i.e. the tables which
represent the real world entities and how they relate to each other. That's
the key to a successful database, but the users need to interface with that
model in such a way that all the technicalities are hidden. That's where the
other part of good database development comes into play, designing an
interface, of which forms are the principle tool, so that the user can
undertake the necessary tasks of updating and querying the database
painlessly. So when a user comes across a degree/subject area combination
which is not already represented in the database they can add it by following
a simple and intuitive sequence of events. This will involve inserting new
rows into at least one table, at most three, but whichever is the case the
user will be able to do what is necessary without needing to know what is
happening under the hood. They simply interact with controls on forms.
2. You mentioned in your first reply that "To insert rows into the
DegreeSubjectAreas table you could h ave a subform based on the table
(DegreeSubjectAreas) in a parent form based on the Degrees table... The
subform would have a combo box bound to the SubjectAreaID column and listing
all the subject areas fromt he SubjectAreas table, so you simply have to
select from the list when adding rows in the subform. " I am not sure if this
is what you suggest for data entry people to do in the Access application to
adding rows. If so, for data entry people, how do I let them adding rows in
this subform when the combination is not listed? Do they need to switch from
the parent from based on Degree table to Access Objects, choose Tables under
the Objects and update each or part of the three tabels (tblDegrees,
tblSubjectAreas, tblDegreeSubjectAreas) mentioned in No. 1 above? If not, do
I need to set up a Command Button in the application for people who are using
it to updating/editing/adding new Degrees/SujbectAreas/DegreeSubjectAreas?
No, they don't go anywhere the database window; in fact it can be hidden
completely. As I said above, its all dine via controls on forms in a way
that makes it an intuitive process for the user. In your case I think a
button in the subform would be the best solution, though it can also be done
via an event of the combo box on the subform; the DblClick event is used
commonly to add new items not in a combo box's list in situations where it
can't be dome simply by typing in the new item, as in you case where the item
is a combination of two values, the degree and the subject area. What I
often do is put a small button beside the combo box and give it a 'tool tip'
so that when the mouse pointer is over it a little message appears saying
'Click to add new degree/subject area'.
3. In your second reply, you mentioned in your second reply that "There are
other refinements you can include in your forms such as adding a new
degree/subject area in the Employees form if the one you want is not already
in the list. This involves opening the Degree form so that you can a degree
record, or select an existing one and add a new subject area to it. In the
latter case you can do this directly in the combo box on the subform by
typing in the new subject area; this requires a little bit of code to be
written, however, so I'd leave that until later when everything else is
working satisfactorily." Sound like this is really what I was thinking about
initially, but requiring writing codes which I don't know how. So If I cannot
adding/updating a new subject area directly from Employee form as you
mentioned, what is another way (or easier way) to do it? I know you also
mentioned that "You'd also have a form based on the SubjectAreas table (or
again a sorted query would be better) for adding new subject areas or editing
existing one." How do I switch from the Employee form when I am not seeing
the Degree and SubjectArea combination listed from the combo box
cboDegreeSubjectAreaID to add new Degree in table Degrees, add new
SubjectArea in table SubjectAreas and add Degree/SubjectAreas combination in
DegreeSubjectAreas table? Do I need to set up a three command button like
Update Degree, Update SubjectAreas, Update Degree/SubjectArea somewhere in
the mainmenu of the application? But it seems very bulky and silly, Could you
please provide more explanations on this?
No, you'd just have the one button as described in my answer to 2 above.
The form this takes you to would then allow the user to select or add a new
degree and or subject area as necessary. In this case as both are just
single column items adding a new degree or subject area is simply a case of
typing it into the relevant combo box.
4. As matter of fact, your promotion of thinking about recording all degrees
that applied is my reality I need to face because I just found that some
people have earned more than one master degree for examle MBA & MHA, or MHA &
MRM, sometimes, MBA and Ph D in Physics. I understand there is a need to set
up the many-to-many relationship betwen employees and DegreeSubjectAreas. How
do I record both Master Degrees and count the highest degree as the MS, for
the latter case, record MBA and PHD in Physics and count the hightest degree
as the PH D since I need cound the number of peple in each type for highest
degree. Do I still need to the combox cboDegreeSubjectAreadID in my Employee
form? I don't think I understand you saying haveing an academic
qualifications subform (based on new table) instead in the Employee form.
Would you please elaberate a little more? Also I don't know what's the
purpose to keep the DateAwarded in the new table. Highest degree doesn't
necessarily mean the latest degree a person earns, right? because she/he can
earn anther MS degree say MBA after she/he has earned a PHD in Physics.
The concept of 'highest degree' is not something inherent in the data, e.g.
its not necessarily the last awarded degree as you say, so it cannot be
computed from the degree data for an employee. Rather it’s a matter of
choice for the user based on a 'business rule'. This means it has to be
indicated by another column (field) in the table which models the
many-to-many relationship between Employees and DegreeSubjectAreas; lets call
this table EmployeeAcademicQualifications. This could be a Boolean (Yes/No)
column and in the subform would be the ControlSource of a check box. Some
validation code would need to be written so that a user can only set one
'Principal Qualification' per employee, prompting the user to say whether
they want any existing 'Principal Qualification' to be replaced by the new
one. This is easily done with some code in the combo box's BeforeUpdate
event procedure.
5. How do you set up a foreign key in a table?
A foreign key is simply a column in one table (the referencing table) which
references the primary key column of another table (The referenced table); it
can also be multiple columns in some situations. Often a primary key is an
autonumber, but the corresponding foreign key in a referencing must not be an
autonumber but a straightforward long integer number data type. This is
because foreign key values are not unique, e.g. you might have a CustomerID
autonumber primary key of a customers table and a long integer CustomerID in
an Orders table; there might be several orders per customer so their
CustomerID would be repeated in several rows of Orders. You don't define a
foreign key in the table design, but by setting up a relationship between the
tables via the Relationship Window. When you do so you can, and usually
will, enforce referential integrity. This means you can't insert a row into
the referencing table until a matching row exists in the referenced table,
e.g. you can't insert an Orders row for a customer who does not yet exist in
Customers. You can also enforce 'cascade updates' which means that if you
change a primary key value all the foreign key values which reference it in
other tables are automatically changed. If the primary key is an autonumber
this is not necessary as an autonumber value can't be changed. Also you can
enforce 'cascade deletions'; this means if you delete a row from a referenced
table all rows in the other table which reference that row will automatically
deleted. You need to think carefully before doing this; in many cases you
will simply want to enforce referential integrity as this means that a row in
a referenced table cannot be deleted if a matching row exists in a
referencing table, e.g. I would not be able to delete the row for San
Francisco in a Cities table if I had customers in San Francisco in a
Customers table (which I do have, as it happens). If I had no san Francisco
customers, however, I'd be able to delete the row for that city from the
Cities table. Enforcing cascade deletions would not be appropriate here but
might be in the case of the relationship between Customers and Orders, as if
I choose to delete a customer from the database I would almost certainly want
to delete their order records too, as they would be meaningless without a
customer to go with them.
You should find that all I've said above is implemented in the demo file
I'll send you. It sounds complex and like a lot of work when written out,
but its actually very straightforward to do, so I'm sure it will be a lot
clearer when you see the demo database. I'll include an explanation specific
to what I do in the demo in my email when I send it to you.
Ken Sheridan
Stafford, England
