How to add where clause in access join query

Question

I have query like this.

SELECT account.AccountNumber, account.Name, Sum(agro.price*agro.qty) AS Expr1
FROM account,data  INNER JOIN (agro INNER JOIN data ON agro.BillNo = data.BillNo) ON    
account.AccountNumber = data.acno
GROUP BY account.AccountNumber, account.Name;

I want to add where db='true' this columns is of 'data' table then how can i do pls help me?

You should put only the flavour of sql that you are using in the tags for your question. Adding extra tags just adds confusion. — Filipe Silva, Dec 02 '13 at 14:59

Filipe Silva · Accepted Answer · 2013-12-02T15:18:59.930

2

Try this:

SELECT account.AccountNumber, account.NAME, Sum(agro.price * agro.qty) AS Expr1
FROM ((account
INNER JOIN data ON account.AccountNumber = data.acno)
INNER JOIN agro ON agro.BillNo = data.BillNo)
WHERE data.db='true'
GROUP BY account.AccountNumber, account.NAME;

You had some confusion in your JOINs, but i think this is what you were aiming for

edited Dec 02 '13 at 15:18

answered Dec 02 '13 at 14:55

Filipe Silva

21,189
5
53
68

1

Its raised an error "Syntax error (missing operator) in query exp. 'account.AccountNumber = data.acno INNER JOIN agro ON agro.BillNo = data.BillN'" Please Check.. – Hiren Raiyani Dec 02 '13 at 15:11
Are you sure you copied this exactly like it is? i created a [sample fiddle](http://sqlfiddle.com/#!3/d22b5/1) in sql server and it worked right away. I'm not seeing what would need to be changed for access. – Filipe Silva Dec 02 '13 at 15:16
Try the query now with the parenthesis (from [this answer](http://stackoverflow.com/a/15389708/1385896), it seems that access likes parenthesis around the joins. i did not know this :p). See if it works. – Filipe Silva Dec 02 '13 at 15:20

How to add where clause in access join query

1 Answers1