FoxPop
 

www.foxpop.co.uk

 

 

POWERBASE TUTORIAL 14b

by Laurie Jane Kern

Building Our Second Primary Table

 

Below is the revised Business Table from Tutorial 14a

Field Name Field Type
ID Auto
Company Name Text
Address Text
City Text
Region Relational
Postal Code Text
Country Relational
Telephone Text
Fax Text
URL Text
Business Type Relational
Notes Text or Memo

Once again I am breaking this Table into several other Tables - read on and see how it all ties together.

In my database, as I showed you in Tutorial 13b, I have Contacts (people, friends, clients, etc.) and I showed you how I separated the data into several Tables to make it easier for me to manage. Well the same is true for Businesses. There are businesses that I deal with (like the computer store) where I don't have a contact name, and there are businesses where I do consultancy work where I have as many as 5 contacts. Then there are the phone numbers for them, the secretaries, beeper, voice mail and emails at work. And let's not forget my fellow consultants who could be at any client site on any given day! So I have many different numbers for them.

I hope you see where I am going with all of this! Keeping all this in mind, we shall now break apart the "Business/Company" Table into the following Tables:

Businesses
B/C Phones
B/C Email

We can now see what fields these new Tables have and how they all relate.

We can start with the Business Table first

Field Name Field Type "Notes"
ID AutoNumber This will allow us to identify this record when we use it in other Tables
Business Name Text -
Business Type Relational to the Business type Table -
Address Text -
City Text -
Region Relational to the REGION Table or TEXT -
Country Relational to the COUNTRY Table -
Postal Code Text -
Main Operator Text This is the MAIN telephone number for the company
URL Text This Business's Home Page
Notes Memo or Text Your choice!

Next is B/C Phones: (this is the contact phone number(s) at the businesses)

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.
Business Relational to the Business Table.

The display will be:

[BusinessName}+Phone:"+[Main Operator]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]"

This is so we can display the Business Name as well as the Main Operator number and record ID.
Phone Type Relational to the PHONE type Table Yes, you can use the same one as for your contacts
Number Text -
ID Auto -

 

And finally B/C email:

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.
Business Relational to the Business Table. The display will be: [Business Name}+ Phone: "+[Main Operator]+chr$(6)+"["+right$("0000"+ str$([ID]),4)+"]" This is so we can display the Business Name as well as the Main Operator number and record ID.
email Type Relational to the EMAIL type Table Yes, you can use the same one as for your contacts
Address Text -
ID Auto -

This might seem complex or "over kill" but I see it as having just the right amount of information in just the right place - and it all fits in the screen really easily.

The next tutorial will have the latest and (I hope) the greatest "sample database" for download, and we will discuss some other things   you might want to do with the database.

PS - we are almost done!

© LJKern and FoxPop 1999

[Previous]  [Index]  [Next]