Solve : MS Access relationship not working!?

1.	Solve : MS Access relationship not working!?
Answer» Hi everyone, I'm new here and I'm not sure this is the place for this question, but here I go, I'm sort of DESPERATE! I have been trying to make a database that relates two tables by using an ID number as a primary field. How on earth do I make it understand that for every record in Table1, there must be a record in Table2 with the same ID number? I just enter data in Table1 (automatically defining an ID number for the record), but on Table 2, there appears no record! The relationship between the tables is a one-to-one relationship between the ID number field in Table 1 (primary key) and the ID number field in Table 2 (foreign key). I have also clicked the options Enforce Referential Integrity and Cascade Delete Related Records, what am I missing here? I APPRECIATE ANY COMMENT ON THIS, THANKS!!! Is the ID number you mentioned the number automatically assigned by Access? If so, why would you use it as the field to establish a relationship between two tables? Can you explain how that would make sense? Do you have any FIELDS, other than ID number, that are defined the same way in the two tables? What version of Access are you using?Yes, the ID number is automatically assigned by Access, and I use it as a link to establish a relationship between two tables containing different kinds of information for the same records. Let me be more clear: In Table1 I have as fields the IDNumber, First Name, Last Name, Address information, etc, about a list of students. In Table2, I have as fields the IDNumber, FirstName, LastName (same as in Table1), and other fields related to academic performance of those students (I used different tables for this because I was asked to do so by my Computer Science teacher, since this is a school project). The problem is that when I enter a new record (new student) in Table 1, there is no record APPEARING for that student in Table2. Is this supposed to work this way because of security reasons I don't get? Because if it is, then I will have removed the "Autonumber" option for the IDNumber field, and just change it to "Number" and make the user assign an IDNumber for each student, I'm just so tired of it not working!! (I'm using Access 2007, by the way).Quote from: juliet123 on October 15, 2009, 06:43:56 PM The problem is that when I enter a new record (new student) in Table 1, there is no record appearing for that student in Table2. Is this supposed to work this way because of security reasons I don't get? Because if it is, then I will have removed the "Autonumber" option for the IDNumber field, and just change it to "Number" and make the user assign an IDNumber for each student, I'm just so tired of it not working!! (I'm using Access 2007, by the way). What reference materials do you have? Are you taking a class on Access, or it being COVERED as part of a class containing other subject matter in addition to Access? I haven't done some techniques with Access for awhile, so my memory is a little blurred on some of it but I believe I can shed some light on this. Suppose you have 20 records in both tables. So, you have ID numbers 1 through 20 in each table. If you add a record to Table 1, nothing will happen in Table 2. New records will not get added to Table 2 in that manner. Generally, the technique of assigning relationships between two or tables to is used when two or more tables exist as a result of some entry of data into each of the tables. For example, if each student had a unique student ID # which was entered into two or more tables, that field could be used to build a relationship between tables and pull one or more fields from one table into another. If Queries are also part of your study of Access, I suggest you look at what queries can do, at how they can be used to extract data from one or more tables. Queries can be saved and opened anytime, like tables. OOhhh, okay I can see now how mistaken I was about Access.. haha So there is no way of doing what I wanted.. but it's fine, I'll just change the ID# field to "number" instead of "autonumber", since that would work great anyways. Thank YOUU very very very much, I was completely desperate since we hadn't seen so much of Access in my class... soo yeah THANK YOU!

Answer»

Hi everyone, I'm new here and I'm not sure this is the place for this question, but here I go, I'm sort of DESPERATE!

I have been trying to make a database that relates two tables by using an ID number as a primary field. How on earth do I make it understand that for every record in Table1, there must be a record in Table2 with the same ID number? I just enter data in Table1 (automatically defining an ID number for the record), but on Table 2, there appears no record! The relationship between the tables is a one-to-one relationship between the ID number field in Table 1 (primary key) and the ID number field in Table 2 (foreign key). I have also clicked the options Enforce Referential Integrity and Cascade Delete Related Records, what am I missing here?

I APPRECIATE ANY COMMENT ON THIS, THANKS!!!

Is the ID number you mentioned the number automatically assigned by Access? If so, why would you use it as the field to establish a relationship between two tables? Can you explain how that would make sense?

Do you have any FIELDS, other than ID number, that are defined the same way in the two tables?

What version of Access are you using?Yes, the ID number is automatically assigned by Access, and I use it as a link to establish a relationship between two tables containing different kinds of information for the same records. Let me be more clear: In Table1 I have as fields the IDNumber, First Name, Last Name, Address information, etc, about a list of students. In Table2, I have as fields the IDNumber, FirstName, LastName (same as in Table1), and other fields related to academic performance of those students (I used different tables for this because I was asked to do so by my Computer Science teacher, since this is a school project).

The problem is that when I enter a new record (new student) in Table 1, there is no record APPEARING for that student in Table2. Is this supposed to work this way because of security reasons I don't get? Because if it is, then I will have removed the "Autonumber" option for the IDNumber field, and just change it to "Number" and make the user assign an IDNumber for each student, I'm just so tired of it not working!! (I'm using Access 2007, by the way).Quote from: juliet123 on October 15, 2009, 06:43:56 PM

The problem is that when I enter a new record (new student) in Table 1, there is no record appearing for that student in Table2. Is this supposed to work this way because of security reasons I don't get? Because if it is, then I will have removed the "Autonumber" option for the IDNumber field, and just change it to "Number" and make the user assign an IDNumber for each student, I'm just so tired of it not working!! (I'm using Access 2007, by the way).

What reference materials do you have? Are you taking a class on Access, or it being COVERED as part of a class containing other subject matter in addition to Access?

I haven't done some techniques with Access for awhile, so my memory is a little blurred on some of it but I believe I can shed some light on this.

Suppose you have 20 records in both tables. So, you have ID numbers 1 through 20 in each table. If you add a record to Table 1, nothing will happen in Table 2. New records will not get added to Table 2 in that manner. Generally, the technique of assigning relationships between two or tables to is used when two or more tables exist as a result of some entry of data into each of the tables. For example, if each student had a unique student ID # which was entered into two or more tables, that field could be used to build a relationship between tables and pull one or more fields from one table into another.

If Queries are also part of your study of Access, I suggest you look at what queries can do, at how they can be used to extract data from one or more tables. Queries can be saved and opened anytime, like tables. OOhhh, okay I can see now how mistaken I was about Access.. haha
So there is no way of doing what I wanted.. but it's fine, I'll just change the ID# field to "number" instead of "autonumber", since that would work great anyways.

Thank YOUU very very very much, I was completely desperate since we hadn't seen so much of Access in my class... soo yeah THANK YOU!

Solve : MS Access relationship not working!?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment