Table design question re duplicates fields

[lmcc007]

I am using Access 2007. I am trying to create a job searching database. The
problem I am having is the phone number field. Sometimes there may be six
different phone numbers. In all the examples I have looked at, they have all
separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is
there a better way to set this up? Is there a way to save data from four
different fields into one field? I have the following fields:

PhoneType1 = Business
Phone1
PhoneType2 = Business 2
Phone2
PhoneType3 = Business Fax
Phone3
PhoneType4 = Mobile
Phone4

As you can see above that Phone type is repeated over and over again.

There should be no duplicates phone numbers for a company.

The phone numbers needs to be in one column in order to sort, filter, and
find.

Also, I want the four PhoneType fields to always be displayed on my form
until I decide to change the type it.

Any suggestions?

 

[John W. Vinson]

I am using Access 2007. I am trying to create a job searching database. The
problem I am having is the phone number field. Sometimes there may be six
different phone numbers. In all the examples I have looked at, they have all
separate fields—that is, Work Phone, Home Phone, Car Phone, and so on. Is
there a better way to set this up? Is there a way to save data from four
different fields into one field? I have the following fields:

PhoneType1 = Business
Phone1
PhoneType2 = Business 2
Phone2
PhoneType3 = Business Fax
Phone3
PhoneType4 = Mobile
Phone4

As you can see above that Phone type is repeated over and over again.

There should be no duplicates phone numbers for a company.

The phone numbers needs to be in one column in order to sort, filter, and
find.

Also, I want the four PhoneType fields to always be displayed on my form
until I decide to change the type it.

Any suggestions?

You're using a relational database: use it relationally!

One Company has multiple Phones: a one to many relationship. Create a *second
table*, Phones, with fields

PhoneID: Autonumber primary key
CompanyID: foreign key to your current table's primary key
PhoneType: Text, "Business", "Home", etc. etc., probably from a very small one
field PhoneTypes table
Phone: Text, the actual phone number

You can create a unique two-field Index on CompanyID and Phone to prevent
duplicates.

I question whether you might need multiple Contacts (people with whom to
communicate) at each Company though, and have the phone table linked to
Contacts rather than to Companies.

 

[lmcc007]

Yes, I did just that. Company and PhoneNumber are my primary keys.

The problem is I want my form to always display Business, Business 2,
Business Fax, and Mobile with all the values being stored in the PhoneNumber
field.

Contacts -- I am not sure about that because the people I contact are inside
the company--like, human resource manager, secretary, supervisor... And,
their addresses are usually the same except with very big companies like
Exxon, Shell...

 

[John W. Vinson]

Yes, I did just that. Company and PhoneNumber are my primary keys.

The problem is I want my form to always display Business, Business 2,
Business Fax, and Mobile with all the values being stored in the PhoneNumber
field.

I'm not sure I understand. You'ld use a Subform based on Phones on the form,
displaying the phone number and the phone type fields. What do you mean by
"all the values being stored in the PhoneNumber field"? There should only be
one.

Contacts -- I am not sure about that because the people I contact are inside
the company--like, human resource manager, secretary, supervisor... And,
their addresses are usually the same except with very big companies like
Exxon, Shell...

So? They might all have the same ADDRESS but surely they each have their own
phone. I have a set of tables

CONtblPeople
ContactID
LastName
FirstName
<etc>

CONtblAddresses
AddressID <autonumber PK>
ContactID
Address1
Direction
Street
Suffix ' e.g. St., Ave., Blvd.
City
State ' also used for Province, etc.
PostCode

CONtblPhones
PhoneID <autonumber PK>
ContactID
PhoneType
Phone

 

[lmcc007]

The problem is I want my form to always display Business, Business 2,

Business Fax, and Mobile with all the values being stored in the PhoneNumber
field.

I'm not sure I understand. You'ld use a Subform based on Phones on the form,
displaying the phone number and the phone type fields. What do you mean by
"all the values being stored in the PhoneNumber field"? There should only be
one.

I want my form to look like Outlook Contact Manager form. You know:

Phone Numbers
Business 800-833-1212
Business 2
Business Fax 800-833-1213
Mobile

Using the subform it will only display if there is data and if no data it
will not display Business, Business 2...

I am reading Access Basics for Programming by Crystal I got off of Allen
Browne's website. I haven't finished yet but maybe it will be address here.

Thanks!

 

[John W. Vinson]

I'm not sure I understand. You'ld use a Subform based on Phones on the form,
displaying the phone number and the phone type fields. What do you mean by
"all the values being stored in the PhoneNumber field"? There should only be
one.

I want my form to look like Outlook Contact Manager form. You know:

Phone Numbers
Business 800-833-1212
Business 2
Business Fax 800-833-1213
Mobile

Using the subform it will only display if there is data and if no data it
will not display Business, Business 2...

Ah, ok.

Base the subform not on Phones but on a Query left joining PhoneTypes to
Phones:

SELECT PhoneTypes.PhoneType, Phones.PhoneType, Phones.Phones
FROM PhoneTypes LEFT JOIN Phones
ON PhoneTypes.PhoneType = Phones.PhoneType
ORDER BY PhoneTypes.PhoneType;

This will display all types whether or not there's a phone to match. You do
need to include the PhoneType field from both tables in order to have the
query updateable, but you needn't display it in the subform.

 

[lmcc007]

I could not get the query to work. And, I sort of didn’t understand it. I
need it to be very basic.

Anyway, after trying it so many times I messed up the database, so I started
over with a new database and created two tables this time.

tblCompanies

CompanyID AutoNumber
CompanyName Text


tblPhoneNumbers

PhoneNumberID Autonumber
Business Text
Business2 Text
BusinessFax Text
Mobile Text
CompanyID Number

If I did it this way, will I be able to sort, filter, and find the phone
numbers without having to search each field? I know this way I will not be
able to add additional phone numbers unless I add more fields, which was what
I was trying not to do.

I wish there was a way to upload the db or post a picture of it.

 

[John W. Vinson]

I could not get the query to work. And, I sort of didn’t understand it. I
need it to be very basic.

Anyway, after trying it so many times I messed up the database, so I started
over with a new database and created two tables this time.

tblCompanies

CompanyID AutoNumber
CompanyName Text


tblPhoneNumbers

PhoneNumberID Autonumber
Business Text
Business2 Text
BusinessFax Text
Mobile Text
CompanyID Number

If I did it this way, will I be able to sort, filter, and find the phone
numbers without having to search each field? I know this way I will not be
able to add additional phone numbers unless I add more fields, which was what
I was trying not to do.

You're still making the same mistake.

Fields are expensive... records are cheap.

Your tblPhoneNumbers should not have one field for each phone number; it
should have ONE RECORD for each phone number.

Try two tables:

tblPhoneTypes
PhoneType (Text, Primary Key)
Seq (Number)

Fill it with the various phone types you want, with Seq containing a number
which will define the sort order.

tblPhoneNumbers
PhoneNumberID Autonumber Primary Key
CompanyID Number (joined to Companies in the relationship window)
PhoneType Text (joined to tblPhoneTypes in the relationship window)
Phone

Create a unique two field index on CompanyID and Phone.

If someone has three phones, they would have three records in tblPhoneNumbers.
If they have only one phone they would have only one record.

Create a new Query. Don't pick any tables; instead copy and paste the
following SQL into the SQL window:

SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType,
tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID
FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers
ON tblPhoneTypes.PhoneType = tblPhoneNumbers.PhoneType
ORDER BY tblPhoneTypes.Seq;

Save this query, and use it as the recordsource for a Subform on your company
form, using CompanyID as the master/child link field. Make it a continuous
form; put textboxes for tblPhoneTypes.PhoneType but set it to Enabled = No,
Locked = Yes so users can't change your master phone type table. Put another
textbox for Phone.

You'll see as many rows as there are phone types; if there's a Mobile phone
for the company, you'll see that phone number in the phone field. If you type
in a new phone number next to the Business 1 phone type, Access will copy the
PhoneType value from tblPhoneTypes to that record in tblPhoneNumbers,
maintaining the link.

 

[lmcc007]

Hi John,

I followed your instructions but the form doesn’t display the Business,
Business 2, Business Fax, and Mobile. If there is no phone number for one of
these fields, it leaves the phone type blank. I tried it again and track
each step. They are as follows:

1. Created main form with one field: CompanyName

2. Saved as frmCompanies

3. Created subform by using the Control Wizard.

4. Used existing Tables and Queries.

5. Next>

6. Query: Query1

7. Available fields choices: tblPhoneTypes.PhoneType
Phone
CompanyID

8. Next>

9. Choose from a list: Show Query1 for each record in tblCompanies using
CompanyID

10. Next>

11. What name would you like for your subform? Query1 subform

12. Finish

13. Open Property Sheet to look at Query1 subform

14. Under Data tab

Source Object: Query1 subform
Link MasterField CompanyID
Link Child Field CompanyID

15. Close Form

16. Opened Query1 subform

17. Property Sheet

Default view = Continuous Forms

18. First Text Box = tblPhoneTypes.PhoneType

19. Went to Data tab

Control Source = tblPhoneTypes.PhoneType
Enable = No
Locked = Yes

20. Second Text Box = Phone

21. Close

22. Opened frmCompanies

23. Query1 subform displays a text box, which is blank because I haven’t
entered a
phone number for this company.

24. Went to second record where there is a phone number for company, so here
two text boxes are displayed. One with the number and the
other one is
empty. For example:

Business 832-888-1919
____________

25. The PhoneType text boxes do not display Business, Business 2, Business
Fax,
Mobile.

Where did I go wrong?

I am glad you understand what I am trying to do, but maybe I missed a step
or something. What do you think?

Thanks a bunch!!!

 

[Fred]

If you haven't entered additional phone numbers (with their phone types) for
that company, nothing should be displayed. It occurred to me that you
might be expecting to see those other ones as "blanks" with the phone type
listed and a blank place for the phone number (like you did in your previous
structure)

 

[John W. Vinson]

Where did I go wrong?

I am glad you understand what I am trying to do, but maybe I missed a step
or something. What do you think?

Probably in Query1. Please open the query in design view; select View... SQL;
and copy and paste the text to a message here.

My guess is that you have the default join type - "inner join". If so you can
fix it yourself - doubleclick on the join line in the query design window, and
check the radio button that says "Show all records from tblPhoneTypes and
matching records from tblPhones".

 

[lmcc007]

Below is what I copied and pasted in SQL:

SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType,
tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID
FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers ON tblPhoneTypes.PhoneType =
tblPhoneNumbers.PhoneType
ORDER BY tblPhoneTypes.Seq;


Is there a way to upload your database on this site?

Thanks!

 

[lmcc007]

Doubleclick on the join line in the query design window, and

check the radio button that says "Show all records from tblPhoneTypes and
matching records from tblPhones".

I checked it and it is set up correct.

How is this telling the form to always display Business, Business 2,
Business Fax, and Mobile even if there is no phone number entered?

 

[John W. Vinson]

I checked it and it is set up correct.

How is this telling the form to always display Business, Business 2,
Business Fax, and Mobile even if there is no phone number entered?

Sorry... I think I gave you some bad advice!

Let me think about this and I'll post back. The Master/Child Link is (of
course) leaving out the records with no CompanyID. I think there's a way to do
it but I'm a bit frazzled today!

 

[lmcc007]

In case I wasn't clear here is what I am trying to do and what I have done so
far.

I am trying to create a form that looks like the Outlook Contact Manager’s
form. For example:

Phone Numbers---------------------------------
Business 800-555-1212
Business 2
Business Fax 800-555-1213
Mobile

I want the form to always display Business, Business 2, Business Fax, and
Mobile whether there is a phone number in the field or not. And, if I need
to add additional phone numbers I can. Below is what I have created:


tblCompanies
CompanyID AutoNumber (PK)
CompanyName Text (Indexed, No Duplicates)

------
tblPhoneNumbers
PhoneNumberID AutoNumber (PK)
PhoneType Text
Indexed = Yes (Duplicates OK)
Lookup: Display Control = Combo Box
Row Source Type = Table/Query
Row Source = SELECT [tblPhoneTypes].[PhoneType],
[tblPhoneTypes].[Seq] FROM
tblPhoneTypes ORDER
BY [Seq];
Bound Column 1
Column Count 2
Phone Text (20)
Input Mask = !999\-000\-0000;0;_
Allow Zero Length = Yes
Indexed = Yes (No Duplicates)

------
tblPhoneTypes
PhoneType Text (PK)
Seq Number (Long Integer)


------
Query1
SELECT tblPhoneTypes.PhoneType, tblPhoneNumbers.PhoneType,
tblPhoneNumbers.Phone, tblPhoneNumbers.CompanyID
FROM tblPhoneTypes LEFT JOIN tblPhoneNumbers ON tblPhoneTypes.PhoneType =
tblPhoneNumbers.PhoneType
ORDER BY tblPhoneTypes.Seq;


------
frmCompanies
Text Box = CompanyName
Control Source = CompanyName

Query1 subform
Source Object Query1 subform
Link MasterFieds CompanyID
Link Child Fields Company ID


------
Relationships
tblPhoneTypestblPhoneNumbers
tblPhoneTypes tblPhoneNumbers
PhoneType PhoneType

Attributes: Not Enforced
RelationshipType: One-To-Many

tblCompaniestblPhoneNumbers
tblCompanies tblPhoneNumbers
CompanyID 1 Many CompanyID

Attributes: Enforced, Cascade Deletes
RelationshipType: One-To-Many

 

[Fred]

John,

To me this looks waaaaay simpler than the discussion.

I think their subsequent post reinforced my guess in my first post......I
think that they are expecting to see display of non-existent records. E.G.
when when no "Business Fax" or "Mobile" etc. record has been entered for a
company, a "Business Fax" and "Mobile" record sill show up, with a blank for
the phone number.

 

[lmcc007]

Yes Fred, that is what I am trying to do.

I looked at MS Outlook Contact manager form layout. It appears to be done:

1) Business, Business 2... is a label or button, which if double click it
will open a form to enter or revise the phone number attached to the label.

2) The down arrow button will let you choose the label--meaning, Assistant,
Car phone, Business, Mobile... After you choose the label it will display a
blank box to enter a phone number if you have not already entered a phone
number for that label.

3) All the phone numbers has to be in one table because you can filter,
sort, and so by the phone number.

It is probably very simple but I can't figure it out. I am a dummy with
Access. I am trying to learn, once and for all, the right way to set up my
tables.

So, what do you suggest I do?

Thanks, for your help!

 

[Fred]

Hello IMC007,

I think that you might have said a couple conflicting ways that you want
this:

1. All of the labels there all of the time
2. Like Outlook (don't know what this is because I don't use Outlook)
3. A "Dropdown" to select the number type, and then a blank to fill it in,
per your last post.

I'd suggest #3.

Make a tiny table listing your phone number types. PhoneNumberType_LU
(shorten that)
Make a dropdown box on the phone number type field in yur sub form. Use
your little PhoneNumberType_LU table as your source for this.

Now you're done.

 

[lmcc007]

I was just explaining how MS Outlook Contact manager works. MS Outlook is a
program that comes with MS Office Professional, which can be used to calendar
dates, to-do list, send and receive e-mails, and add business cards to the
contact manager.

And, I tried #3 earlier this week but it didn't work because each record may
have more than one phone number and so on.

 

[Fred]

I know what Outlook is, I just stopped using it (and switched to free
Thunderbird) a long time ago. It has the one big problem where it sends
attachments out as unreadable-by-many .dat files unless you use "plain text"
compose mode. I just meant that since I don't use it I don't know it's
screens etc.

- - - -

However, I'm assuming that, for a contact, Outlook has a handful of
standardized types of phone numbers, and thus a pre-defined heading for each
of them. If that is what you want and can live with, then you'd skip this
entire thread, and go back to a single table with a field for each phone
number type.

Under the structure that the respondents in this thread came up with, you
have no such predetermined structure / restructions. If one person has 15
phone numbers, many of non-standard types, their record would have 15. If
someone else had only one, their list would have only one.

One other possibility would be to automate it so that when you enter a new
person, it automatically loads a set of records in the phone number table for
the common types. So you'd have titles and blanks for the common phone
number types, plus the ability to add others. This would be a lot of work
and complexity to go though, resulting in what many would call bad data.

In my opinion, make the leap, and go with your #3, which is how it's
normally done when databasing things such as yours. It does not have the
restriction/problem that you describe....it lets you put an unlimited number
of phone numbers under each person, indluding repition of the types, if you
so choose.