Re: How to remove duplicates from linked table query?

Tech-Archive recommends: Speed Up your PC by fixing your registry

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) )


--
HTH
Van T. Dinh
MVP (Access)



"Penny" <penny@xxxxxxxxxxxxxx> wrote in message
news:newscache$aps8ti$zu5$1@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
> "Van T. Dinh" <VanThien.Dinh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:unNRmm1GGHA.3320@xxxxxxxxxxxxxxxxxxxxxxx
>> 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.
>>
>> --
>> HTH
>> Van T. Dinh
>> MVP (Access)
>>
>>
>>
>> "Penny" <penny@xxxxxxxxxxxxxx> wrote in message
>> news:newscache$q3e8ti$be2$1@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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.
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Count Problem
    ... Try saving this query as, say, qryIntermediary: ... SELECT Industry, Sum(SumOfCosts) As Costs, CountAs NoOfCustomers ... GROUP BY Industry, Customer) AS Subquery ...
    (microsoft.public.access.queries)
  • RE: Query returning many duplicate records
    ... If you wanted to calculate the number of establishments in a particular ... Then, in the query you would add the Industry column, and the ... "Roberto" wrote: ...
    (microsoft.public.access.queries)
  • Re: How to remove duplicates from linked table query?
    ... please advise which Industry and which Position ... would you like the Query to return for Candidate 284? ... please describe your criteria to select the above. ... > I run a query on a 'tblCandidates' table linked to two other tables ...
    (microsoft.public.access.queries)
  • Re: Query Improvement
    ... INDUSTRY: Index on IND_ID column. ... query you do not use a NVL function for the join. ... Even in older versions it should help to write it as an equijoin without IN or EXISTS and let the CBO decide about the driving table. ...
    (comp.databases.oracle.server)
  • Re: How not to display similar records in a subform?
    ... of Position Types or any combination of either, so are a necessary criteria. ... > If Industry and Position Type are not germane, ... > include them in the query? ...
    (microsoft.public.access.forms)