By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Need help with Access Query syntax

P: 90
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT   [DATE]
  2.        , REFNO AS [REF NO]
  3.        , orderno AS [ORDER NO]
  4.        , fileno AS [FILE NO]
  5.        , AMOUNT
  6.        , glcode.description AS [NAME]
  7. FROM     glcode
  8.        , arjou
  9. WHERE    glcode.glcode=arjou.ctoacc
  10.    OR    glcode.glcode=arjou.dtoacc
  11. ORDER BY date desc
Dec 13 '11 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 12,421
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.
Dec 13 '11 #2

P: 759
Try, in Query Properties Sheet, to set Unique Values to YES.
Dec 14 '11 #3

Expert Mod 15k+
P: 31,707
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 :

Expand|Select|Wrap|Line Numbers
  1. FROM    [GLCode]
  2.         INNER JOIN
  3.         [Arjou]
  4.   ON    (GLCode.GLCode IN(Arjou.Ctoacc, Arjou.Dtoacc))
Dec 14 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.