How to enter 1 customer with 5 contacts in access without enterin.

[Lynn H]

I would like to make a database for customers. One company has 5 contacts and
I wish to enter the company once and be able to enter more than 1 contact in
the record file. How do I change the contact to enable me to enter more than
1 contact. There used to be a database for this but cannot find it now .. any
help would be appreciated - Thanks Lynn

 

[Rick B]

That is a one-to-many relationship. You would need two tables. One with
the company data including a key field (such as CompanyNumber). The second
field would contain the key (CompanyNumber), contact name, contact phone,
contact birthdate, etc.

This is a classic one-to-many relationship.

Rick B

 

[JimLudden]

A simpler solution requires that you assume that each contact is for only one
company. (If a contact answers for more than one company you will have to
enter that contact twice.) This often suffices and simplifies the data
structure:

Your contact table would have:
Company Key -- a link to the primary key of the Company table
Contact data -- Name, phone, Email, etc.

Thus you can have as many contacts for each company as you need. The primary
key for each contact will include the Company key.

 

[Rick B]

Jim:

That is exactly what I was proposing. One-to-many with one being the
company and many being the contacts. The contacts table could have one
contact per company, or 100. Was my post unclear?

Rick B

 

[Howard]

I'd like to add a twist to this.
I also wish to have a one to many like this but need to validate it so that
I can have a MAXIMUM of 5 contacts for each comapny
Making a table with five contact fields makes a repeating group so I guess
I'll have to count the records somehow. Any ideas?


secondly, I'd like to make it so that you cannot edit the contacts for a
given company, just delete them all and remake them - a delete query?

Howard

 

[John Vinson]

I'd like to add a twist to this.
I also wish to have a one to many like this but need to validate it so that
I can have a MAXIMUM of 5 contacts for each comapny
Making a table with five contact fields makes a repeating group so I guess
I'll have to count the records somehow. Any ideas?


secondly, I'd like to make it so that you cannot edit the contacts for a
given company, just delete them all and remake them - a delete query?

You'll need to implement database security so that the ONLY way a user
can interact with the data is through a Form that you create. They
should not have any access to query or table datasheets - otherwise
you lose control.

Use a Form based on the company table, with a Subform based on the
Contacts table. Set the Subform's AllowAdditions and AllowDeletes
properties to True, and AllowEdits to False to prohibit editing the
data for an existing contact.

In the Subform's BeforeInsert event put code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*","[Contacts]","[CompanyID]=" & Me!CompanyID) >= 5 Then
MsgBox "Only five contacts allowed!", vbOKOnly
Cancel = True ' Prohibit inserting the record
End If
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps