Parent Records Only Print If SubRecords Exist

[Steve Wormuth]

Hello,

In a database of medical records, I have a table of symptoms. Each symptoms
can have multiple notes entered about that symptom (one-to-many) in a
separate table. The problem is that when I print a report of symptoms, the
report only prints the symptom if it has a note (subrecord) attached. The
report does print the symptom and related notes correctly, but excludes a
symptom if it has no subrecord.

How would one correct this?

Thanks,
Steve

 

[Marshall Barton]

In a database of medical records, I have a table of symptoms. Each symptoms
can have multiple notes entered about that symptom (one-to-many) in a
separate table. The problem is that when I print a report of symptoms, the
report only prints the symptom if it has a note (subrecord) attached. The
report does print the symptom and related notes correctly, but excludes a
symptom if it has no subrecord.

Change to report's record source query to an outer join
instead of the Inner Join you have now. This is easy to do
in the query design grid by right clicking on the line
between the two tables and then selection for include all
records from symptoms and the matching records from notes.

 

[Steve Wormuth]

Get an error message, "Join Expression Not Supported". There are multiple
joins in the query...

Steve

 

[Marshall Barton]

If you have nested joins, then you have to change the lower
levels to outer joins too. Help states that you can not
nest an inner join in an outer join. Think it through and
make sure that you specify what you really need.

 

[Steve Wormuth]

Hello,

Changing all joins to outer joins still does not work. It says the join
format isn't supported.

This seems to be so simple. I want a report which allows you to select a
patient, then show the report for that patient. The top section of the
report shows patient information, below that is a list of symptoms, and
below that are any notes entered about those symptoms. The report works
great, except that if there isn't a note for a symptom, it excludes the
symptom entirely... I am lost here. This seems like it should be standard
fare...

Steve

 

[Marshall Barton]

Something smells a little fishy here. I think I'll have to
see the actual query's SQL before I'll believe it can't be
done. Copy/Paste the SQL into your reply and I'll crawl
through it to see if I can spot anything funny. A bit of
explanation about table keys would also help.