CODEWITHSUNDEEP
sqlms-access
Kirs Kringle
Kirs Kringle

Reputation: 929

Subquery in MS-Access SQL Syntax error

Good Evening.

I'm trying to create a query that averages each product purchase Vs. the number of purchases to get the average amount purchased.

This includes three tables:

  1. CUSTOMER
  2. INVOICE
  3. LINE

I wrote this:

SELECT CUSTOMER.CUS_CODE, CUS_FNAME+" "+CUS_LNAME AS Name, CUSTOMER.CUS_BALANCE,    
Sum([LINE]![LINE_NUMBER]*[LINE]![LINE_PRICE]) AS Total_purchases,  
Count(INVOICE.INV_NUMBER) AS Number_of_purchases, 

(
SELECT [Sum([LINE]![LINE_NUMBER]*[LINE]![LINE_PRICE]) /   
[Count(INVOICE.INV_NUMBER)]
FROM INVOICE, LINE
WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
) AS Average_purchase_amount

FROM CUSTOMER, INVOICE, LINE
WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE AND INVOICE.INV_NUMBER = LINE.INV_NUMBER 
GROUP BY CUSTOMER.CUS_CODE, CUS_FNAME+" "+CUS_LNAME, CUSTOMER.CUS_BALANCE;

I get an error at the second SELECT. I'm not sure why. I feel like I'm missing something very simple.

The actual error reads:

From the error I assume that somewhere in my expression things are not lining up, but I guess I don't understand enough about what is going on to know what exactly it is.

Upvotes: 1

Views: 855

Answers (1)

Vincent James
Vincent James

Reputation: 1148

There are unbalanced brackets. Either each one was meant to be in brackets or whole equation:

SELECT [Sum([LINE]![LINE_NUMBER]*[LINE]![LINE_PRICE])] / [Count(INVOICE.INV_NUMBER)]

-- OR --

SELECT [Sum([LINE]![LINE_NUMBER]*[LINE]![LINE_PRICE]) /  Count(INVOICE.INV_NUMBER)]

Try JOINing your tables. Something like this:

FROM INVOICE
    INNER JOIN CUSTOMER ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
    INNER JOIN LINE ON LINE.INV_NUMBER =INVOICE.INV_NUMBER

Upvotes: 1

Related Questions