Linking Multiple Form information together

[Sawyer05]

i've designed these tables

tblStudentdetails
tblEmergencycontact
tblSchoolinfo
tblGeneralenquiry
tblSupportNeeds



Each table will have its own form that will be opened by individual buttons in my student details form.

I ran a test and entered details into my student form and my Emergency contact form but i don't know how to make all the information link to the same student.

I have attached a screenshot of my relationships below.


Any help i can get will be much appreciated!

[R_Badger]

You should use queries to join data take a look here for a bit of SQL guidance on it http://www.w3schools.com/sql/sql_join.asp

[Missinglinq]

I assume that the tblStudentdetailsyou you listed is actually the tblPersonalInfo that is shown in the attached image. At any rate, Age is a Field that should never be stored in a Table. If the age is stored, after the student has a birthday, it will be incorrect! The only exception to this would be if it were a static Field, which is to say one that once entered will never change. An example would be 'age at which student was first enrolled' or 'age when symptoms first appeared.'

'Age,' as in 'current age,' should always be calculated and displayed in an Unbound Control on Forms or Reports. This type of calculation has to take into account whether or not a person has celebrated their birthday for the current year. One formula used is

DateDiff("yyyy", [DOBField], Date()) - IIf(Format$(Date(), "mmdd") < Format$([DOBField], "mmdd"), 1, 0)

Linq ;0)>

[Sawyer05]

I assume that the tblStudentdetailsyou you listed is actually the tblPersonalInfo that is shown in the attached image. At any rate, Age is a Field that should never be stored in a Table. If the age is stored, after the student has a birthday, it will be incorrect! The only exception to this would be if it were a static Field, which is to say one that once entered will never change. An example would be 'age at which student was first enrolled' or 'age when symptoms first appeared.'

'Age,' as in 'current age,' should always be calculated and displayed in an Unbound Control on Forms or Reports. This type of calculation has to take into account whether or not a person has celebrated their birthday for the current year. One formula used is

DateDiff("yyyy", [DOBField], Date()) - IIf(Format$(Date(), "mmdd") < Format$([DOBField], "mmdd"), 1, 0)

Linq ;0)>

Hi MissingLinq, thanks for the reply.
Yes the Studentdetails and Personalinfo are the same, i changed the name and forgot to update this post!
i never thought about the age not changing!
the reason for it is for events i have to know what students are under 16
if i don't have an Age field, how do i generate a query to give me the list of students under 16?

[ssanfu]

In 5 out of the 6 tables, the PK is "ID". That will cause confusion when creating queries. I would recommend renaming them something like Emerg_ID, Skill_ID, School_ID, etc

how do i generate a query to give me the list of students under 16?

You calculate it in a column in the query. Here are a couple of other ways to calc the age:
http://access.mvps.org/access/datetime/date0001.htm

Good luck

[Missinglinq]

If, for instance, you want a Form to return all students under the age of 16

1. Create a Query from your TblPersonalInfo Table
2. In Query Design View (aka the Grid) go to an empty Field and in the 'Field' box, enter the code given below
3. Now, go down to the 'Criteria' box and enter < 16
4. Save the Query and name it something appropriate
5. Create a Form, based on this Query

AccurateAge: DateDiff("yyyy",[DateOfBirth],Date())+Int(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd"))

It should only return Records for students who are under the age of sixteen.

Linq ;0)>

[Sawyer05]

Thanks for the advice ssanfu, i've changed each ID accordingly

thanks Missinglinq, its working perfectly!

now another question.

Now that i have all my student info, what would be the next step to enrolling the students onto a course.
i've set up a course table of (Course_ID, Course Name)

note: before enrolling on a course most students (99%) take a trial during there school year before enrolling
so i've set up a trial table
there's numerous types of trials that happens a view times a year so my table looks like this
(Trial_ID, type of link, day of trial, group, session)

the type of link, is how the trial is determined (same as trial name)
Day of trial is determined by which trial it is
the group, is the class group as there can only be 10 students per group so there could be up to 4 groups in some trials happening on the same day but doing different subjects
the Session is which trial is occuring as there can be up to 4 a year and dates are not decided until later on so the session list would be named simply 1,2,3,4.

So how would i go about linking the tables together.
What fields would i need added?
and once the trial is done, how would i take the student from that to a full time course, if a place is offered by the college and a student accepts.