Microsoft Access - Relational Database Example
This example shows step by step how to create
2 relational tables and create a form and sub-form to display those results.
It's one of the basics of access that must be understood if you want to create
any kind of database. Once you have done this and understand it you will be
writing creative databases in no time.
The example shown here was for a radio ham who wanted to store details of contacts and history.
Imagine Hams contains a list of hams with fields HamID HamName HamLocation
- HamID should be set as your unique ID. I.E 2 'Hams' may have the same name
but never the same HamID
- ContactHistory contains contact history with other hams with fields AutoNum HamID
Date Details
- Autonum is your UniqueID and we don't really care about it or use it for
anything
Here is that database with the tables set up and some data.
Hams.mdb
or zippedHams.zip
Now the fun....
- Go into tools relationships and show both tables
- Select the HamID field in table1 and drag it to HAMID in table2.
- Click on the create button that pops up.
- This is called a relationship, in particular a 'one to many' relationship.
i.e for each one HAMID in table1 there are MANY related HAMID's in table
two.
Access now understands how the two tables are connected and this gives you a
fantastic advantage.
- Now create a form based on table1 and place all the fields in table1 on that
form. Save the form but don't close yet.
- Now place a subform (it's one of the toolbox buttons called
subform/subreport) on that form.
- In the wizard that pops up choose
table/query and click next. Choose table2 and select all fields and click
next, now here's the relationship and because you've already told access how
it works it can guess at the required one. Choose Show Table2 for each
record in Table1 using HAMID, then click next.
- Give the subform a name, I normally use zsubTable1Table2 and click finish.
- Save the form and open it. Now your form will show all the HAM info for a
particualar person and all there related contacts (including history)
- When you move to the next person it will only show all there contact history.
| 