one to many relationship question

[cphillippe]

hello, i am having a problem with my relationships. i have the following 3 tables



Tbl_name
-nameid
-first
-last

Tbl_address
-addrid
-address
-city
-state
-zip
-nameid

Tbl_phone
-phoneid
-cell
-email
-nameid

i have a one-to-many relationship, and i keep getting the following error when i try to imput a new record from a form that has all the fields from each table

you cannot add or change a record because a related record is required in table 'Tbl_name'
i think this error is because the foreign keys are not being updated from their primary key respectively and i don't know why

[June7]

The usual arrangement is 1 form for data entry/edit to one table.

Use form/subform arrangement of data entry to related tables.

Main form bound to tblName and subforms for the dependent tables.

Exactly what is the relationship here? Each name can have multiple addresses and multiple phone numbers and multiple emails?

[orange]

Your tables and relationships are really a HOW you have modeled and designed your database to support your "business". In order to comment on the tables and relationships, and their applicability, readers must have an appreciation of the "business" involved.
So I recommend you tell us more of "the business" in plain, simple English. I also suggest you do some research on Normalization, Primary Key, Foreign Key and Referential Integrity ---fundamental database concepts.

Welcome to the forum and good luck with your project.

[cphillippe]

Well I was trying to design a form that inputs customers name address and contact info. I guess it would be one to one? I broke it into multiple tables because I have another form that doesn't require you to input all the info but for example tblname and tblphone

[orange]

From a "business"/plain English perspective:
-do all records in tblName relate to Customers, if so possible name for table could be tblCustomer
-readers have no context of the business --retail,products/services... or a mailing list.
-have you considered mailing address, physical location address, ship to address, billing address (depends on extent of your business)
-do/could you have more than 1 phone (phone type)

More info on "what" your database contains/supports would help with context.
Good luck with your project.

[cphillippe]

ok, here is my basic DB, i've kept it small for the purpose of just trying to figure out why my form is not updating each foreign key with the correct primary key when i create a record.Database1.zip

[davegri]

Database1-davegri-v001.zip
Here you go.
Set referential integrity to links.
Reworked form to hold a tab control with address on one tab and phone on the other.

Look at the tables. You were confused as to how links worked, your thinking was backwards. The Many side needs a link to point back to the one, but the link in the many is NOT the primary key in that many table.

[cphillippe]

i was trying to avoid that tabbed look, and just have a single form design. i see what you did. is that proper db design?

[davegri]

Design depends on your needs. I adapted it to your original table setup which was one name to many addresses and many phone numbers.
If there's only one phone number and one address, it can all be in one table, and all on one form with no tab or subform required.
Even if there are two phone numbers, you can have 2 or more fields in the single table; for example, HomePhone, MobilePhone, WorkPhone. Same for addresses.

[cphillippe]

how would i set it up like that? my original post was not correct, im just trying to make a one-to-one relationship with 3 separate tables

[June7]

Why use 3 tables when one serves?

[davegri]

I believe the general consensus of advisors here would be to use one table in your situation, unless you have more information for your needs that might dictate otherwise.

[cphillippe]

well i have two forms for input. one form inputs all for tblname, tbladdress, and tblphone. The second form inputs data from just tblname. two seperate forms for 2 different purposes. i was trying to reduce redundant data

[orange]

Always start with a description of what you are trying to support. Model your business subject matter and relationships based on business rules. Design and test your setup. Then move to forms.

[davegri]

well i have two forms for input. one form inputs all for tblname, tbladdress, and tblphone. The second form inputs data from just tblname. two seperate forms for 2 different purposes. i was trying to reduce redundant data

Still sounds like one table would do just fine.
A form doesn't have to show all fields in the table, just the fields desired for that form.