Hi there! I am a novice trying to teach myself Access as I go because it's necessary for a project. I really hope someone here can help me out with the following problem. It seems very simple and I don't know why it's not working, but, it isn't 
I have a table ("tblStudentInfo") that contains, yes, information about students. I have another table ("tblAgencyAddresses") that contains information about outside agencies at which the students do field work.
Now, there are first-year students and second-year students. Since a student usually works at different agencies in the first and second year, I want to have two different fields in tblStudentInfo, "FirstYearAgency" and "SecondYearAgency". I want to have a 1-to-many relationship between both the "AgencyName" field in tblAgencyAddresses and the "FirstYearAgency" and "SecondYearAgency" fields in tblStudentInfo, and I want to enforce referential integrity in both cases.
However, Access 2003 only lets me set one of the 1-to-many relationships. It doesn't matter whether I do FirstYearAgency or SecondYearAgency first; whichever one I do first, it works fine. But when I try to add the other one, it tells me that it can't enforce referential integrity for that one. I don't see why I shouldn't be able to have multiple 1-to-manys where the "many"s are different fields on the same table, and the "1" is shared. It sounds like a common situation. What am I missing here?
I checked to see whether everything involved is "text" data type; it is. Both FirstYearAgency and SecondYearAgency have identical information within them, that being nothing -- in part of my attempt to fix this, I deleted the columns and recreated them -- so that is not the issue either
Any help would be massively appreciated!!!
I have a table ("tblStudentInfo") that contains, yes, information about students. I have another table ("tblAgencyAddresses") that contains information about outside agencies at which the students do field work.
Now, there are first-year students and second-year students. Since a student usually works at different agencies in the first and second year, I want to have two different fields in tblStudentInfo, "FirstYearAgency" and "SecondYearAgency". I want to have a 1-to-many relationship between both the "AgencyName" field in tblAgencyAddresses and the "FirstYearAgency" and "SecondYearAgency" fields in tblStudentInfo, and I want to enforce referential integrity in both cases.
However, Access 2003 only lets me set one of the 1-to-many relationships. It doesn't matter whether I do FirstYearAgency or SecondYearAgency first; whichever one I do first, it works fine. But when I try to add the other one, it tells me that it can't enforce referential integrity for that one. I don't see why I shouldn't be able to have multiple 1-to-manys where the "many"s are different fields on the same table, and the "1" is shared. It sounds like a common situation. What am I missing here?
I checked to see whether everything involved is "text" data type; it is. Both FirstYearAgency and SecondYearAgency have identical information within them, that being nothing -- in part of my attempt to fix this, I deleted the columns and recreated them -- so that is not the issue either
Any help would be massively appreciated!!!
