How to remove duplicates from linked table query?

  • Thread starter Thread starter Penny
  • Start date Start date
P

Penny

Hi All,

I run a query on a 'tblCandidates' table linked to two other tables
'tblCandidateIndustries' and 'tblCandidatePositions' each of which may have
many records pertaining to the Candidate in the parent table.

The query returns every permutation of Candidate, Industry and Position in a
seperate line, e.g.

CandidateID Industry Position

543 Health Secretary
284 Aged Care Admin Assistant
284 Aged Care Receptionist
284 Construction Book Keeper
284 Construction Payroll
889 Banking Finance Broker

But I only need to show each Candidate only once to display to the user. I
haven't been able to get DISTINCT or FIRST to remove the duplicates. Any
tips?

Regards,

Penny.
 
Using your sample data, please advise which Industry and which Position
would you like the Query to return for Candidate 284?

Also, please describe your criteria to select the above.
 
Van,

This query returns all the Candidates at least once and any Candidate who
has Industries and/or Positions in the related tables is returned once for
each combination of these. I guess I need to enter the Industry and/or
Position as parameters into the query and return all Candidates who have at
least one value for these two in at least one of the two linked tables.

A Candidate search will be based on this query. For an example search the
user may choose an Industry(say Banking) from a dropdown list on the search
window, I would need to enter the industry as a parameter into the query.
The user may also choose a Position(say Receptionist) in a similar fashion.
The query should then return only the Candidates who have Banking against
their ID in tblCandidateIndustries and Receptionist against their ID in
tblPositions.

I know my query is now different to what I first described.

Penny.
 
In that case, you should use a Parameter Query to return the CandidateID of
the Candidate that match you criteria. For example, you can use a query
with SQL String like:

SELECT DISTINCT CandidateID
FROM [YourTable]
WHERE
( (Indutry = [Enter Required Industry]) OR
([Enter Required Industry] Is Null) )
AND
( (Position = [Enter Required Position]) OR
([Enter Required Position] Is Null) )
 
Hi Van,
In that case, you should use a Parameter Query to return the CandidateID
of the Candidate that match you criteria. For example, you can use a
query with SQL String like:

SELECT DISTINCT CandidateID
FROM [YourTable]
WHERE
( (Indutry = [Enter Required Industry]) OR
([Enter Required Industry] Is Null) )
AND
( (Position = [Enter Required Position]) OR
([Enter Required Position] Is Null) )
Click to expand...

Seeing as I want the query to return CandidateID's if that candidate has
industries and/or positions(even just one industry and no positions), should
it be WHERE .......OR......... rather than using the AND?

Regards,

Penny.
 
I already covered that in the nested OR in each operand of the outer AND
expression.

Basically, with the WHERE expression I posted, the query will ask the use
for 2 parameter values, one for Indutry and one for Position. The user can
do one other following:

* Leave both Parameters blank. This will return all CandidateID

* Enter value for the required Industry and leave Position blank. This will
return CandidateIDs for candidates belonging to the required Industry
*without* regards to positions.

* Leave Poisition blank & enter value for the required Position. This will
return CandidateIDs for candidates classified for the required Position
*without* regards to the industry clasifications.

* Enter values for both Industry & Position. This will only select
CandidateIDs for candidate that is classified in the exact required
combination of industry and position.

--
HTH
Van T. Dinh
MVP (Access)



Penny said:
Hi Van,
In that case, you should use a Parameter Query to return the CandidateID
of the Candidate that match you criteria. For example, you can use a
query with SQL String like:

SELECT DISTINCT CandidateID
FROM [YourTable]
WHERE
( (Indutry = [Enter Required Industry]) OR
([Enter Required Industry] Is Null) )
AND
( (Position = [Enter Required Position]) OR
([Enter Required Position] Is Null) )
Click to expand...

Seeing as I want the query to return CandidateID's if that candidate has
industries and/or positions(even just one industry and no positions),
should it be WHERE .......OR......... rather than using the AND?

Regards,

Penny.
Click to expand...
 
Thanks Van,

I get what your saying. One more question. The user may or may not choose an
Industry and/or Postion title from pre populated combo boxes. So how in code
do you use the query, passing in the values(if entered) from the combo
boxes?

Regards,

Penny

Van T. Dinh said:
I already covered that in the nested OR in each operand of the outer AND
expression.

Basically, with the WHERE expression I posted, the query will ask the use
for 2 parameter values, one for Indutry and one for Position. The user
can do one other following:

* Leave both Parameters blank. This will return all CandidateID

* Enter value for the required Industry and leave Position blank. This
will return CandidateIDs for candidates belonging to the required Industry
*without* regards to positions.

* Leave Poisition blank & enter value for the required Position. This
will return CandidateIDs for candidates classified for the required
Position *without* regards to the industry clasifications.

* Enter values for both Industry & Position. This will only select
CandidateIDs for candidate that is classified in the exact required
combination of industry and position.

--
HTH
Van T. Dinh
MVP (Access)



Penny said:
Hi Van,
In that case, you should use a Parameter Query to return the CandidateID
of the Candidate that match you criteria. For example, you can use a
query with SQL String like:

SELECT DISTINCT CandidateID
FROM [YourTable]
WHERE
( (Indutry = [Enter Required Industry]) OR
([Enter Required Industry] Is Null) )
AND
( (Position = [Enter Required Position]) OR
([Enter Required Position] Is Null) )
Click to expand...

Seeing as I want the query to return CandidateID's if that candidate has
industries and/or positions(even just one industry and no positions),
should it be WHERE .......OR......... rather than using the AND?

Regards,

Penny.
Click to expand...
Click to expand...
 
You can simply use the full references to the ComboBoxes as the Parameters
in the Query, i.e. use something like:

[Forms]![YourForm]![YourIndustryComboBox]

instead of:

[Enter Required Industry]

and provided that the Query is handled by Access (e.g. DoCmd methods), the
Expression Service in Access will resolve the references to actual values
for you.
 
Thanks Van, you've been a great help.

Regards,

Penny

Van T. Dinh said:
You can simply use the full references to the ComboBoxes as the Parameters
in the Query, i.e. use something like:

[Forms]![YourForm]![YourIndustryComboBox]

instead of:

[Enter Required Industry]

and provided that the Query is handled by Access (e.g. DoCmd methods), the
Expression Service in Access will resolve the references to actual values
for you.

--
HTH
Van T. Dinh
MVP (Access)



Penny said:
Thanks Van,

I get what your saying. One more question. The user may or may not choose
an Industry and/or Postion title from pre populated combo boxes. So how
in code do you use the query, passing in the values(if entered) from the
combo boxes?

Regards,

Penny
Click to expand...
Click to expand...
 
Back
Top