Break tables without Table Analyzer (Access 2000) 1

[ssampier]

I have inherited the maintenance of a small database containing several tables and queries. None of our tables are related. Our main table contains over 8000 records. It has several repeating fields, such as email 1, 2… 31. I’d like to move these fields into their own related table, called tblEmail.

I have tried using the table analyzer, but I didn’t like the results. The Table Analyzer used a lookup field to relate tblEmail, which I didn’t want. Also, it renamed my table to Table1. I didn’t want that either.

I’d like to move these fields into their own related table. The new table would have Automatic Number as the primary key. I’d like to use the main table’s primary key as a foreign key to relate the tables.

Could I use an append query to move these fields or is there any easier way to break a table without using the table analyzer?

Thank you for your help.

 

[lespaul]

select userid, email1 FROM tablename where email1 <> ''
UNION
Select userid, email2 FROM tablename where email2 <> ''
UNION
select userid, email3 FROM tablename where email3 <> ''

you may want to include another identifer, but this is the basics for normalizing your tables. Once you get your UNION query with the correct data in the correct format you can use it as the source of an INSERT query:

INSERT INTO newTableName (SELECT .....)



Leslie

 

[ssampier]

It worked! I had some difficulty in getting the SQL syntax written correctly.

Now I have a table, tblEmail, with two fields, Phone Number and EmailAddress. Since one customer can have many email addresses (one to many), is it a concern that I have repeating Phone Number (the foreign key)?

If so, I'd like to move these to separate fields within the existing table. Otherwise, I'll create an Autonumber field as the Primary Key and relate the two tables using the Foreign key Phone Number.

Thanks for your help!

 

[lespaul]

Since one customer can have many email addresses (one to many), is it a concern that I have repeating Phone Number (the foreign key)?

not a concern, that's the way a Junction table like this works!!!



Leslie

 

[ssampier]

I defined my one-to-many relationship without problems.

Since we used phone number as the primary key that would entail a many to many relationship (one customer can have many phone numbers).

I'd like to create another table containing all the separate phone numbers (DSL number, billing number, etc) related by primary phone number. This time I'd prefer to these separated into separate fields, billing number, dsl #, etc.

Can I do it this way?

 

[willir]

Interesting.

I actually use phone numbers and email addresses in the same field, and define the type of contact info -- PhoneH (home), PhoneW (work), emailH, etc and define this as a many-to-many relationship.

One contact will have several ways they can be contacted.
A contact method such as business phone number can have many contacts. (Example, a Helpdesk number, SalesOrder number)

I am not keen on your idea of using the phone number as a primary key -- phone numbers and email addresses change. My preference would be to use an ID number as the primary key. By all means define the index for the phone number as unique to prevent duplicates.

And there is nothing wrong by referencing primary key the phone number / email address table or tables in another table or tables as the foreign key. This is the idea and the power behind a relational database.

However, keep in mind that the phone / email table can include more attributes to better define the contact type. DSL, ISDN, FrameRelay are easy values to include as the PhoneType attribute. For example, and only as an example, the billing info can be stored on the table to reference another record within the same table -- akin to the employee / supervisor table design where the supvisor field points to another employee.

Richard

 

[ssampier]

The Phone number as the primary key was not my idea. I work for a telephone company so old habits die hard, I guess. I'm just trying to get my work done without wasting countless hours. Previously, I would have to manually exclude entries that had a separate billing number from their main phone number.

I'm still fairly new to Access and database, so my methodology may be a little off. I had a SQL and DBMS several class several years ago, but I must admit I nodded off in the normalization section (my book is dryer than dirt).

I'm confused. How can you have phone number and email address in the same field?

 

[lespaul]

One way, you have a setup like this:

[b]customer[/b]
CustomerID
Name
Address
etc.

[b]contact types[/b]
ContactID
ContactType

ContactID     ContactType
   1          Home Phone
   2          Work Phone
   3          Cell Phone
   4          email Address


[b]customer contacts[/b]
CustomerContactID
CustomerID
ContactTypeID
ContactInfo

CCID         CustomerID          ContactTypeID       ContactInfo
  1            1                      4          someone@somewhere.com
  2            1                      1                5055551212
  3            1                      3                5056652121

Leslie

 

[willir]

How can you have phone number and email address in the same field?

Both phone numbers and email addresses are text strings. So they can treated in a similar fashion. Having said that, you can also use two tables or two fields depending on how you want to define your relationships.

I work for a telephone company ... None of our tables are related.

What is the purpose of the database?

Your objectives as outlined...
1) I’d like to move these fields (email1, email2...) into their own related table
2) I'd like to create another table containing all the separate phone numbers (DSL number, billing number, etc) related by primary phone number. This time I'd prefer to these separated into separate fields, billing number, dsl #, etc

Okay, assuming you want a table for email addresses and a table for phone numbers. Since this database seems more than a contact database where you can bill for phone numbers, but probably do not bill for email addresses, this makes sense.

Can a company or person have more than one phone numbers - yes of course.
Can a phone number be used by more than one person? If yes, then you should treat this as a many-to-many relationship. Otherwise, you have a one-to-many relationship.

Can a company or person have more than one email address - yes of course.
Can an email address be used by more than one person? If so, then you have a many-to-many relationship. If not, then you have a one-to-many relationship.

Let's play both scenarios with telephone numbers...

tblUser
UserID - primary key
UserName - can be the name of a business or person
...etc

Many-to-Many example
tblPhoneNo
PhoneNo - primary key
PhoneType
BillingType
BillToPhoneNo - foreign key to tblPhoneNo.PhoneNo
...etc

Discussion:
BillToPhoneNo allows you to set the primary phone number used for billing. You may have five different numbers all billed to one phone number.
PhoneType - voice, data, DSL, etc
BillingType - allows you define a billing type, and reference a billing table

tblPhoneProfile
PhoneNo - foreign key to tblPhoneNo
UserID - foreign key to tblUser

Discussion:
You can items / fields to this table that are applicable to the PhoneNo x UserID. For example, Primary or secondary for primary or secondary contact number. Or a date when the field was last updated.

Presentation:
A M:M relationship is flexible in how the data can be presented. A common approach is to create a subform based on the joiner table -- in this case, tblPhoneProfile. If the subform for tblPhoneProfile is to be included in the main form based on tblUser, hide the field UserID and change the PhoneNo to a combo box pointing to tblPhoneNo. You can also approach this from the other way with the main form based on tblPhoneNo, and for the subform based on tblPhoneProfile, hide the field PhoneNo and change the UserID to a combo box pointing to tblUser. Or you can use a form based on tblPhoneProfile and use a combo box for both UserID and PhoneNo.

For a one-to-many solution, a sample design would be ...

tblPhoneNo
PhoneNo - primary key
UserID - foreign key to tblUser
PhoneType
BillingType
BillToPhoneNo - foreign key to tblPhoneNo.PhoneNo

Here, the most common presentation would be to use a a subform based on tblPhoneNo embedded in a form based on tblUser.

Hopefully, I have given you some ideas.

Richard

 

[ssampier]

Richard, I thank you for your long, concise post. Again, this is not my database. I have inherited it from my position's predecessor. I'm responsible for its upkeep, doing whatever it takes to keep the records updated.

The function of our database is a data repository. The department uses it to answer simple query questions like, "How many DSL customers do we have in town x?", "Does customer Y have dial up Internet, DSL, or webhosting?" I use it for audits, comparing Access data against other systems.

On a monthly basis I perform three audits, comparing our Access data versus external data from three separate systems that I receive via email. The audits are my focus for this database. The audits do the following: make sure customers are being billed, I have concise records of current users, and that customers have the proper service (dial up, DSL, etc).

Each residence or business can have only one Internet service (we really don’t include webhosting in the audits). However, each residence or business can have many different phone numbers, that’s why I was thinking many-to-many.

My Table Relationships

Main Table TblEmail
UniqueID
Phone Number ---------> PhoneNumber------>
FirstName Email Address
LastName

tblBillingInfo tblAuditBilling
UniqueID UniqueID
----> PhoneNumber
BillingNumber---------------> BillingNumber

That’s how I’d imagine it anyway. I know it’s an unorthodox way of doing things, but just the way it is at my company. Old habits die hard; we have four systems that are not related. We have manually import the same information four times (signup, wired/order, server, Access).

 

[willir]

I understand that you inherited the database, but your wish-list did suggest that you want to make some changes (add tables) to the design.

for clarification, do you mean your design is....
[tt]
Main Table TblEmail tblBillingInfo tblAuditBilling
UniqueID UniqueID UniqueID
Phone Number --> PhoneNumber---> PhoneNumber
FirstName Email Address BillingNumber---> BillingNumber
LastName
[/tt]

Or...
[tt]
Main Table TblEmail
UniqueID
Phone Number --> PhoneNumber
FirstName Email Address
LastName


Main Table tblBillingInfo tblAuditBilling
UniqueID UniqueID
Phone Number --> PhoneNumber
FirstName BillingNumber---> BillingNumber
[/tt]

In the first example, you are suggesting you have to navigate to the audit and billing tables through the eMail table.

...Moving on
Thank you for the added information -- respository database -- understood. Where and how is the information kept for the types of service -- DSL, Internet, DialUp, WebHosting? Is this a simple field on one table, or a collection of fields in a table? This is the information you want to extract from the database, so it makes sense to include this in the discussion.

 

[ssampier]

Thanks Richard! It is true I do want to make some performance changes. The main table performance was extremely slow. It helped moving that email information to its own separate table (it messed up my unmatched queries, but that's a separate issue).

You are correct, the second option is my existing relationship. I'm sure there's an easier way to relate everything, but I'm trying to take one step at a time (the AA of database "redesign").

The information for DSL, dialup, etc. is located as the field called "Service" in the main customer table (selecting a response for each customer). I haven't yet seen the need to move that information to a separate table.

As for the phone number being primary key, it's easiest for me, since we don't use CustomerIDs or any other unique number. Our billing system does have this information, but I don't receive it from Wired/Orders. I could use a AutoNum as Primary key, but then I'd have to manually input that information into the other tables to properly relate them. The combined PhonenumberEmailAddress as primary key may be a good option. My only concern is that customer's EmailAddress changes more often than I you might imagine (more often than Phone Number).

Again, I really appreciate your help!