Nested Queries Help!

[Guest]

Consider a simple Structure:

SalesTable: OrderID, DateOrdered, Etc.

InvoicesTable: SalesTable.OrderID, InvoicedAmount, DateCollected

PayablesTable: SalesTable.OrderID, PayedOutAmount, DatePaid

Relationships are simple one to many where OrderID from the SalesTable is
PrimaryTable, having Many relation in the Invoices and Payables table.

Question: Write a query that Sums InvoicedAmount, Sums PayedOutAmount Such
that a "profit" can be calculated. My efforts have generated "too complex"
errors or worse

Any guidance is appreciated!

 

[John Spencer]

SELECT I.OrderID
, Sum(InvoicedAmount) as Invoiced
, Sum(PayedOutAmounts) As Paid
, Sum(InvoicedAmount) - Sum(PayedOutAmounts) as TheDifference
FROM InvoicesTable as I LEFT JOIN PayablesTable as P
ON I.OrderID = P.OrderID
GROUP BY I.OrderID

If you don't want to do this by OrderID then drop it from the SELECT clause
and the GROUP BY clause.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Allen Browne]

You will have to be careful here not to get items duplicated if there is any
possibility a sales item could have multiple invoices or multiple payments.

1. Create a query with the SalesTable and the InvoicesTable.
Double-click the line joining them in the upper pane of table design.
Access pops up a dialog with 3 options. Choose:
All records from SalesTable, and any matches from InvoicesTable.

2. Use a subquery to get the value from the 3rd table.
Type an expression like this into the Field row.
(It's all one line.)
AmtPaid: (SELECT Sum(PayedOutAmount AS AmtPaid)
FROM PayablesTable
WHERE PayablesTable.OrderID = SalesTable.OrderID)

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Guest]

You might want to ask your Account recievable folks if they ever get single
payments from customers which are meant to cover multiple invoices before you
adopt that data structure and put it into stone...