Access Query

[GPnew]

I have 2 table I wish to join. It's an account ledger
The tranaction table (GLTrans) had a field DrAccount and another CrAccount. The other table is the chart of accounts (Chartacc) which has a field Accountcode
DrAccount must always = a Chartacc.AccountCode ie
GLTrans.DrAccount = Chartacc.AccountCode AND
CrAccount must always = a Chartacc.AccountCode ie
GLTrans.CrAccount = Chartacc.AccountCode

However the problem I have is that there is a CrAccount and DrAccount in each record so GLTrans.CrAccount = Chartacc.AccountCode = CrAccount Code cannot work as Chartacc.AccountCode does not equal both DrAccount and the CRaccount) Is there a way I can join these 2 tables ? Or do i need to restructure the GLtrans?

Thanks

 

[r937]

you just need to join to the accounts table twice

SELECT GLTrans.columns
     , cr_acct.columns
     , dr_acct.columns
  FROM (
       GLTrans
INNER
  JOIN Chartacc AS cr_acct
    ON cr_acct.AccountCode = GLTrans.CrAccount
       )
INNER
  JOIN Chartacc AS dr_acct
    ON dr_acct.AccountCode = GLTrans.DrAccount

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon