Record Does Not Show If No Detail

[Caryn]

I have a table with client names and another table with the family members of
the client. Those are connected via a relationship. I have a query to
generate a list of only those clients who are active as of a certain date.
If I create a report using the wizard, not including the family members every
record shows up on the report. If I create a report including the family
members (which show up in the detail section of the report design), only the
records for clients who have family members appear. On this generated
report, the client will not appear if he does not have any famly members.
How do I make all the records appear along with the family members?

Thanks in advance!

 

[KARL DEWEY]

You need to open report in design view, right click and scroll down to select
Properties.
The record source will be a SQL statement that is an INNER JOIN.
If the join is ---
FROM Client INNER JOIN [Family Members]... the change to read
FROM Client LEFT JOIN [Family Members]...

If the join is ---
FROM [Family Members] INNER JOIN [Client]... the change to read
FROM [Family Members] RIGHT JOIN [Client]...

 

[Caryn]

It worked! Now I have another problem. If the client doesn't have any
family members the report still prints a blank detail line which only has the
labels showing. How do I make this line only appear when there is data in
the the family members?

You need to open report in design view, right click and scroll down to select
Properties.
The record source will be a SQL statement that is an INNER JOIN.
If the join is ---
FROM Client INNER JOIN [Family Members]... the change to read
FROM Client LEFT JOIN [Family Members]...

If the join is ---
FROM [Family Members] INNER JOIN [Client]... the change to read
FROM [Family Members] RIGHT JOIN [Client]...

--
Build a little, test a little.

I have a table with client names and another table with the family members of
the client. Those are connected via a relationship. I have a query to
generate a list of only those clients who are active as of a certain date.
If I create a report using the wizard, not including the family members every
record shows up on the report. If I create a report including the family
members (which show up in the detail section of the report design), only the
records for clients who have family members appear. On this generated
report, the client will not appear if he does not have any famly members.
How do I make all the records appear along with the family members?

Thanks in advance!