Get Unique records from Linked Table query

[timmahan]

Using Access 2003, we have a main table of peopledump, which has unique records, each with an idn in field idn.

However each idn may be in several Lists, such as sales, service, other, etc. numbered 1 2 3 4 5 etc

In order to see which lists each person idn is in I have created a query linking [peopledump].idn to datasources_people.person, and then datasources_people.datasource to datasource.idn

This works however where the people idn is in several list datasource I get duplicate people idn's

So what I want to do is to say something like If there is a duplicate or several duplicates of people.idn, then pick The one with the Lowest number in datasource. Thus if people idn has 4 records (queries,not data) with a datasources of 1,2,3, and 4 give me ONLY the query record with datasource 1

If in datasource 4, and 5 then give me only the record that matches 4 etc

How can I do this, or can it be done ?

 

[Remou]

Perhaps something like:

SELECT idn, Min(datasource) FROM tblT GROUP BY idn

 

[timmahan]

RE: Suggestion from Remou
SELECT idn, Min(datasource) FROM tblT GROUP BY idn

Not applicable or formatted in correct SQL for Access
Can anyone help ?

 

[Remou]

Please explain "Not applicable or formatted in correct SQL for Access". To use it, you would, of course, have to substitute your field and table names, but it is a fairly standard Access query.

 

[timmahan]

We solved the problem of duplicates by having our MySQL programmer add a field of [datasorces] to the peopledump table. So we no longer have the duplicate records problem.

The field contains records such as
datasources
,1,3,
,1,3,4,
,1,3,4,5,
,4,
,5,

We can now use this field for certain criteria such as
Like ",1,*" Or Like ",2,*" Or Like ",3,*" And Not Like ",4," And Not Like ",5," OR Not ",5,"and so on

 

[PHV]

I strongly suggest you to use parentheses when mixing OR & AND operators ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886