Re: How to remove duplicates from linked table query?

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: 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)
  • Re: How to remove duplicates from linked table query?
    ... you can use a query ... I guess I need to enter the Industry and/or ... > against their ID in tblCandidateIndustries and Receptionist against their ... please describe your criteria to select the above. ...
    (microsoft.public.access.queries)