Database Design and Data Entry Help..

[TacoBueno]

Greetings.. I am attempting to teach myself Access but I am having trouble trying to figure out how to implement into my database the features I need my enduser to have.

Basically I am trying to create a Contacts database and I want the "form" that my end user enters data into to look like this (all examples are minimalist because I am looking for specific logical answers to solving my problems, not field suggestions):

Name: [textbox]
Phone: [textbox]
Email: [textbox]

Now I need to have Phone and Email each in their own tables, or in some way seperated so that I can retrieve a list of emails or phones without duplicates.

My initial attempt was to create the tables:

*key

NAMEtable
*Name
Phone (related to PHONEtable.Phone)
Email (related to EMAILtable.Email)

PHONEtable
*Phone


EMAILtable
*Email

Now the problem with that setup is that I cannot create a new entry into NAMEtable unless the specific Phone AND/OR Email are ALREADY listed inside their respective tables.

What I need to be able to do is give my user the ability to use a "form" with 3 fields, that will do the following:

Allow a new Name without a Phone or Email.
Allow a new Name WITH a Phone and/or an Email.
Allow a 2 different Names to have the same Email, but pointing to only 1 instance of that email in a different table.
(which it already does all if the Email and Phone already exist in their tables, I need it to be able to create new entrys into those tables from the form if they don't already exist). I also need to be able to enter in new Emails from a different form2 that dont have any relation to any persons in Name (which isn't a problem either at the moment since I just open the Email table and enter data).

I am unsure how to execute this first forms dilema though, I am assuming my database design is fine and it has something to do with some formality or code that I am missing to be able to do what I want. If so could someone help me out and make some suggestions? Or if my design is way off and there is a better way to be able to enter and store a Name, Email, and Phone from a form that relates back to a single contact and prevents duplicates then please let me know. Thanks

Also here is another post I made in a different forum but wasn't able to get quite the answers or help I was looking for.


"Name [textbox]
Email [textbox]

if the email the user is entering isnt already in the database it will not create a new row in email.email and then link contacts.email to that specific email.email...........

for example if

contacts NAME , EMAIL
row1 = bob , email.email row1
row2 = bobswife, email.email row1

email Email
row1 = bob@bob.com

then I open a form that will have 2 members, both pointing to the same email.

1 of 2
Name: [bob]
Email: [bob@bob.com]

2 of 2
Name: [bobswife]
Email: [bob@bob.com]

Ok, lets say I go to create a new member

I can do this just fine:

Try1
3 of 3
Name: [bobsson]
Email: [bob@bob.com]

What I can't do but need to be able to do is:

Try2
3 of 3
Name: [bobsson]
Email: [bobson@bob.com]

It will return an error stating that the email isnt in the email table. I need to know how to get Try2 to do what I want so that when I enter 3 of 3 my database looks like this:

contacts NAME , EMAIL
row1 = bob , email.email row1
row2 = bobswife, email.email row1
row3 = bobsson , email.email row2

email Email
row1 = bob@bob.com
row2 = bobsson@bob.com"

Anyways.. thanks again for any help.

 

[gmmccarthy]

My suggestion is why have separate tables?

Have your table contain the three fields (name, phone, & email).

You could then set the Indexed? property to Yes (no duplicates) to whichever fields you choose. Name, however, does have the opportunity to be duplicated.

Just an idea from another novice.

 

[TacoBueno]

hmm =/

Thanks alot for the attempt (and the free bump) but that wont work =( sigh

the database in the end is going to be more complicated than the example I showed.. I just need to know how to execute the form I gave while logically storing a main contacts table which points to an unduplicated email subscription list table.. all that creating and linking must be done from the same textbox that my end user will be using..

If I were programming this myself in C or something I would simply add a condition to my form asking if the data entered was in the emails table, if so, point/relate) to it, if not, create a new row in emails.. would be simple if I could handle the error message that pops up and tells me "the email must exist first in the email table" and replace it with something that entered it into the emails table for me (without user interaction).. sigh anyways..

I have pretty much convinced myself that my database design is fine for what I am wanting it to do. Its just the IO user interface I don't get I am guessing. (and if my design is flawed, then someone please give me a legitimate, my problem specific solution to my very simple question, I've been floating around forums other than this one with this question for over a week now and no one has been able to give me anything remotely close to what I'm looking for yet =( just a bunch of analogies that have nothing to do with what I'm asking...)

anyways, thanks for any help you might be able to give