Greg Smith
GregSmith at starband.net
Fri May 14 11:05:09 CDT 2004
Hi everyone! I've got a puzzler here that makes no sense to me. Access 97. SP "I've Lost Count"... This is a large database with many related tables. There is one main table (Fee Book) with the Primary Key is [Fee Book #]. It is manually entered, but is unique by definition. In one of the related tables (Fee Book Ledger), this field is used for the related records in both tables. The Fee Book table is the main table while the Fee Book Ledger table holds related records. Referential Intregity is Enforced. Cascade Update Related Records is applied, but Cascade Delete Related Records is NOT. The Join Type is #2, "Include ALL records from 'Fee Book' and only those records from 'Fee Book Ledger' where the joined fields are equal. Obviously, in the Fee Book table, where [Fee Book #], the primary key, is required, it cannot be blank. Why am I ending up with orphans in the Fee Book Ledger table? No fee book number (in the Fee Book Ledger table...it's blank...but the field is NOT required...yet) but some of the other fields are filled. Just a blank [Fee Book #] field. And it has NO related record in the primary table since that table cannot have blank fields. And, to test it further, I went into the Fee Book Ledger table, entered a record, but purposly left the [Fee Book #] field blank, and it ACCEPTED it. Now I want to go home. I can make the [Fee Book #] field in the Fee Book Ledger table required, since it currently is not, but that may have some unintended consequences down the road that I'm not aware of at this moment. I'm going to test that in a few moments, but I wanted to see if anyone in this group had seen this type of oddball behavior before. TIA Greg Smith GregSmith at Starband.net