Writing a Query for a report in Access 03

[lahmayes]

I am using MS Access 2003. I'd like to write a report to see the status of my wholesale customers:

Report: Wholesale customers

Select:
All customers with defined discount greater than zero that have placed an order since user entered date

Group & Sort:
Sub group by discount amount then by tax id

Calculations:
Count # of times ordered
Average order amount

Output:
ID, contact name, company name, email, date first purchase, date last purchased, # of orders, avg order

So, I figured I need to start by writing a query for the report to use. I've got a start. I can easily join my orders table with my customers table, and output only wholesale orders. What I don't know how to do is count the number of orders by each customer and then average them. It seems like I need to be able to loop through each customer somehow. I am very new to SQL, but I haven't encountered any method to loop through data yet. Any help would be much appreciated. This is what I have so far:

SELECT c.customerid, c.fullname, c.company, c.email, c.phone, c.discountpercent, c.taxid, o.orderdate, o.orernumber
FROM customers AS c, orders AS o
WHERE c.discountpercent > 0 AND c.customerid = o.customerid

[dohamsg]

Hi, try this:

SELECT c.customerid, c.fullname, o.aOrderAvrg
FROM customer
JOIN
(
SELECT customerid, AVG(order_amount) as aOrderAvrg
FROM orders
GROUP BY customerid
) o
ON c.sutomerid = o.customerid