Subquery in MS-Access SQL Syntax error

1
votes

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:

  • "Syntax error in query expression '( SELECT [Sum([LINE]![LINE_NUMBER]*[LINE]![LINE_PRICE]) / [Count(INVOICE.INV_NUMBER)].'"

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.

1
sqlms-access
Please do not change the subject by editing your question like that. If your original question was about a syntax error, you can answer it yourself or accept the answer given by Vincent. If you have a different problem, ask a new question. Remember, this is a "question and answer" site, not a discussion board. - BellevueBob
@BellevueBob Sorry I'll delete things. - Kirs Kringle
Better, thanks! This keeps your original question relevant, in case someone visits it again in the future. - BellevueBob

1 Answers

1
votes

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