Relationships and finding data

[Calantha]

Hello,

I've been away from the forum for awhile attempting to figure things out on my own, but I've run into another roadblock.

I have two databases 1) Student Database 2) Schools Database (with an Information tahle and a References table)

The student database includes all student information. The schools database includes information on schools our students have come from as well as contacts.

When a student applies to our school they require two reference letters. These reference letters are often from teachers.

In the student database there is an area where I enter the information of the references (name, school, email, phone, etc.).

In the schools database I have a table which shows all of the information on the school, and another table which is for all of the references. The references are related to the schools table so that I know which references belong to which school, this is fine.

However, what I would like to do is when I receive an application, I would like to enter the reference data in the student database and have that correspond in the reference table of the schools database. This seems easy enough.

However, if I enter the reference data in the student database, it then shows up in the reference table in the schools database but how does it know what school it is from unless after that I go and look up the school ID number and enter it?

Is there anyway I can do this without having to go look up the school's ID number? Or are you always supposed to know the ID numbers?

I hope this was clear enough.

[T-Square]

In two words: Value lists.

Create a value list based on the schools table using SchoolID and SchoolName and link this to the SchoolID field on the Student layout. You'll see the ID, but do not need to remember it.

[Calantha]

Wow! That is awesome! Thank you!

[Calantha]

I'm still stuck. Let me see if I can explain this properly.

Databases involved:

Student Database

Schools Database

Tables involved:

Student

School

References

Each student has 2 references.

Each reference belongs to a school.

1 School can have multiple references (teachers referencing students), 1 reference can refer multiple students, 1 student has multiple references.

What I need to accomplish:

The reference table must be related to the Schools table so that I can see how many and which references come from which schools.

Schools table shows me a list of all the references from that school.

I would like the references table to show me how many students they have referred. So the reference table must be somehow related to the student database.

The student database has to show me the information for the references which have referred that student.

So:

Schools contains:

School Information

List of References from that School

References:

Reference Information

List of Students they have referred

Students:

Student Information

Information of References (2)

Is that clear? I've been fighting with this one. I've attempted a bunch of different things... the last of which was doing away with the references table and using the Students table to hold the reference information and then using a portal in the Schools table to list the references. The only issue with that is that one reference can refer multiple students, therefore the list would contact duplicates (which I don't want).

All your help is very much appreciated.

I stil love the value list idea!

[comment]

If a reference can have multiple students, you will need either another table joining students to references, or make the student key field in References a multi-line key.

I would go with the second option, but it means you will not be able to enter data into the key field using a drop down list.

[Calantha]

A multi-line key. Is that a key that has two references?

I attempted to create a relationships from References RefID to the student database of RefID_1 and RefID_2 for both references but it didn't work...

[comment]

A multi-line key field has multiple values, separated by carriage returns. A record in another table will be considered related if it matches any single line of the multi-line key.

For example, if you have:

SCHOOLS:

1 Yale

2 Harvard

3 MIT

STUDENTS:

1 Jones

2 Smith

3 Doe

and there is a reference from Harvard relating to Jones and Doe, its record in REFERENCES would look like this:

ReferenceID: (serial number)

SchoolID: 2

StudentID: 1¶3

Note that a multi-line key MUST be of type Text, even if the other ID fields are Number fields.

[Calantha]

Interesting.

It will take me awhile to wrap my head about that one and when I do I will try it out.

Thank you for your help.