Need help with Access Query syntax

[chelvan]

hi all!
i have the following query to run on ms access2000. once i run this query the data will be doubled. but i need it once. my problem is on where condition. there i have to check two different fields on table arjou (dtoacc, ctoacc) with glcode of the table glcode. any other ways?

SELECT   [DATE]
       , REFNO AS [REF NO]
       , orderno AS [ORDER NO]
       , fileno AS [FILE NO]
       , AMOUNT
       , glcode.description AS [NAME]
FROM     glcode
       , arjou
WHERE    glcode.glcode=arjou.ctoacc
   OR    glcode.glcode=arjou.dtoacc
ORDER BY date desc

thanks
chelvan

[Rabbit]

Your where clause is not causing the duplication. There's duplication because your join has a many on one or both of the tables. You have to decide on an algorithm to keep just one record.

[Mihail]

Try, in Query Properties Sheet, to set Unique Values to YES.

[NeoPa]

It's not clear exactly what you mean by 'once i run this query the data will be doubled'. However, I would start by changing your lines #7 through #11 to link the tables using an INNER JOIN :

FROM    [GLCode]
        INNER JOIN
        [Arjou]
  ON    (GLCode.GLCode IN(Arjou.Ctoacc, Arjou.Dtoacc))