Updatable Query from three tables and later a form

[trb5016]

I have three tables:

People
ID_Person (PK, AN)
num_Student Number
txt_Last Name
txt_First Name

Grad_Students_Faculty
ID_Grad Council (PK, AN)ID_Person
txt_Faculty Name
txt_Relationship

Grad_Students_Funding
ID_Grad Fund (PK, AN)
ID_Person
txt_Academic Semester
num_Academic Year
txt_Fund Name

I need a query (which will be used as a continuous form later that is updatable that will show:


Student Number; Last name; First Name; Advisor (see below); Semester; Year; and Fund Name

Advisor will display the Faculty Name from the Grad_Student_Faculty Table where the txt_Relationship="Advisor" (Each student can only have one advisor but many faculty members with other titles - hence that setup.

There is only one record per student per Semester/Year for the Grad Students Funding table. So if I filter this query for "Fall" and "2008" each student would only show up once (and only need to show up once because the tables with the one-many relationships will only have one matching record.

I've done this in the past with an older layout where the Advisor was its own field under Grad_students funding and used code to populate the ID_Person and the semester and year for the Grad Students Funding table so I then filtered it and it worked fine.

Now that there's three queries I can't figure out how to use even a cheap workaround like that one.

I know this is long and a shot in the dark but any advice would be appreciated.

P.S. This is an Access Front End with the Tables being linked to a SQL server

[NTC]

make the query "Advisors" using the table Grad_Students_Faculty the criteria of course is "Advisors" so this query results is only those records where the faculty is the advisor.

then make a query using the People Table, Advisors Query, and Grad Students Funding table- joining at the common ID_person field...