Let's start with a few basic principles. I said in my last post that
normalization is defined by functional dependencies. A column (I'll use
this more correct term rather than field from now on) is functionally
dependent on another column if the second column's value is determined by the
first. This means that for every time that a particular value of column 1
appears, which might be in the result set of a query, on a form etc., the
value of column 2 is the same. Non-key columns must be functionally
dependent on the key, e.g. in your example Title, FirstName, Surname Add1,
Add2 etc are all functionally dependent on the key MembID.
If we look at the other columns in this table then, DeptNo for example, is
also functionally dependent on MembID, assuming that each Member is in only
one Department. The Dept column, however, is functionally dependent on
DeptNo, and as this is functionally dependent on the key MembID it is said to
be Transitively Functionally Dependent. A transitive functional dependency
means that the table is not properly normalized, and there is the possibility
of update anomalies; it would be perfectly feasible for another row
incorrectly to have a different Department value for the same DeptNo.
Consequently the table should contain only the DeptNo column as a foreign
key, with all the other columns representing attributes of the Department
moved to another table; this is called decomposing the table.
Once you have moved the relevant columns to a Departments table that itself
will need decomposing, so this process should be undertaken on paper first
before actually splitting up the tables in the database. There will be many
Universities with Accountancy departments no doubt, so the Departments table
should include a Uni column as a foreign key (or more likely a numeric UniID
column) referencing the primary key of a Universities table which will in
turn have columns representing the attributes of the universities. Other
columns in the Departments table would represent attributes of the
departments, which as well as DeptNo (the key) and Department will include
address columns as university department in my experience can have far flung
locations rather than using a single address of the university per se.
I hope by now you can see how this process of decomposition to achieve
normalization works; its essentially one of applied common sense, and boils
down to identifying the non-transitive functional dependencies, i.e. what
columns are solely determined by the table's key. A useful way of
remembering the rules of normalization to Third Normal Form was once given by
John Vinson; 'the key, the whole key and nothing but the key, so help me
Codd'. Edward Codd was the de facto inventor of the database relational
model in his papers of around 1970 while at IBM. What this means is that
every non key column should be determined by the primary key alone and the
whole of the primary key where this comprises more than one column, so that
for any value of the primary key the values of the non-key columns will
always be known.
I think you might be getting confused as regards duplicated values.
Duplicated values in a column do not necessarily constitute redundancy.
Duplication would only imply redundancy where the values are not functionally
dependent on the key. So having duplicate values of DeptNo in the Members
table does not imply redundancy, it merely means that more than one member
comes from the same department. Duplication of values in the Uni column in
that table would, however, as that column is not solely determined by the key
MembNo, but also by the non-key column DeptNo, which is why all the
University data needs to be moved out to a different table and referenced by
a UniID foreign key in the Departments table.
Also don't assume that the duplication of a value means that value refers to
the same thing. The Accountancy department at Trinity College, Dublin is not
the same thing as the Accountancy department at University College, Dublin
(if it has one), so the Departments table would have more than one row with
'Accountancy' as the Department value, but with different DeptNo key values
and different UniID foreign key values. Whichever Accountancy department the
member was in would be represented by its DeptNo in the relevant row of the
Members table.
Now lets deal with your specific questions which are not covered by the
general principles outlined above:
You can do this with the StrConv function in an UPDATE query, e.g.
UPDATE MyTable
SET MyField = StrConv(MyField,3);
The value of 3 for the second argument is the value of the vbProperCase
constant, but you can't use constants in a query, only in VBA, so you must
use its literal numeric value here.
My view would be that hyphenated names should be in a single column, but
separate first and second names are better stored in separate columns in the
table. Even with names in one column the StrConv would work as it operates
on each name separately, but with some names you would have to do some manual
editing, e.g. Victoria de Los Angeles and names such as MacDonald or O'Reilly.
Firstly data should only be entered/edited via forms, not in raw datasheet
view. In a form you can use the KeyPress event procedure of a control to
convert the characters to lower case as typed in:
KeyAscii = Asc(LCase(Chr(KeyAscii)))
I'm afraid, like John, I could only look at your file on a commercial
consulting basis.
