Currently I do NOT need the FROM clause, However I probably will move
the
Warehouse costs into the payables table. The surcharge I have no idea
where
to put that since I haven't discussed that yet. Regardless, where do I
send
the check for all this help?
Oddly enough I got a syntax error on your last post as well as this
one. I
copied the code into the SQL window. It saves it, but then generates a
Syntax error when I try to "run" it.
The syntax error is in the TotalPaid Column (so it claims). I am
running
Access 2003, if that makes a difference.
:
First of all, it doesn't look like you need the Payables table in the
FROM
clause unless WareAndSurcharge fields come from that table. IF you
can
eliminate that from the FROM clause you might be able to use an
aggregate
query to simplify your query somewhat.
SELECT SalesOrders.IsComplete
, SalesOrders.SFSOID
, Receivables.DatePaid
, Customers.CompanyName
, Employees.LastName
, Employees.CommissionRate
, Format((Nz([CCCharge],0)+nZ([Adjustments],0)),"Currency") AS CCA
, (SELECT Sum(OriginalAmount) AS TotalPaid
FROM Payables
WHERE Payables.SFSOID = SalesOrders.SFSOID) AS TotalPaid
, Nz((SELECT Sum(FreightCoInvoiceAmt) AS TotalPaidFreight
FROM Payables
WHERE Payables.SFSOID = SalesOrders.SFSOID),0) AS TotalPaidFreight,
Format(nZ([BestWayNJ],0)+nZ([WestCoastCA],0)+nZ([BarFreightIL],0)+nZ([ConsolidatedTX],0)+nZ([EvansMI],0)+nZ([Surcharge],0),"Currency")
AS WareAndSurcharge
, Sum(AmountPaid) as TotalReceivables
FROM (Employees INNER JOIN
(Customers INNER JOIN SalesOrders ON Customers.CustomerID =
SalesOrders.CustomerID)
ON Employees.EmployeeID = SalesOrders.EmployeeID)
LEFT JOIN
Receivables ON SalesOrders.SFSOID = Receivables.SFSOID
WHERE (((SalesOrders.IsComplete)=True) AND
((Receivables.DatePaid)>=[forms]![Report Date Range]![Beginning
Order
Date]
And (Receivables.DatePaid)<=[forms]![Report Date Range]![Ending
Order
Date]))
GROUP BY SalesOrders.IsComplete
, SalesOrders.SFSOID
, Receivables.DatePaid
, Customers.CompanyName
, Employees.LastName
, Employees.CommissionRate
, Format((Nz([CCCharge],0)+nZ([Adjustments],0)),"Currency")
, (SELECT Sum(OriginalAmount) AS TotalPaid
FROM Payables
WHERE Payables.SFSOID = SalesOrders.SFSOID)
, Nz((SELECT Sum(FreightCoInvoiceAmt)
FROM Payables
WHERE Payables.SFSOID = SalesOrders.SFSOID),0)
,
Format(nZ([BestWayNJ],0)+nZ([WestCoastCA],0)+nZ([BarFreightIL],0)+nZ([ConsolidatedTX],0)+nZ([EvansMI],0)+nZ([Surcharge],0),"Currency")
ORDER BY Employees.LastName;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Here is the ugly beast thus far:
SELECT DISTINCT SalesOrders.IsComplete, Receivables.DatePaid,
SalesOrders.SFSOID, Customers.CompanyName, Employees.LastName,
Employees.CommissionRate, (SELECT Sum(AmountPaid) AS TotalReceivables
FROM
Receivables WHERE [Receivables].[SFSOID]=[SalesOrders].[SFSOID] AND
(((Receivables.DatePaid)>=[forms]![Report Date Range]![Beginning
Order
Date]
And (Receivables.DatePaid)<=[forms]![Report Date Range]![Ending Order
Date]))) AS TotalReceivables,
Format((Nz([CCCharge],0)+nZ([Adjustments],0)),"Currency") AS CCA,
(SELECT
Sum(OriginalAmount) AS TotalPaid FROM Payables WHERE Payables.SFSOID
=
SalesOrders.SFSOID) AS TotalPaid, Nz((SELECT Sum(FreightCoInvoiceAmt)
AS
TotalPaidFreight FROM Payables WHERE Payables.SFSOID =
SalesOrders.SFSOID),0)
AS TotalPaidFreight,
Format(nZ([BestWayNJ],0)+nZ([WestCoastCA],0)+nZ([BarFreightIL],0)+nZ([ConsolidatedTX],0)+nZ([EvansMI],0)+nZ([Surcharge],0),"Currency")
AS WareAndSurcharge
FROM ((Employees INNER JOIN (Customers INNER JOIN SalesOrders ON
Customers.CustomerID = SalesOrders.CustomerID) ON
Employees.EmployeeID =
SalesOrders.EmployeeID) LEFT JOIN Payables ON SalesOrders.SFSOID =
Payables.SFSOID) LEFT JOIN Receivables ON SalesOrders.SFSOID =
Receivables.SFSOID
WHERE (((SalesOrders.IsComplete)=True) AND
((Receivables.DatePaid)>=[forms]![Report Date Range]![Beginning Order
Date]
And (Receivables.DatePaid)<=[forms]![Report Date Range]![Ending Order
Date]))
ORDER BY Employees.LastName;
Tables SalesOrders, Payables, and Receivables are linked one to many
with
SalesOrder the top of the food chain.
:
It would help if you posted the SQL of the query that is not working
(View:
SQL). And some details about your table structure.
GUESSING that maybe what you want is something like:
SELECT S.OrderNo, Sum(R.ReceivableAmount) as TotalReceived
, (SELECT Sum(AmountDue) FROM Payables WHERE Payables.OrderNo =
S.OrderNo)
as AmountDue
FROM SalesOrders as S INNER JOIN Receivables as R
ON S.OrderNo = R.OrderNo
WHERE R.DatePaid Between #1/1/07# and #1/31/07#
GROUP BY S.OrderNo, (SELECT Sum(AmountDue) FROM Payables WHERE
Payables.OrderNo = S.OrderNo)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
I am having difficulty designing a query and would like some help.
The
query
should return only ONE row per SalesOrder. The query should SUM
the
receveibles from a table Receivables where SalesOrderNo is the
foreignkey
linking to Parent SalesOrder table and the receivable was paid
within a
specified date range. Currently I am getting a row for EACH
receivable
within a date range. Payables are summed regardless of the date
range.
Just
fetch all costs for the given SalesOrderNo. Any help? Do I need
to be
more
specific? Thanks.
Dave
