Lookup field Query

[hnhpak]

I have student database and making parent teacher appointments form

We have three tables / quaries
1. Students
2. Guardians and Students
3. PTM Appointments

In PTM Appointments table first with lookup field i select student name from student table

each student have two guardians father and mother or may be more or less

Then we have to select the name of guardian with whom meeting is set by lookup field with multiple records selection, it may be only father only mother or father and mother

I want in lookup field only the names of parents/ guardians of student first selected not all guardians of all students,

I have a problem of making query for the lookup field for the names of guardians from table: [students and guardians] for only Student ID in current record of table PTM appointments.

I will be grateful if any one can help me in this regards

Haseeb ul Hasan

[rpeare]

Your table structure doesn't currently support what you want to do.

You can do this a couple of ways but the easiest for a newer user is to have:
PK = Primary Key
FK = foreign key

1. A table for students including a PK (autonumber)
2. A table for guardians Including a PK (autonumber)
3. A table linking students to guardians with a PK (Autonumber), a FK to the Students Table, a FK to the Guardians table, this will allow you to assign multiple guardians to each child.
4. your PTM appointments table with a PK (Autonumber) a FK to the students table.
5. a PTM attendees with a PK (Autonumber) a FK to the PTM Appointments table and a FK to the guardians table, this will allow you to have multiple guardians assigned to a single PTM meeting.