I have three tables:
Code:
People
ID_Person (PK, AN)
num_Student Number
txt_Last Name
txt_First Name
Code:
Grad_Students_Faculty
ID_Grad Council (PK, AN)ID_Person
txt_Faculty Name
txt_Relationship
Code:
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