Summing numbers after a specific date

[pandaw1 via AccessMonster.com]

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?

Many Thanks.

 

[Allen Browne]

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

 

[pandaw1 via AccessMonster.com]

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.

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?

 

[Allen Browne]

Yes, that's an issue with subqueries.

3 solutions:
http://allenbrowne.com/subquery-02.html#AtMostOneRecord

You also have the choice of EXISTS or IN.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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.

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?

 

[pandaw1 via AccessMonster.com]

Hi Allen,

Sorry - I need more basic help - I still can't work out what I should do here.


Many Thanks,
Adele.

Yes, that's an issue with subqueries.

3 solutions:
http://allenbrowne.com/subquery-02.html#AtMostOneRecord

You also have the choice of EXISTS or IN.

Hi Allen,

[quoted text clipped - 65 lines]

 

[Allen Browne]

Okay, here's a simplified version of the query you posted, without the
subquery:

SELECT qryLocalChargeProjects.chJobNumber,
First(qryLocalChargeProjects.chrProjectName) AS TheProject,
First(qryLocalChargeProjects.chrProjectTown) AS TheTown,
Max(qryLocalChargeProjects.dtmDate) AS MaxOfdtmDate,
qryLocalChargeProjectsSum.[SumOfTotal Fees],
qrySumOfInvoicesByProject.[SumOfTotal Invoice],
Max(qryProjectInvoices.dtmInvoiceDate) AS MaxOfdtmInvoiceDate,
Sum(qryLocalChargeProjects.[Total Fees]) AS SumOfTotalFees1
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
WHERE qryLocalChargeProjects.dtmDate
Between DateSerial(Year(Date()),Month(Date())-2,1)
And (Date()-Day(Date()))))
GROUP BY qryLocalChargeProjects.chJobNumber,
Max(qryLocalChargeProjects.dtmDate)
ORDER BY Max(qryLocalChargeProjects.dtmDate);

Fix it up as needed so you get it working, and save it as (say)
qryJobMaxInvoice.

Now you can create another query based on that one, to give you the previous
invoice date. Type an expression like this in the Field row:

(SELECT Max(dtmInvoiceDate) AS TheDate
FROM qryProjectInvoices
WHERE qryProjectInvoices.chJobNumber
= qryJobMaxInvoice.chJobNumber
AND dtmInvoiceDate < MaxOfdtmInvoiceDate)

Now that you have both dates, you can create a query that sums the invoice
amounts between the 2 dates.

I'm sure there is a simpler process here, but at least that should get you
there.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Sorry - I need more basic help - I still can't work out what I should do
here.


Many Thanks,
Adele.

Yes, that's an issue with subqueries.

3 solutions:
http://allenbrowne.com/subquery-02.html#AtMostOneRecord

You also have the choice of EXISTS or IN.

Hi Allen,

[quoted text clipped - 65 lines]

last invoice. I want to be able to show the fees accrued since the
last
invoice date - could anyone advise?

 

[Dennis Miley]

There are 521 of these messages in my Mail VIEW, Unread box. Why can't I
delete any of them?

Dennis