Re: SQL Syntax Help Please

From: Craig Hornish (temp_at_cap-associates.com)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 12:39:23 -0400

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
temp@cap-associates.com - 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"

"Mark" <Mark@discussions.microsoft.com> wrote in message
news:2AA01A56-20EB-4A72-B554-3E49FCA28C78@microsoft.com...
> 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
>
>

Relevant Pages

  • Re: [PHP] OOP slow -- am I an idiot?
    ... OOP has overhead. ... That saves you putting wasteful SQL queries in your ... If you need to do a complex query with a couple of joins and such, ... I want to create a "customer" class which fetches its attributes from a ...
    (php.general)
  • Sub Query Consolidation, Syntax Question
    ... I am new to writing free hand SQL, and have a two part question. ... #1 I'm trying to consolidate a number of queries into one query. ... to me that there should be a way to consolidate this into one place, ... Information] RIGHT JOIN NetShipPlusOpen ON [Customer Information].[Sold To ...
    (microsoft.public.access.queries)
  • Re: Add worksheets with Web Query
    ... Yes one query would suffice, I assume this would be a module? ... What I need to happen then is when the workbook is open is (assuming I ... Basically the pages I'm scraping are payments received by my company. ... but the customer name does not match the payee name on the statement. ...
    (microsoft.public.excel.misc)
  • Efficient coordinated queries??
    ... A large chunk of it can be brought in with a single query and each row represents an item to process. ... For a hypothetical example consider a customer order database where we need to process through all customers and can bring in a row of customer data as our main query but there are 0..n1 rows of customer order data, 0..n2 rows of customer feedback information, 0..n3 rows of customer discounts available, etc. ... Instead I want to bring in the data as individual DataSets representing an item to process. ... Fully denormalize in a gi-normous SQL query and go back to just a single row of data per item. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: comboBox & northwinds sample orders form
    ... True, but OFTEN to display a customer name when you have a customer id, we ... dlookup) are suggest. ... the problem is that you don't need dlookup in a query. ... You can always just shove in the sql in place of the ...
    (microsoft.public.access.formscoding)