B
batchen27
I built some tables but I don't know how to connect them together.
Any help anybody??
Any help anybody??
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
I nees help with building an access database for a travel agency
K
Ken Sheridan
In the absence of any more detail about your tables the best I can do is give
you a generalised summary of what's involved.
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 of the entity type, e.g. ContactID, FirstName and
LastName might be attributes of Contacts and hence columns of a Contacts
table. Its important that each attribute must be specific to the entity
type, so that each 'fact' is stored once only. In the jargon its said that
the attribute 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. The process of eliminating redundancy is known as Normalization and
there are 'Normal Forms' ranging from First Normal Form (1NF) to Fifth Normal
Form (5NF) each with its own rules for determining whether a table is
normalized to that Normal Form. There are also some other normal form, but
these are rather bizarre and you don't need to concern yourself with them.
For more details of normalization see:
http://www.datamodel.org/NormalizationRules.html
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.
In Access relationships are created in the Relationships window which can be
opened from the main database toolbar. Add tables to the window and create
the relationships by dragging from the primary key of one to the
corresponding foreign key of the other. In the dialogue which opens you can
enforce referential integrity and cascade deletes and updates where
appropriate. Be careful about enforcing cascade deletes, however, as there
will be situations where you don't want this, e.g. you would probably not
want to automatically delete all customers in a city if a city record is
deleted, but instead prevent the deletion of a city if there are any
customers in that city. To ensure the latter enforce referential integrity,
but not cascade deletes.
Ken Sheridan
Stafford, England
you a generalised summary of what's involved.
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 of the entity type, e.g. ContactID, FirstName and
LastName might be attributes of Contacts and hence columns of a Contacts
table. Its important that each attribute must be specific to the entity
type, so that each 'fact' is stored once only. In the jargon its said that
the attribute 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. The process of eliminating redundancy is known as Normalization and
there are 'Normal Forms' ranging from First Normal Form (1NF) to Fifth Normal
Form (5NF) each with its own rules for determining whether a table is
normalized to that Normal Form. There are also some other normal form, but
these are rather bizarre and you don't need to concern yourself with them.
For more details of normalization see:
http://www.datamodel.org/NormalizationRules.html
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.
In Access relationships are created in the Relationships window which can be
opened from the main database toolbar. Add tables to the window and create
the relationships by dragging from the primary key of one to the
corresponding foreign key of the other. In the dialogue which opens you can
enforce referential integrity and cascade deletes and updates where
appropriate. Be careful about enforcing cascade deletes, however, as there
will be situations where you don't want this, e.g. you would probably not
want to automatically delete all customers in a city if a city record is
deleted, but instead prevent the deletion of a city if there are any
customers in that city. To ensure the latter enforce referential integrity,
but not cascade deletes.
Ken Sheridan
Stafford, England
L
Larry Linson
I built some tables but I don't know how to connect them together.
Any help anybody??
With all that detail, you might get a response starting "Headbone connected
to th' neck bone," as in the old song.
As a start, you might download and view my presentation "Practical Table
Layout" from http://appdevissues.tripod.com/downloads.htm. It deals with
the practical issues of determining what data you need and how to relate the
tables "without having to become a relational database expert".
First, you are going to have to understand the needs and requirements of
your users... what data they need, when and how they need to be able to
retrieve or enter it. And, with only a little knowledge of airlines and
rate structures, at the very least, I know you'll have to have an automated
connection to the airlines' databases.
If I understand correctly, almost all successful travel agencies subscribe
to a service that provides what they need, retrieves schedules/prices, etc.,
and actually makes reservations. This, very likely, isn't a project that
should be undertaken by a part-timer who needs guidance on relating tables.
Larry Linson
Microsoft Office Access MVP