FoxPop
 

www.foxpop.co.uk

 

 

POWERBASE TUTORIAL 13 b

by Laurie Jane Kern

Building our First Primary Tables

Below is the revised People Table from Tutorial 13a where we left off the last time.

People Table Field Type
ID Auto Number
Title Text
First Name Text
Last Name Text
Address Text
City Text
Region Relational
Postal Code Text
Country Relational
Telephone - home Text
FAX Text
Mobile Text
Email Text
Email2 Text
URL Text
Contact Type Relational
Notes Memo

I am now going to explain how and why I am breaking this Table into several Tables. Remember this is not the only way to set up a Contact database;   this is how I have done it, to fit my personal and work life. So here we go!

In my database I have friends, family members, people I work with, people I consult for (or have in the past). There are also companies too, but that is a whole other set of Tables which we shall discuss later. There are people  I correspond with via email, but have never met, nor do I have their addresses. In addition to this, I have contacts who have NO email addresses and some with 3. Then there is the telephone - 2 lines at home, mobile (car) phone, beepers, cell phones and voice mail as well! There are the other consultants I work with and we could be at any of 1, 2, 3 or more client sites - some I do work at and others I don't.

I personally do not like having a record in a Table (as shown above) and I then fill in only 2 or 3 fields. And what do I do when I don't have enough fields? Detailing my own database here will help to explain why I have broken the People/Contact Table into smaller Tables with each Table focused on "a specific category of information" . Here are the Tables we are going to build:

People
Phones
Personal Addresses
Email

I expect that now you are now wondering what fields make up each of these new Tables and how do they all relate?

Let's start with the People Table first

Field Name Field Type "Notes"
ID AutoNumber This will allow us to identify this record when we use it in other Tables
Title Relational to the TITLE Table  
FirstName Text  
LastName Text I don't have a lot of contacts who give me there middle name or initial so I do have that field in my Table
Contact Type Relational to the CONTACT type Table  
Gender Relational to the GENDER TYPE Table Hmmm, do we need to make a new lookup table?
Birthday Text  
Age Calculated: see tutorial 8 for a choice of equations This is an optional field, I do not have it in mine but you might want to have it.
Notes Memo or Text Your choice!

Next is Personal Addresses Table:

Field Name Field Type "Notes"
Contact Relational to the People Table. The display will be: [FirstName}+" "+[LastName]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]" This is so we can display the full Name as well as the record ID. You can also change it to have the last name before the first name.
Address Text  
City Text  
Region Relational to the REGION Table or TEXT If most of your "contacts" were in the USA and or Canada, then the lookup Table would contain the State and Territory Names. If you are in the UK this could be a listing of the Counties or even "WALES" and "SCOTLAND". But is you are like me, where you have addresses from around the world, it could be a text field or you could have counties, states and Canadian territories.
Country Relational to the COUNTRY Table  
Postal Code Text  
Notes Memo or Text Your choice!
Home Page Text A Home Page is an address of sorts, don't you agree.
ID Auto  

Now for the Phones Table:

Field Name Field Type "Notes"
Contact Relational to the People Table. The display will be: [FirstName}+" "+[LastName]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]" This is so we can display the full Name as well as the record ID. You can also change it to have the last name before the first name.
Phone Type Relational to the PHONE type Table Oh! We need to make a new lookup table!
Number Text  
ID Auto  

And finally the Email Table:

Field Name Field Type "Notes"
Contact Relational to the People Table. The display will be: [FirstName}+" "+[LastName]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]" This is so we can display the full Name as well as the record ID. You can also change it to have the last name before the first name.
Email Type Relational to the EMAIL Type Table Oh! We need to make a new lookup table!
Address Text  
ID Auto  

What about the two new lookup Tables - Email Type and Phone Type? The Table layout will be very similar to the other lookup Tables we created. For now I leave it up to you to determine what TYPES of phone and email values you might want - I shall reveal my choices in the sample database that will be available for download with the next tutorial. I will also have data in the sample database so you can see how it all works. See you soon.

© LJKern and FoxPop 1999

[Previous] [Index] [Next]