Defining Relationships between two tables

B

Bernadette Wayne

I have 2 tables. One has various fields including
Lastnames of Parents and a Family ID field (Autonumber)
The 2nd table also has a Family ID field (autonumber)and
a field called 'Children's Names'. I have set up a
relationship between the two Family ID fields, but it
will only let me type in one child's name only. Possibly
because it is one to one relationship. What i want is to
be able to enter several children belonging to the one
family in the 2nd table which will be stored in the
Family record.
How should I adjust things to have a one to many
relationship ie one family name and many childrens names
in the one record.

Thanks for any help
 
T

Tom Wickerath

Hi Bernadette,

An autonumber primary key should be joined to a number (long integer)
foreign key. I would first delete the relationship, and then rename the
Family ID field in the children's table to something like ChildID (or
pkChildID). Set it as a primary key if it is not already.

Then add a new number (long integer) field and name it FamilyID (or my
preference is fkFamilyID to indicate that it serves as a foreign key). Create
a relationship between your existing Family ID field in the parents table
with this new long integer field. That should do it.

If my answer has helped you, please sign in to Microsoft's Online Community
and mark my post as "Answered".


Tom
http://www.access.qbuilt.com/html/expert_contributors.html
___________________________________________

:

I have 2 tables. One has various fields including
Lastnames of Parents and a Family ID field (Autonumber)
The 2nd table also has a Family ID field (autonumber)and
a field called 'Children's Names'. I have set up a
relationship between the two Family ID fields, but it
will only let me type in one child's name only. Possibly
because it is one to one relationship. What i want is to
be able to enter several children belonging to the one
family in the 2nd table which will be stored in the
Family record.
How should I adjust things to have a one to many
relationship ie one family name and many childrens names
in the one record.

Thanks for any help
 
G

Guest

Thank you for your advices. When I enter the names of
the children how can i get the system to automatically
link the children (Long integer number field) to the
autonumber generated without having to type the number in
for each child.

Thank you again
 
T

Tom Wickerath

This is kind of hard to answer, since I do not have a copy of your database
to look at the issue. Perhaps an example will work better, which you can
reverse-engineer and try to match to your current database. Try this example,
and see if you can get it to work the way you want:

1.) First, locate a copy of the sample Northwind database, so that you'll
know where it is. It is likely already on your hard drive. Search for
Northwind.mdb

2.) Create a brand new database. Then use File > Get External Data >
Import... Navigate to the copy of Northwind that you located. Import two
tables: Categories and Products. By default, import relationships should be
checked when you click on the Options button.

3.) Select the Categories table. Click on Insert > Form > Form Wizard.
Select the CategoryName, Description and Picture fields. Before clicking
next, select the Products table from the Tables/Queries dropdown. Select all
fields except the ProductID and SupplierID fields. Note: There is no reason
to display autonumber fields to your users--this will often times just lead
to confusion on their part. This is why I've had you skip the CategoryID and
ProductID fields. We didn't include the Suppliers table in this example, so
you shouldn't add the SupplierID field either.

4.) Click Next three times, accepting the default choices each time.

5.) At the last screen of the wizard, I recommend naming the new form and
subform something like frmCategories and fsubProducts. Use whatever names
work for you.

You should be able to add a new product, for any given category.

If you still cannot get it to work in your database, and you are willing to
send me a copy, I will take a look at it for you. Compact the database first
(Tools > Database Utilities > Compact and Repair). If you know how to add it
to a .zip archive file, then please do so. Then send it to me: AOS168 @
comcast . net (<--Just remove the spaces in this e-mail address).

Tom
___________________________________________

:

Thank you for your advices. When I enter the names of the children how can
i get the system to automatically link the children (Long integer number
field) to the autonumber generated without having to type the number in for
each child.

Thank you again

___________________________________________

:

Hi Bernadette,

An autonumber primary key should be joined to a number
(long integer) foreign key. I would first delete the relationship, and then
rename the Family ID field in the children's table to something like ChildID
(or pkChildID). Set it as a primary key if it is not already.

Then add a new number (long integer) field and name it FamilyID (or my
preference is fkFamilyID to indicate that it serves as a foreign key). Create
a relationship between your existing Family ID field in the parents table
with this new long integer field. That should do it.

If my answer has helped you, please sign in to Microsoft's Online Community
and mark my post as "Answered".

Tom
http://www.access.qbuilt.com/html/expert_contributors.htm l
___________________________________________

:

I have 2 tables. One has various fields including Lastnames of Parents and a
Family ID field (Autonumber) The 2nd table also has a Family ID field
(autonumber)and a field called 'Children's Names'. I have set up a
relationship between the two Family ID fields, but it will only let me type
in one child's name only. Possibly because it is one to one relationship.
What i want is to be able to enter several children belonging to the one
family in the 2nd table which will be stored in the Family record. How should
I adjust things to have a one to many relationship ie one family name and
many childrens names in the one record.

Thanks for any help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top