I want to setup a database by using Microsoft Access. But I do not
really understand the Relationship and Expression. I want to know how
the tables relate each other, and use the expression to query
multi-table.

Hello kevinchiem!!

I must start talking a little about MS Excel´s lists or databases.
In order to create a list in Excel one first row must be introduced in
which the content of each column is described. Then you will introduce
in the following rows each one of the data. These rows constitute the
registries of the database or list. Ecxel can perform two main
operations with this data: Sort and Edit. Other operations that can be
made are to make a filter to obtain a new sublist just by the students
who fulfill conditions or for finding subtotals of the fields that are
desired.
These functions are own of an application of databases management,
nevertheless it exists a fundamental difference between Excel and an
database application: Excel basically only can have a list in each
sheet and it cannot establish relationships between these lists.

In the terminology of the database applications, which Excel calls
list or database denominates table. And the set of all the tables with
its relationships is what it really constitutes the database.

For a better management of the information and to reduce the number of
data, the tables are related. For example, if you desire to have lists
with the students of each subjet, instead of including the full name
in the list of each subjet, it will be enough with storing the
student-card number of the students of each subjet. These card number
serves to establish a relation between the table that contains the
student profiles and the tables of each subject.

A table is a collection of data with the same structure. If the data
is arranged in a spreadsheet, a column always has the same data type,
for example a card number, a name, etc. Each table consists of a
certain number of fields, in each one a data is stored, this data can
be numerical, alphanumeric, date, etc.
A field is a single column of a table, and the name of the column is
the name of the field.
A registry is each one of the rows of the table, and is formed by the
data of each one of the fields stored in a same operation.

Access as a relational database manager allows the simultaneous use of
data coming from more than a single table.

In order to be able to relate tables to each other a common field 
will be specified that it contains the same value in the two tables
and this field will be the major key in one of them.
The tables are related from two to two, where one of them will be the
main table from which the relation originates and the other will be
the secondary table destiny of the relation.
The kind of relationship that Microsoft Access creates depends on how
the related fields are defined:
·  A one-to-many relationship is created if only one of the related
fields is a primary key or has a unique index.
·  A one-to-one relationship is created if both of the related fields
are primary keys or have unique indexes.
·  A many-to-many relationship is really two one-to-many relationships
with a third table whose primary key consists of two fields - the
foreign keys from the two other tables.

After you've created Relationships you can create queries, forms, and
reports to display information from several tables at once.


Access offers the ability to answer questions. The answer to these
questions, and many more are found in the database, and Access can
find the answers quickly. When you pose a question to Access, the
question is called a query. A query is simply a question represented
in a way that Access can understand.
You use queries to view, change, and analyze data in different ways.
You can also use them as the source of records for forms and reports.
The most common type of query is a select query. A select query
retrieves data from one or more tables using criteria you specify, and
then displays it in the order
you want.
The Expressions in Queries is used to create a computed fields in
queries. To include computed field in queries, you enter a name for
the computed field, a colon, and then the expression in one of the
columns in the Field row. For example to calculate Total Price, you
would type
Total Price:[Price]*[Quantity]. 
You can type this directly in the Field row. You most likely will not
be
able to see the entire entry, because the Field row is not large
enough. To avoid this, select the column in the Field row, right-click
to display the shortcut menu, and then click Zoom. The zoom dialog box
displays where you can type the expression.
The computations you can do are addition (+), subtraction (-),
multiplication (*), or division (/). You can use parentheses in you
computation to indicate which computation should be done first.

I recommend you to visit the following pages that will provide you
very nice tutorials and explanations:

"BATS (Baseline Access, Training and Support)": It is a California
State University initiative to provide all students, faculty, and
staff training in the uses of baseline hardware and software systems,
and ongoing professional and technical support for utilization of
computer resources at San Diego State University.
http://www-rohan.sdsu.edu/~bats/

"BATS Handouts" page:
http://www-rohan.sdsu.edu/~bats/PDF/pdfhandouts.html

To download the Access database files for practice of the following
lessons/handouts, please click:
http://www-rohan.sdsu.edu/~bats/PDF/ACCESS_DBS/access_dbs.html

"Introduction to MS Access II: Relationships & Queries"
http://www-rohan.sdsu.edu/~bats/PDF/Students/Access/Access2.pdf

-----------------------------------------------------------------

Another places to visit are 

"Microsoft Access Tutorial - FunctionX":
http://www.functionx.com/access/


"Simon Fraser University MS Access Tutorials":
http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html


"Washington State University - Land Grane Training Alliance - Online
Access 97 Lessons":
http://cru.cahe.wsu.edu/training/Access97/


"Google Search Results page of Access and tutorial":
://www.google.com/search?q=access+tutorial&hl=en&lr=&ie=UTF-8&start=0&sa=N


I hope this helps you, if you need some clarification, please post a
Request of Answer Clarification.

I hope this helps you.

Regards
livioflores-ga

---

Clarification of Answer by livioflores-ga on 28 Sep 2002 23:23 PDT

Hi again, and thanks for the good rank!!

If you want to practice, working on constructed databases and
following instructions step by step, please visit "Microsoft Access
Tutorial - FunctionX":
http://www.functionx.com/access/ 


Download the samples databases, according the version of Access that
have you installed in your PC (look for the links under the Exercises
paragraph):

MsAccess 97: http://www.functionx.com/access/databases/MS%20Access97.exe
or
MsAccess 2000: http://www.functionx.com/access/databases/MS%20Access%202000.exe


Open the "Subject to learn" page by clicking the corresponding link,
and follow the instructions, using the sample database that the lesson
request to open.

For Relationships the page is:
http://www.functionx.com/access/lesson08.htm


For Queries you have two pages:
http://www.functionx.com/access/lesson18.htm
and
http://www.functionx.com/access/lesson19.htm


For Expressions you have two pages too:
http://www.functionx.com/access/lesson20.htm
and
http://www.functionx.com/access/lesson21.htm


You can choose from a total of 23 diferent lessons from the main page.

Good luck with your learning kevinchiem!!!

livioflores-ga

It helps if you give a real world samples.

Hi,
Relationship:
It is the association established beween two tables using common
fields. Usually it was done
by linking the Primary key of a table to a forign key in another
table.

The types of relationships are one to one, one to many and many to
many
One to One:
If a record in main table has exactly only one relavent record in
another table.
One to Many:
Every record in table has multiple relavant records in another table.

Many to Many:
you have to break many to many relationship to 2 one to many
relationship You can use
Relationship window to create relationship graphically.

Expression:
Expression is used to set the values of a field(column) or to create a
calculated fields in
queries. Expression is the combination of constants, variables, fields
from tables, operators
built in fuctions etc. use expression builder to create new
expression.

For detailed step by step instruction and explanation suggest to 
spend some time on Access help menu topic Relationships in Creating
and Linking tables and
Using Criteria and Expressions to Retrive Data in Working with Queries
Topic.

$5 might not be enough to tempt a researcher, so here’s my comment for
what it’s worth.  I'm not really an expert so some corrections might
be poted as well.

Access is a relational database based (as are all relational
databases) on the original work by E.F. (Ted) Codd in the late 60’s. 
I think that he was working for IBM at the time.

Within an Access table all of your data is arranged into Rows, each
row in a table sharing the same field names (what would be called a
‘column’ in a spreadsheet).
For example you could create a table called ‘Address’ with the first
field being called ‘House Number’, the second being ‘Street Name’ and
so on.

You might create a second table ‘Friend’s Names’ with fields ‘First
Name’, ‘Second Name’, ‘Nickname’ etc.

To connect your friends name with their address you use a
relationship.  In this case you could call the relationship ‘lives at’
(<friends name> ‘lives at’ <Address>).  In Access you might create an
Autonumber field to use as the primary key (a unique identifier for
each row) in each table, open the relationships window (from the
toolbar) and drag and drop the key field from the Names table onto the
key field of the Address table.  This should create a relationship and
Access will draw joining line in the Window to show you that it's
there.

The main point keeping data relationally is to avoid duplication.  If
you had more than one friend at an address or a friend had more than
one address you would record this using a new relationship rather than
typing the address or your friends name in again.

Once a set of tables and relationships have been designed in such a
way as duplication is impossible for any given set of data it’s said
to be ‘normalised’.  This is what DB administrators spend a lot of
time doing.

You use an 'expression' to get the data you want out of the database. 
If you wanted to know all of your friends with the first name ‘Fred’
you’d use an expression.

In programming terms an expression is any piece of program code in a
high-level language which, when (if) its execution terminates, returns
a value.  In databases this term is used for any piece of code used
upon a set of tables and relationships via a database management
system that returns data.  In Access this usually means a query,
though forms and reports may also return data generated using an
expression
In Access this is usually done graphically using the GUI, so you tend
not to see queries as ‘expressions’.  Behind the scenes, however,
Access uses SQL (Structured Query Language) so, once you’ve designed a
query, switch to ‘SQL View’ (you’ll find it on the toolbar) to see
what it looks like as an expression.

Good Luck

Google
://www.google.co.uk/search?hl=en&ie=ISO-8859-1&safe=off&q=codd+entity+relationship+expression&btnG=Google+Search


Might I recommend a book such as 'Teach Yourself Microsoft Access' 
ISBN: 0764532820 for further research?