Why do some of my records fail to find their children?

[sueb]

I have a database with 4 tables (I've attached the relationship diagram).

I have two queries that show to the user the recordset that contains Accounts and their associated 18-1 data. (The "18-1" is the name of a State of California form; that's why it's so unhelpful. It just contains information about a patient's stay in our hospital.)

The query that displays the form (pic also attached) shows all the appropriate data fields.

The query that prints all those same fields sometimes does and sometimes doesn't find the very same data that the form query does.

How can that be?

Here's the form's query:

SELECT [18-1].[18-1 Index], Accounts.[Account Index], Patients.[Patient Index], Patients.ChartNum, [Family Name] & ", " & [Given Name] AS Name, Patients.[Family Name], Patients.[Given Name], Patients.[Date of Birth], Accounts.Account, Accounts.Payer, Accounts.Admit, Accounts.[Emergency Admit], Accounts.[Admit ICD9s], Accounts.[Admit Diag], Accounts.[Auth Rep], [18-1].[Creation Date], [18-1].[Level of Care], Accounts.Discharge, [18-1].[Ready for Review], [18-1].[Diag ICD9s], [18-1].[Current Diag], [18-1].[Current Condition], [18-1].[Planned Procedures], [18-1].[UR Nurse], [18-1].[UR Date Signed], [18-1].[Responsible Physician], [18-1].[Dr Date Signed]
FROM (Accounts INNER JOIN Patients ON Accounts.[Parent Index-Patient] = Patients.[Patient Index]) INNER JOIN [18-1] ON Accounts.[Account Index] = [18-1].[Parent Index-Account];
 

Here's the report's query:

SELECT [18-1].[18-1 Index], [UR Nurses].[UR Nurse], [Family Name] & ", " & [Given Name] AS Name, Patients.ChartNum, [UR Demographics].Sex, [UR Demographics].[Medi-Cal Number], [18-1].[Level of Care], Patients.[Family Name], Patients.[Given Name], Patients.[Date of Birth], Accounts.Payer, Accounts.Admit, Accounts.[Emergency Admit], Accounts.[Admit ICD9s], Accounts.[Admit Diag], Accounts.[Auth Rep], Accounts.Discharge, [18-1].[Diag ICD9s], [18-1].[Current Diag], [18-1].[Current Condition], [18-1].[Planned Procedures], [18-1].[UR Date Signed], [18-1].[Responsible Physician], [18-1].[Dr Date Signed]
FROM (Accounts INNER JOIN ([UR Demographics] INNER JOIN Patients ON [UR Demographics].[Parent Index-Patient] = Patients.[Patient Index]) ON Accounts.[Parent Index-Patient] = Patients.[Patient Index]) INNER JOIN ([18-1] LEFT JOIN [UR Nurses] ON [18-1].[UR Nurse] = [UR Nurses].[Nurse Index]) ON Accounts.[Account Index] = [18-1].[Parent Index-Account];
 

Images:
Relations

Form

[sueb]

Oh, never mind! (I really should change my avatar to whatever the international symbol is for "Unregenerate Idiot".)

Just as I hit the Submit button, it occurred to me that the join between the UR Demographics table and the Patients table might be a constriction, since most of the patients don't have UR demographics data entered for them.

Sure enough, once I made a query that gathered what demographics data there is to the entire Patients' table, and used that in my reports query (instead of just bare joins), everything work perfectly! (I actually also then simply used the same query for both the form and the report. duh!)

Please feel free, however, to offer any advice you have after looking at this. There is so much free-floating expertise in this group.

[TheSmileyCoder]

Take comfort in knowing that the only difference between an expert and you is that experts have made more mistakes then you :P

I can still not write "full" code that will work in the first go, although I tend to write longer and longer codebits without errors. Extensive testing and debugging is still required, and I am getting good at that :P

[NeoPa]

Hi Sue,

It's not very common for an OP to mark one of their own posts as Best Answer, but I think it would be appropriate in this case for post #2.

And nice job to smiley for updating the question to show more clearly :-)

All good.

[sueb]

Thanks, Smiley, for fixing my post. I forgot that I could embed pictures right in the post.

And, of course, you're right: the better you get, the more destruction you've left in your wake, and the more devastating errors you have yet to come! (somehow, that makes it all more fun) I just felt so really stupid because I had spent so very many hours on this problem without getting even a glimmer of where the real flaw lay. I'm sure, too, that getting my problem ready to present here was a big part of helping me see the light. I don't know what I'd do without this forum.

NeoPa, since you think it's appropriate, I'll go ahead and mark my answer. When I'm searching in other threads for answers, I find it disheartening when the OP has not found any of the responses to fit their needs (or, at least, they haven't said so). Actually, that's one improvement I'd like to see: when a thread has a "best answer", it would be nice if that were reflected on the sort of "home page", where all the threads are listed. Currently, each thread has some stats listed (replies, views, etc.), but it would be very helpful if it had a little star or something once it also had a best answer.

Use your influence, guys! :D