Query not displaying all records

[Eddy Sincere]

This is driving me nuts. I have a simple (Allan Browne) search form that displays results from a primary and a few related tables. All of a sudden three records just aren't showing up.

I've compressed and attached the DB so you can see it.

Open Form SBMaintenance → All (In Quality Assurance group, under Documents heading) → Sort by Record Number

Note that record 104 is in the table with all the fields containing information. Also, there are 269 records.

Open form SBCntrlldDcmnts → Controlled Filter (In Documents group)



Note that record 104 is not included. Also, there are 266 records which means that three are missing.

When I bypass the forms and go straight to the query I get the same information. It's worked for the last 7 months and just started acting weirdly.

ControlledDocs59.zip

[rpeare]

front end won't work without a back end :P

[ssanfu]

The query "qryCntrlldDcmnts" is the problem. The record that has the "104" does not have a related record in table "DcPrRgltn". Therefore the "104" record is being excluded from the results.

To test:
If you change the join properties between "CntrledDcmnts" (the 1) and "DcPrRgltn" (the many) to option 2 (Include ALL records in "CntrledDcmnts" and only those records from "DcPrRgltn" where the joined fields are equal), the record with "104" appears.

[Eddy Sincere]

Ummmm... The back end is in the Zip file. CoontrolledDocs59_be. Just use the Linked Table Manager and it should work fine.

[Eddy Sincere]

Steve - I am totally swearing at myself right now. I knew it was something simple but after I look at it for too long my brain goes to mush. As always, thanks for taking the time to answer. Problem solved!

[ssanfu]

but after I look at it for too long my brain goes to mush

A frequent symptom of mine.

[DelbyJones]

Just want to say thank you for posting this question and responses. I was struggling with a similar problem and your answer helped me figure it out--FINALLY. I was about to go crazy.

When will I ever learn that whenever something isn't working right, it's all about the #$@% relationships!

Thanks again.

[ssanfu]

So true..... and in real life also