Simple subquery in Access

Question

I have got a Subquery issue that I am sure has a really really simple solution but I can't figure out what it is!

Here's what I'm trying to do, I have two tables, let's say, customer and orders. The customer table obviously stores a list of individual customers and the orders table stores a list of orders placed by clients. I am trying to create a query that will return each customer's details as well as the total order amount placed by that customer. Try as I might I can't seem to be able to get this query to work as it says:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's from clause."

I am trying to go with soemthing like this, please could anyone advise on what is wrong?

select
  customer.name,
  customer.address,
  (select sum(order.orderamount) from order, customer where order.customerid = customer.id)
from
  customer

THanks!

Solution

select 
  customer.name, 
  customer.address, 
  (select sum(order.orderamount) from order where order.customerid = customer.id) as amount
from customer 

but you can do it wihout subquery:

select 
  customer.name, 
  customer.address, 
  sum(order.orderamount) 
from order 
   join customer on order.customerid = customer.id
group by   customer.name,   customer.address

Comments

[Fionnuala]

sql is a generic tag. In ms-access (originally tagged access) join is not supported, you must specify a join type.