Hi Neil
>The reason why there are 2 records the same is because that number appears twice under that design number. How can I change my code below to show only occurence of each record:
>SELECT article.articleno, specific.articleno, specific.mainkey ;
>FROM ("U:\Data\article"), ("U:\Data\specific") ;
>WHERE thisform.text1.Value=article.articleno AND article.articleno=specific.articleno ;
>INTO CURSOR temp
You need to use the 'DISTINCT' clause your query to ensure that only unique combinations of the three fields selected are returned. Amend your query to be:
SELECT DISTINCT article.articleno, specific.articleno, specific.mainkey ;
FROM ("U:\Data\article"), ("U:\Data\specific") ;
WHERE specific.articleno = article.articleno ;
AND article.articleno = thisform.text1.Value
INTO CURSOR temp
However, this will still return the value more than once if the same "mainkey" occurs in conjunction with more than one "articleno".
If that can happen, then you should exclude the articleno columns from the result set, (you really don't need them since you are specifying them in the join and supplying a single value for the query anyway) and just use the following:
SELECT DISTINCT specific.mainkey ;
FROM ("U:\Data\article"), ("U:\Data\specific") ;
WHERE specific.articleno = article.articleno ;
AND article.articleno = thisform.text1.Value
INTO CURSOR temp
----
Regards
Andy Kramek