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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lynn H - 06 Dec 2004 15:17 GMT

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

Reply to this Message

Rick B - 06 Dec 2004 15:33 GMT

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

> 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

Reply to this Message

JimLudden - 06 Dec 2004 17:15 GMT

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.

> 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
[quoted text clipped - 14 lines]
> any
> > help would be appreciated - Thanks Lynn

Reply to this Message

Rick B - 06 Dec 2004 18:13 GMT

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

> 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
[quoted text clipped - 26 lines]
> > any
> > > help would be appreciated - Thanks Lynn

Reply to this Message

Howard - 07 Dec 2004 21:50 GMT

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

> Jim:
>
[quoted text clipped - 44 lines]
>> > any
>> > > help would be appreciated - Thanks Lynn

Reply to this Message

John Vinson - 08 Dec 2004 02:56 GMT

>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
[quoted text clipped - 4 lines]
>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

Reply to this Message