Microsoft Access Office VB VBA Help and Examples

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.

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.
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.