Vanesa:
It sounds like you are new to relational database systems, so you might be
best doing a bit of background reading (any good general purpose primer on
Access to start with) and/or some online tutorials (Google for Access +
Tutorial).
The key thing to understand is that relational database management systems
model the real world, or at least the part of it the database is concerned
with, in terms of Entity Types, each of which is represent by a table, whose
columns represent Attributes of the entity type. Tables relate to each other
by means of 'keys', a 'foreign key column' in one table referencing the
'primary key' column (or columns) of another table. Take a look at the
Relationships window in the Northwind database to see how they all fit
together.
Users interface with the database via forms and reports. Forms are the only
way in which data should be entered or edited by users, never directly into a
table in raw datasheet view.
Forms or reports are usually based on Queries. A query can use just one
table or join a number together via the 'keys'. Queries can be designed
visually in query design view, or written in SQL, the underlying language
used for querying.
Behind the scenes are modules containing VBA code. Forms or reports have
their own modules (called class modules) and there are also standard modules
in which procedures or functions can be created. The code can do many
things, manipulating data, opening forms or reports etc. New users often use
Macros in place of code, but macros, while cheap and cheerful, are limited,
so its well worth while learning how to use code; its not as scary as it
looks at first sight!
To put a little more flesh on the bones of the above I'm adding a few of my
stock replies below; firstly on the relational database model itself; then on
'normalization', a very important concept which eliminates 'redundancy' and
prevents inconsistent data being entered (there is some duplication between
these two); and finally some thoughts on books, though these does include
some more advanced ones than you'll want at look at for a while.
1. The database relational model was originally proposed by E F Codd in a
paper in 1970 in the journal 'Communications of the Association for Computing
Machinery'. Since then there has been a vast amount of further theoretical
work, and the relational model has shown itself to be a robust one. Without
going too deeply into the theoretical basis, which can be quite abstract, a
relational database in essence models a part of the real world in terms of
its entity types and the relationship types between them. Note the inclusion
of the word 'type' in both cases here. While its almost always used in the
former case, its often omitted in the latter case. This is a little bit
sloppy but not too important. When one talks about a 'relationship' it
really refers to a relationship value. As an example 'marriage' is a
relationship type, but my being married to my wife Fiona is a relationship
value, represented by our names on the marriage certificate, which is the
physical equivalent of a row in a Marriages table with columns Husband and
Wife, each referencing the primary key of a table People. This is a
many-to-many relationship type (I've been married twice so would be in two
rows, my first wife would also be in two rows as she remarried too). It is
resolved into two one-to-many relationship types, People to Marriages in each
case, in one case via the Husband column in the other via the Wife column.
In a relational database tables model Entity Types. In the above example
People is an entity type, modelled by the People table. Marriage is also an
entity type, modelled by the Marriages table. As we've seen its also a
relationship type. In fact a relationship type is just a special kind of
entity type.
Each column in a table represents an attribute type of each entity type, so
attribute types of People might be FirstName, LastName, DateOfBirth etc.
This table would also have a PersonID numeric column (usually an autonumber)
as its primary key as names are not unique. Each row in a table represents
one instance of the entity type, and the attributes of each instance are
represented by values at column positions in the row. This is the only way
that data can be legitimately stored in a relational database.
Its important that there is no redundancy in the information content of the
database. This is achieved by the process of 'normalization'. Normalization
is based on a set of 'normal form's ranging from First Normal Form (1NF) to
Fifth Normal Form (5NF). There is also one called Boyce/Codd Normal Form
(BCNF) which was inserted when it was found that the original Third Normal
Form was deficient; it didn't cater satisfactorily for tables with two or
more candidate keys where thee keys were composite and overlapped, i.e. had a
column in common. I won't go into the details of normalization here; you'll
find it written up in plenty of places.
To see an example of redundancy and therefore a table which is not properly
normalized take a look at the Customers table in the sample Northwind
database which comes with Access. You'll see that it includes City, Region
and Country columns. If you look at its data you'll se that we are
redundantly told that São Paulo is in province SP which is in country Brazil
4 times. This is not just inefficient, it is dangerous as it leaves the
table open to inconsistent data being entered. There is nothing to stop
somebody putting São Paulo in the UK, USA or in each in separate rows in the
table for instance. To normalize the table it should be decomposed into
Customers, Cities, Regions and Countries tables, each of the first three with
a foreign key referencing the primary key of the next table up in the
hierarchy.
2. Normalization is the process of eliminating redundancy from a database,
and involves decomposing a table into several related tables. In a
relational database each table represents an entity type, e.g. Contacts,
Companies, Cities, States etc. and each column in a table represents an
attribute type of the entity type, e.g. ContactID, FirstName and LastName
might be attribute types of Contacts and hence columns of a Contacts table.
Its important that each attribute type must be specific to the entity type,
so that each 'fact' is stored once only. In the jargon its said that the
attribute type is 'functionally dependent' solely on the whole of the primary
key of a table.
To relate tables a 'referencing' table will have a foreign key column which
makes the link to the 'referenced' table, e.g. a Contacts table might have a
CompanyID column as a foreign key, while a Companies table has a CompanyID
column as its primary key. Consequently no data other than the CompanyID
needs to be stored in a row in the Contacts table for all the company
information for that contact to be known; its available via the relationship
and can be returned in a query joining the two tables on the CompanyID
columns.
Similarly the Companies table might have a CityID column and the Cities
table a StateID column. If its an international database the States (or more
generically Regions) table would have a CountryID referencing the primary key
of a Countries table. So via the relationships, simply by entering (in
reality this would be selected from a list of Companies in a combo box, not
typed in)a CompanyID in the Contacts table the location of the contact's
company is also known. Redundancy, and therefore repetitive data entry is
avoided.
To see how a database is made up of related tables take a look at the sample
Northwind database. Its not completely normalized in fact (deliberately so
for the sake of simplicity) but illustrates the main principles of how tables
representing entity types relate to each other. An example of its lack of
proper normalization can be found in its Customers table. You'll see that
this has City, Region and Country columns so we are told numerous times that
São Paulo is in SP region (as is Resende) and that SP region is in Brazil.
Not only does this require repetitive data entry, but more importantly it
opens up the risk of 'update anomalies', e.g. it would be perfectly possible
to put São Paulo in California in one row and California in Ireland! Proper
normalization as I described above would prevent this as the fact that São
Paulo is in SP region would be stored only once in the database as would the
fact that SP region is in Brazil and California is in the USA.
An example of what at first sight might seem to be redundancy, but in fact
is not, can also be found in Northwind. The Products table and the
OrderDetails table both have UnitPrice columns. It might be thought that the
unit price of a product could always be looked up from the Products table, so
its unnecessary in Order Details. However, the unit price of a product will
change over time, but each order needs to retain the price in force at the
time the order was created. Consequently a UnitPrice column is needed in
both tables; that in products holds the current price and is used to get the
value for that in Order Details (code in the ProductID control's AfterUpdate
event procedure in the Order Details Subform does this), which then remains
static when the current price (in products) changes. In each case UnitPrice
is functionally dependent on the key of the table, so there is no redundancy
3. Of the general purpose primers on Access I particularly like John L
Viescas's 'Running Microsoft Access' (Microsoft Press).
For an introduction to VBA programming in Access Evan Callahan's 'Microsoft
Access/Visual Basic Step by Step' (Microsoft Press) is easy to follow and,
while not taking things to a very high level, provides a solid basis on which
to build.
At a more advanced level the 'Access Developer's Handbook' by Paul
Litwin,Ken Getz and Mike Gunderloy (Sybex) covers the subject in great
detail, and contains a vast amount of usable code.
A useful and easy to read little book on the theoretical basis of the
database relational model is Mark Whitehorn and Bill Marklyn's 'Inside
Relational Databases With Examples in Access' (Springer).
For a highly authoritative but quite abstract explanation of the relational
model Chris Date's 'An Introduction to Database Systems' (Addison Wesley) has
for many years been regarded as a definitive work on the subject. Its by no
means an easy read, however.
For SQL Joe Celko's 'SQL for Smarties' (Morgan Kaufmann) is a wealth of
information on how to write queries. It deals with standard SQL, however,
and is not Access oriented. In fact Joe's views on Access do not bear
repetition where they might be read by people of a sensitive disposition.
Even so it is worth its weight in gold.
Ken Sheridan
Stafford, England
