How not to display similar records in a subform?

[Penny]

Hi All,

I run a query based on a dynamically generated SQL select statement that
draws from a parent table and two linked tables. I then make the subforms
recordsource the query string. It works nicely to return and display the
Candidates who match the desired criteria and all the permutations of the
criteria combinations they've been matched against(example shown below).

CandidateID Name Industry PositionType

543 Mary Health Secretary
284 Sarah Aged Care Receptionist
284 Sarah Aged Care Payroll
284 Sarah Construction Receptionist
284 Sarah Construction Administration
889 Bob Banking Payroll

But I only need to show each Candidate only once to display to the user, the
Industry and PositionType combinations are not needed for display purposes.
Is there any way to get the form to show each candidate id only once? Any
tips?

Regards,

Penny.

 

[Jeff Boyce]

Penny

If Industry and Position Type are not germane, why show them? In fact, why
include them in the query? If you want to see unique values of CandidateID,
change that property in your query to show Unique Values.

 

[Penny]

Hi Jeff,

The point of the query is to return candidates who posses the Industry and
of Position Types or any combination of either, so are a necessary criteria.
The user only needs to see that candidate listed once however.

I've tried various combinations of the 'Unique Values' property, DISTINCT
and FIRST and none of them work. Any other ideas?

Regards,

Penny.

 

[Jeff Boyce]

Penny

I understand that they are germane SELECTION criteria, but you said you
didn't want/need to show them. While I do not have a clear picture of how
your data is structured (and hence, how it needs to be queried), I suspect
using those two as selection criteria (but unchecking the display-these
checkboxes) would leave you with CandidateID and name, for which Unique
Values should apply.

 

[Penny]

Hi Jeff,

Sorry, you're correct that the two criteria fields need not be shown. I only
showed them in the example to illustrate why the same candidate was
appearing more than once.

I'm playing with versions of the query using your Unique Values idea and
some other ones. I'm swaying between being very confused and then not so
it'll take me a while.

In the query design grid criteria for the joined positions table I've got
[Forms]![frmMAINMENU]![Child0]![PositionTitle] to get the Position Title
from the form and [Forms]![frmMAINMENU]![Child0]![Industry] from the form.
If either one has no criteria selected no results at all are returned. How
do I allow one of them to receive a null value but still allow the query to
return candidates who the other criteria?

Regards,

Penny

 

[Jeff Boyce]

Penny

You could try selecting on "Is Null Or ....(your forms reference)"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff,

Sorry, you're correct that the two criteria fields need not be shown. I only
showed them in the example to illustrate why the same candidate was
appearing more than once.

I'm playing with versions of the query using your Unique Values idea and
some other ones. I'm swaying between being very confused and then not so
it'll take me a while.

In the query design grid criteria for the joined positions table I've got
[Forms]![frmMAINMENU]![Child0]![PositionTitle] to get the Position Title
from the form and [Forms]![frmMAINMENU]![Child0]![Industry] from the form.
If either one has no criteria selected no results at all are returned. How
do I allow one of them to receive a null value but still allow the query to
return candidates who the other criteria?

Regards,

Penny

Penny

I understand that they are germane SELECTION criteria, but you said you
didn't want/need to show them. While I do not have a clear picture of how
your data is structured (and hence, how it needs to be queried), I suspect
using those two as selection criteria (but unchecking the display-these
checkboxes) would leave you with CandidateID and name, for which Unique
Values should apply.

--
Regards

Jeff Boyce
<Office/Access MVP>

of
the once?
Any

 

[Penny]

Thanks Jeff,

Your suggestion to use 'Unique Values' with the pertinent fields not
displayed and also using "Is Null Or ....(your forms reference)" where the
tools I needed.

Regards,

Penny

Penny

You could try selecting on "Is Null Or ....(your forms reference)"

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff,

Sorry, you're correct that the two criteria fields need not be shown. I only
showed them in the example to illustrate why the same candidate was
appearing more than once.

I'm playing with versions of the query using your Unique Values idea and
some other ones. I'm swaying between being very confused and then not so
it'll take me a while.

In the query design grid criteria for the joined positions table I've got
[Forms]![frmMAINMENU]![Child0]![PositionTitle] to get the Position Title
from the form and [Forms]![frmMAINMENU]![Child0]![Industry] from the
form.
If either one has no criteria selected no results at all are returned.
How
do I allow one of them to receive a null value but still allow the query to
return candidates who the other criteria?

Regards,

Penny

message

Penny

I understand that they are germane SELECTION criteria, but you said you
didn't want/need to show them. While I do not have a clear picture of how
your data is structured (and hence, how it needs to be queried), I suspect
using those two as selection criteria (but unchecking the display-these
checkboxes) would leave you with CandidateID and name, for which Unique
Values should apply.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi Jeff,

The point of the query is to return candidates who posses the Industry
and
of Position Types or any combination of either, so are a necessary
criteria.
The user only needs to see that candidate listed once however.

I've tried various combinations of the 'Unique Values' property, DISTINCT
and FIRST and none of them work. Any other ideas?

Regards,

Penny.

message
Penny

If Industry and Position Type are not germane, why show them? In
fact,
why
include them in the query? If you want to see unique values of
CandidateID,
change that property in your query to show Unique Values.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi All,

I run a query based on a dynamically generated SQL select statement
that
draws from a parent table and two linked tables. I then make the
subforms
recordsource the query string. It works nicely to return and
display
the
Candidates who match the desired criteria and all the permutations of
the
criteria combinations they've been matched against(example shown
below).

CandidateID Name Industry
PositionType

543 Mary Health
Secretary
284 Sarah Aged Care
Receptionist
284 Sarah Aged Care Payroll
284 Sarah Construction
Receptionist
284 Sarah Construction
Administration
889 Bob Banking
Payroll

But I only need to show each Candidate only once to display to the
user,
the
Industry and PositionType combinations are not needed for display
purposes.
Is there any way to get the form to show each candidate id only once?
Any
tips?

Regards,

Penny.