Urgent! One relationship change has made my database mess up!

[kate10123]

Hi there,

I have 22 records in my tutorial table which had links to a student table and a tutor table. It worked really well but I realised that two of the fields that were held in the student table needed to be moved to two separate tables as I wanted to keep a history of changes made.

So I moved these two fields:
AtRiskOfFailure
AtRiskOfDroppingOut

to 2 new tables:
tbl_StudentFailureHistory
tbl_StudentDroppingOutHistory

I linked these tables to the student table by studentID and ensured referencial integrity.

Then I went to the data entry form I use to input the tutorial data and changed the recordsource for these two fields but then when I ran the form, none of the records displayed!

I have attached the tables in a database to show what my problem is, I would really appreciate some clarification as to what I am doing wrong.

Thanks! and sorry for posting so often

 

[ASherbuck]

Open your blank form in design view, it says it's recordsource is query3. Open the recordsource in design view. Draw joins from your main table to those two new ones, set their join properties to All Records in the Student Table and those in the joined table that are equal (Middle radio button)

Should work, oh you'll have to redo you're drop downs now I think as they are blank to me but that may be how you want it.

Cheers

 

[kate10123]

Thanks a lot that worked!!!

 

[kate10123]

ha I have run into a slight problem with that solution...

I don't seem to be able to edit these combo boxes to actually select yes or no. I can see yes or no but cannot select them.

 

[ASherbuck]

Just a quick look over, you have no records in your tables.

I was hasty to tell you to make that join, I didn't review your structure to see what it was you were intending to do. I would rethink your table structure, I would link the failing or dropping ID to the student table not the other way around.