Hi Allen,
Thanks for your help - I also went out to your web page and found that
very
useful - thanks.
Here's my SQL for the query:
SELECT qryLocalChargeProjects.chJobNumber, qryLocalChargeProjects.
chrProjectName, qryLocalChargeProjects.chrProjectTown, Max
(qryLocalChargeProjects.dtmDate) AS MaxOfdtmDate,
qryLocalChargeProjectsSum.
[SumOfTotal Fees], qrySumOfInvoicesByProject.[SumOfTotal Invoice], Max
(qryProjectInvoices.dtmInvoiceDate) AS MaxOfdtmInvoiceDate, Sum
(qryLocalChargeProjects.[Total Fees]) AS [SumOfTotal Fees1]
FROM ((qryLocalChargeProjects INNER JOIN qrySumOfInvoicesByProject ON
qryLocalChargeProjects.chJobNumber =
qrySumOfInvoicesByProject.chrJobNumber)
INNER JOIN qryProjectInvoices ON qrySumOfInvoicesByProject.chrJobNumber =
qryProjectInvoices.chrJobNumber) INNER JOIN qryLocalChargeProjectsSum ON
qryLocalChargeProjects.chJobNumber = qryLocalChargeProjectsSum.chJobNumber
GROUP BY qryLocalChargeProjects.chJobNumber, qryLocalChargeProjects.
chrProjectName, qryLocalChargeProjects.chrProjectTown,
qryLocalChargeProjectsSum.[SumOfTotal Fees], qrySumOfInvoicesByProject.
[SumOfTotal Invoice]
HAVING (((Max(qryLocalChargeProjects.dtmDate)) Between
DateSerial(Year(Date())
,Month(Date())-2,1) And (Date()-Day(Date()))))
ORDER BY Max(qryLocalChargeProjects.dtmDate);
So, I tried adding the following in the criteria under the last column
Total
Fees
(SELECT chJobNumber, Sum([Amount]) AS SumOfAmount FROM
qryLocalChargeProjects
WHERE dtmInvoiceDate > Nz((SELECT Max ([dtmInvoiceDate]) AS MaxOfDate FROM
qryLocalChargeProjects AS Dupe WHERE Dupe.chrJobNumber =
qryLocalChargeProjects.chJobNumber), #1/1/1900#)
But I get an error that says: "You have written a subquery that can return
more than one field without uising the EXISTS reserved word in the main
query's FROM clause. Revice the SELECT statement of the subquery to
request
only one field"
What am I doing wrong?
Thanks,
Adele.
Allen said:
Perhaps something like this:
SELECT CustomerID,
Sum([Amount]) AS SumOfAmount
FROM Table1
WHERE InvoiceDate >
Nz((SELECT Max([InvoiceDate) AS MaxOfDate
FROM Table1 AS Dupe
WHERE Dupe.CustomerID = Table1.CustomerID), #1/1/1900#);
If subqueries are a new concept, here's an introduction:
http://allenbrowne.com/subquery-01.html
I am running a query that shows total fees, total amount invoiced and
date
of
last invoice. I want to be able to show the fees accrued since the last
invoice date - could anyone advise?