Records with the same last name disappear...

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.

"Caryn" wrote in message
...
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?

The properties for the text box "Last name" has "No" selected in Hide
Duplicates.

"Jeff Boyce" wrote:

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.

"Caryn" wrote in message
...
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?



.

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.

----
HTH
Dale



"Caryn" wrote:

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?

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.

"Caryn" wrote in message
...
The properties for the text box "Last name" has "No" selected in Hide
Duplicates.

"Jeff Boyce" wrote:

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.

"Caryn" wrote in message
...
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?



.

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.

--
Build a little, test a little.


"Caryn" wrote:

The properties for the text box "Last name" has "No" selected in Hide
Duplicates.

"Jeff Boyce" wrote:

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.

"Caryn" wrote in message
...
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?



.

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" wrote:

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.

--
Build a little, test a little.


"Caryn" wrote:

The properties for the text box "Last name" has "No" selected in Hide
Duplicates.

"Jeff Boyce" wrote:

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.

"Caryn" wrote in message
...
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?



.

Used the wizard, but I didn't use "a GroupBy function" that I'm aware of.

"Jeff Boyce" wrote:

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.

"Caryn" wrote in message
...
The properties for the text box "Last name" has "No" selected in Hide
Duplicates.

"Jeff Boyce" wrote:

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.

"Caryn" wrote in message
...
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?



.



.

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" wrote:

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" wrote:

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.

--
Build a little, test a little.


"Caryn" wrote:

The properties for the text box "Last name" has "No" selected in Hide
Duplicates.

"Jeff Boyce" wrote:

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.

"Caryn" wrote in message
...
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?



.