I feel like this should be easy but I cannot seem to figure out the answer. I have a database in Access 2010 that tracks students in field placements at different agencies. Each student completes two placements (Concentration and Foundation).
I have a field placements table (Agencies) where each agency has a unique id (AID). I have a student table (Students) where each student has a unique ID (SID) as well as an id for each placement (CYPID and AYPID) that pulls the agency id (AID) using a combobox on an associated form.
My issue is that I need to create a query to then fill a report that shows all students regardless of placement type (Foundation or Concentration) for each agency. When I try to create the query I cannot use AID to pull student data using both CYPID and AYPID. If I use only one (CYPID or AYPID) it works fine but not when I use both. ANy help would be appreciated. Thanks in advance!
Thanks for the reply, Sam but I still cannot get the query to return what I need.
If I do the left join with AID connected to either CYPID or AYPID individually, the query works fine. However, as soon as I join AID to either CYPID or AYPID (whichever one of the two is not joined already) the query fails to display any data.
I need to be able to see all students regardless of placement type (concentration or advanced) that are currently placed with each agency.
Any further advice would still be appreciated. Thank you.