CODEWITHSUNDEEP
sqlvb.netms-access
Khattab Aljaily
Khattab Aljaily

Reputation: 3

Select from multi access tables

Try to get result of this query:

SELECT DISTINCT
       invoices.invoice_id,
       invoices.customer,
       invoices.total,
       SUM(pay_amount) AS paid
FROM invoices,
     invoice_payment
WHERE invoices.invoice_id = invoice_payment.invoice_id
      AND invoices.type = 'client'
      AND invoice_payment.pay_type <> 'postpaid'
      AND format(invoices.create_date, "dd/mm/yyyy") BETWEEN '01/11/2017' AND '14/11/2017';

I got

"Your query does not include the specific expression 'invoice_id' as part of an aggregate function"

Upvotes: 0

Views: 24

Answers (1)

dbajtr
dbajtr

Reputation: 2044

Add a GROUP BY when you are aggregating data, e.g. count, sum etc

Like this:

SELECT DISTINCT
       invoices.invoice_id,
       invoices.customer,
       invoices.total,
       SUM(pay_amount) AS paid
FROM invoices,
     invoice_payment
WHERE invoices.invoice_id = invoice_payment.invoice_id
      AND invoices.type = 'client'
      AND invoice_payment.pay_type <> 'postpaid'
      AND format(invoices.create_date, "dd/mm/yyyy") BETWEEN '01/11/2017' AND '14/11/2017'
GROUP BY invoices.invoice_id,
         invoices.customer,
         invoices.total;

The Access docs for GROUP BY are here

Also try to avoid the old style joins, they are nasty and outdated. This would be much better:

FROM invoices
INNER JOIN invoice_payment ON invoices.invoice_id = invoice_payment.invoice_id

Upvotes: 1

Related Questions