Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Missing record (2000 (Win2000))

    I've lost a record. Actually the record exists, but it's missing in action from my query. I'll try to explain: Two tables (tbl_Courses and tbl_Criteria) have a many-to-many relationship via a junction table, tbl_LinkCourseCriteria. Each of our 234-plus courses supports one or more "criteria," and I've created a report to display those courses grouped by criterion number. (Many thanks to PaulK for his invaluable help.) Table Courses also has a one-to-one relationship with yet another table (tbl_TrainingResources) that has other fields I need for the report. All four of these tables appear in the design grid for the query I'm about to discuss.

    My first effort to create the report had a few errors, most of which I fixed without further complication. However, I managed to damage one course--number 165. The problem was that I had marked that course as supporting Criteria 2 and 5, when I should have marked it for Criterion 2 alone. In trying to fix that problem, I deleted the record for #165 from...something. This was four days ago, and my memory fails me, but I think it was from tbl_Courses. It appears that was a really dumb thing to do, because now #165 refuses to appear in the query's datasheet view. That means, of course, that #165 does not appear on the report either, since the query is the datasource for the report.

    Somehow I got #165 back into tbl_Courses, but it still will not appear in the query's datasheet view nor in the report. I opened the query in datasheet view and re-ran the query--but with no effect. This course never got flushed from tbl_TrainingResources, and I see it in tbl_Courses. If memory serves, I believe I simply re-keyed the record for #165 into tbl_Courses. It seems that approach isn't working. How can I fix this, short of dumping tbl_Courses and recreating it with a make-table query? (I think that's how I created the table in the first place, but then I had to use the Look-up Wizard to create a sub-table and key in the numbers for the criterion/criteria each course supports. I'd really, really like to avoid having to do that job all over again, if you know whadda mean.)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing record (2000 (Win2000))

    Lucas,

    What is the field that links tbl_Course to the other tables? Is it an AutoNumber field or a Course number that you can enter and modify?

    If it is an AutoNumber, the recreated record in tbl_Course has a new value in this field; old AutoNumber values will not be re-used. So you need to update tbl_LinkCourseCriteria and tbl_TrainingResources, because they will still contain the old value of the linking field.

    BTW, have you set referential integrity with cascading updates and perhaps cascading deletes for the relations between the tables? Referential integrity can help prevent problems like this.

    HTH,
    Hans

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Missing record (2000 (Win2000))

    It's an AutoNumber field (CourseID) in tbl_Courses and in the related tables: tbl_TrainingResources and tbl_LinkCourseCriteria.

    As for referential integrity:
    1. The many-to-many relationships between tbl_Courses and tbl_Criteria (via tbl_LinkCourseCriteria) are set up to enforce referential integrity, both for updates and deleting.
    2. The one-to-one relationship between tbl_Courses and tbl_TrainingResources displayed in the design grid for the query qry_selCourseCriteria. However, when I opened the Relationships window, I saw no line connecting those two tables. So I created (re-created?) the relationship. Now there's a line connecting the two tables, and the relationship type is one-to-one (CourseID to CourseID). I tried checking the box for Enforce Referential Integrity, but I get an error message: "Data in table 'tbl_Courses' violates referential integrity rules.... Edit the data so that records in the primary table [tbl_TrainingResources] exist for all related records."

    Sorry, but I just don't follow the logic here. Can you help me understand what I have to do? Also, how do I "update tbl_LinkCourseCriteria and tbl_TrainingResources..."?

    Update: I think I'm beginning to see at least part of the problem. In tbl_TrainingResources, the course in question (#165) has CourseID number 167. That was the number this course carried with it going into this project. However, in tbl_Courses (where I monkeyed around with this course), #165 has CourseID number 243! We're talking about the same course, but the Primary Key (i.e., CourseID) numbers are no longer identical. So...how can I fix this little mess? And--given this set of circumstances--is there any way to enforce referential integrity (updates and deletions) short of starting all over?

    Thanks for your patience, Hans!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing record (2000 (Win2000))

    Lucas,

    The CourseID field should be an AutoNumber field in tbl_Course only. In the related table tbl_TrainingResources (and in tblLinkCourseCriteria), it should be Numeric (Long Integer). You can change the data type of a field from AutoNumber to Numeric without loss of existing data, but you'll have to delete relations involving the field before, and re-create them afterwards. Access won't let you change the data type of a field that is part of a relation.

    Next, change the CourseID from 167 to 243 in tbl_TrainingResources (and, if necessary, in tblLinkCourseCriteria - probably not, since referential integrity is at work there). That should now be possible, since CourseID is no longer an AutoNumber field there.

    Then, use the Find Unmatched Query Wizard to find out if there are still records in tbl_Course with a CourseID that doesn't occur in tbl_TrainingResources. Do the reverse also. If you find unmatched records, repair it by either changing CourseID in tbl_TrainingResources to the correct value from tbl_Courses, or add a new record with the correct CourseID.

    Once you've done this, you should be able to set referential integrity for the relation from tbl_Courses to tbl_TrainingResources. In this case, I'd set cascading updates, but not cascading deletes (to prevent users from accidentally deleting course information).

    Regards,
    Hans

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Missing record (2000 (Win2000))

    Yes! Yes! It works! Thank you, Hans! <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •