Relationship Help

G

Guest

I have set up a very easy relationship. I have one table that has
information about families who receive financial aid. I have a second table
for the children of these family, since there is sometimes more than one
child, I was tired of typing in duplicate info. My family table has a family
# (auto number) as a key. My student table has the student # as a key. (Not
auto - 5 digit number)

I have created a form and a subform form. The family being the main form
and the student being the sub. I entered a few families in, with their
students using the subform. When I look at the table, the relationship is
there (I see the + and it expands to the students), but the information I put
in is not there. It is however in the student table all by itself.

I realize I don't know what I am doing or what it should look like. Any
help out there?
 
G

Guest

Ok, so it shows that I don't know what I'm doing. We are a non-profit so we
can't pay people to figure these things out for us like big companies do. I
am determined to learn this stuff though.

I tried adding the Family ID# to the student table, and it gave me errors
when I did that.

Family Table

Family ID #, AutoNumber - Key
Type of Aid, Text
Date of Application, Date/Time
Parent Title, Text
Parent First Name(s), Text
Parent Last Name, Text
Address, Text
City, Text
State, Text
Postal Code, Text
Home Phone, Text
Church / Organization, Text
Ministry Title, Text
Parent in Ministry, Text
Full or Part time, Text

Student Table

Student #, number - Key
Student 1st Name, Text
Student Last Name, Text
Grade, number
Start Payment Date, date
End Payment Date, Date
Tuition Amount, currency
approved, text
Parent Decision, text
Date of Completion, date
 
L

Lynn Trapp

Ok, so it shows that I don't know what I'm doing. We are a non-profit so
we
can't pay people to figure these things out for us like big companies do.
I
am determined to learn this stuff though.
Click to expand...

I understand fully. I work with a lot of non-profit organizations and
understand your financial constraints. You have come to the right place to
help you get the answers you need.
I tried adding the Family ID# to the student table, and it gave me errors
when I did that.
Click to expand...

What kind of error did it give you? What datatype did you try to set the
Family ID# to? Your Student ID# should probably be set to an AutoNumber
Datatype and, then add a Family ID# field to the Student table that is of
datatype Number (Long Integer). Give that a try and let me know the results.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
G

Guest

First of all, thank you for your time.

Second, I really don't want to change the student id# to auto, because I am
using real student ID's that have been assigned by our registrars office. I
will change it later if I have to, but this is what I am getting with the
family ID #.

It is currently in both tables. The first table is autonumbering it, so I
then use the same number in my subform for the student's record. When I try
to advance to the next entry, it tells me it can't save because there are
duplicates. I can't change the Main table to allow duplicates because it is
the key, and I have already set the second table to allow duplicates, but I
still get the error.

I just also tried changing the key to family id in the student table, but it
didn't work either

If you think I should start from scratch, give me some hints. I have not
been entering my records yet, as it still doesn't work.
 
G

Guest

Lynn,

Ok, I took a long break from all this. I walked around the building to
clear my head and sat back down to read my Dummies book, again. This time I
noticed that the author recomended using a wizard to make the table and
relationships. I tried it by making a new students table with the wizard,
told it to link to my existing family table, I prayed, and it worked. I
can't figure out the difference, the keys seem to be the same as what I had.
Must have been the prayer ;-)

Thank you so much for your help with this. I will certainly use this post
again, and again. You are a very patient person.

Sincerely
Kelly
 
G

Guest

Kelly,

I have been lurking in this thread, and have an idea or two. I'm glad to
hear it worked using the wizard, but maybe I can give some idea of what may
be going on behind the scenes. You were on track using FamilyID as a field
in the Student table. FamilyID is autonumber in the Family table, right? In
that case, FamilyID in the Student table must be of data type Number (table
design view). Sounds like you set FamilyID as the primary key field in the
Family table. FamilyID in the student table, then, would not be a primary
key. Instead, you would go to Tools > Relationships, add both tables, drag
FamilyID from one table and drop it on FamilyID in the other. Click "Enforce
Referential Integrity". Now make a form based on the Family table and
another based on the Student table. With the Family form open in design
view, drag the icon for the Student form onto it. You should now be able to
go to a Family record and add any number of students to the Student table by
way of the subform.
You do not add FamilyID to a record in the Student table. Because of the
relationship you established, each new record created through the subform
will have the same number in FamilyID as does the parent record (the one in
the Family table).
If you base the forms on queries based on the tables rather than directly on
the tables themselves, all of the above continues to apply. Just substitute
Query for Table in the above suggestions.
 

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

Similar Threads

auto number fields 2
How to query this 10
Setting up tables properly 1
Help in setting relationships in Access 3
One-to-one relationship 2
C# Skype silent connection 0
Alias table in relationship 5
Counting Records in A Group 5

Top