Records with the same last name disappear...

  • Thread starter Thread starter Caryn
  • Start date Start date
C

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. I
have created a report which lists both the clients and the family members
with a left join. However, if there are multiple clients with the same name
that appears in the query, only the client who is last alphabetically
appears. Any other records with that last name are missing from the report.
For example, Nicole Smith appears, but Kelly and Jessica Smith's records do
not. They appear in the query, but not the report. Does anyone have any
idea what could be causing this?
 
Caryn

One of the settings/properties available in reports is "Hide Duplicates".
Do you have that property set to Yes for the control holding the name?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
That is why I ALWAYS add an autonumber field (like ClientID) to my tables.
This way, you can use the ClientID as the foreign key in the family members
table and group by the ClientID in your report.
 
More info, please...

How is your report designed? Are you using GroupBy?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
I have a query to generate a list of only those clients who are active as
of a certain date. I have created a report which lists both the clients and
the family members with a left join.

Your post mentions a query with date criteria and also a left join for
family members. Is this an external query and an internal SQL statement in
the report or all in one query?

Post the SQL of the query.
 
I created a query in the query tab to limit the clients to only those active
in December going forward. The Query Name is "Payment Due to Doctor."
However there is also a field named the same. The SQL in the Record Source
is: SELECT [Payment Due to Doctor].[IS Last Name], [Payment Due to
Doctor].[IS First Name], [Payment Due to Doctor].[Insured SSN], [Payment Due
to Doctor].[Type of Coverage], [Payment Due to Doctor].[Chosen Dentist],
[Payment Due to Doctor].[Payment Due to Doctor], [Dependant List].[Dependant
Name], [Dependant List].[Date of Birth], [Dependant List].Relationship FROM
[Payment Due to Doctor] LEFT JOIN [Dependant List] ON [Payment Due to
Doctor].[Insured SSN]=[Dependant List].[Primary Insured Information];
 
I would go back into your [Payment Due to Doctor] query and LEFT JOIN
[Dependant List] there. Then test it.

Then use the query as report source.


--
Build a little, test a little.


Caryn said:
I created a query in the query tab to limit the clients to only those active
in December going forward. The Query Name is "Payment Due to Doctor."
However there is also a field named the same. The SQL in the Record Source
is: SELECT [Payment Due to Doctor].[IS Last Name], [Payment Due to
Doctor].[IS First Name], [Payment Due to Doctor].[Insured SSN], [Payment Due
to Doctor].[Type of Coverage], [Payment Due to Doctor].[Chosen Dentist],
[Payment Due to Doctor].[Payment Due to Doctor], [Dependant List].[Dependant
Name], [Dependant List].[Date of Birth], [Dependant List].Relationship FROM
[Payment Due to Doctor] LEFT JOIN [Dependant List] ON [Payment Due to
Doctor].[Insured SSN]=[Dependant List].[Primary Insured Information];

KARL DEWEY said:
of a certain date. I have created a report which lists both the clients and
the family members with a left join.

Your post mentions a query with date criteria and also a left join for
family members. Is this an external query and an internal SQL statement in
the report or all in one query?

Post the SQL of the query.
Click to expand...
Click to expand...
 
Back
Top