SQL Syntax Help Please




I am trying to create a join of four tables to get data from a db in the
format I want.

The Database tables and fields are:
Orders (orderid,customerid,Orderdate)
Customer(Customerid, customername, address)

My purpose is to track the account status of the customer for each order
they have placed.

The desired format of result obtained from the query is like this:

Customername, Orderid, Orderdate,AmountReceived,AmountDue(this is a
calculated column).

I am using Access 2000.

1.)Can someone help me with the SQL syntax please. I have tried several
permutations and combinations of sql (to the best of my knowledge) but without
2.) Can someone please suggest a good book on SQL syntax that will clear
thing up for me.

Many Thanks in Advance,


Duane Hookom

You can't easily create a query with the details from all these tables since
the CustomerPayments are related to Orders rather than OrderDetails. If the
query is for a report, you could create a subreport for either the
ORderDetails or the CustomerPayments. Place the subreport in the Order group
header or footer.

Craig Alexander Morrison

Create a query on the order details table to get the total of the order
lines and then use that query in place of the order details table in your
main query.

Craig Hornish

Hi Mark,
Here is what you "asked" for :) in Question 1. Please forgive the way I
describe this, it is the first time that I tried this with pure SQL, usually
I would just have another Query that did the sums for the amount recieved
linked to the other query.
The outer "layer" is your basic sql statement to get the total for the Order
details. What makes this work for this grouping, so you have the Payments
received, is the inner part that sums the customer payments based on the
Order ID. Now you have the payment recieved and can subtract it from the
DetailSum to get the AmountDue.

SELECT DISTINCT Customer.CustomerName, Orders.OrderID, Orders.OrderDate,
Sum([Quantity]*[UnitPrice]) AS DetailSum,

(SELECT Sum(CustomerPayments.PaymentReceived)
FROM CustomerPayments
Where CustomerPayments.OrderID= Orders.orderID) AS AmountReceived,

[DetailSum]-[AmountReceived] AS AmountDue
FROM (Orders INNER JOIN OrderDetails ON Orders.OrderID =
OrderDetails.OrderID) INNER JOIN Customer ON Orders.CustomerID =
GROUP BY Customer.CustomerName, Orders.OrderID, Orders.OrderDate;
This has been tested with 2 customers with 2 products each and 2 payments

Craig Hornish
(e-mail address removed) - so I can delete it when it becomes a spam magnet

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads