SQL Syntax Help Please

[Guest]

Hello!

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)
OrderDetails(Orderid,UnitPrice,productid,quantity)
Customer(Customerid, customername, address)
CustomerPayments(orderid,PaymentReceived)

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
success.
2.) Can someone please suggest a good book on SQL syntax that will clear
thing up for me.


Many Thanks in Advance,

Mark

 

[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 Alexander Morrison]

Do the same with the Payments table too.

 

[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 =
Customer.CustomerID
GROUP BY Customer.CustomerName, Orders.OrderID, Orders.OrderDate;
This has been tested with 2 customers with 2 products each and 2 payments
each.

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
issue"