Aging Query Calculation

[The Business Boy via AccessMonster.com]

Hello.. I have the following aging query:

SELECT tblOrders.AcctNumber, tblOrders.InvoiceNumber, tblOrders.InvoiceDate,
IIf(DateDiff("d",InvoiceDate,Date())<=30,StorageCharges,0) AS [0-30], IIf
(DateDiff("d",InvoiceDate,Date())>30 And DateDiff("d",InvoiceDate,Date())<=60,
StorageCharges,0) AS [31-60], IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,StorageCharges,0) AS [61-90], IIf
(DateDiff("d",InvoiceDate,Date())>90,StorageCharges,0) AS [91+]
FROM tblOrders
WHERE StorageCharges <
(SELECT Sum(PaymentAmount)
FROM tblPayments as P
WHERE P.InvoiceNumber = TblOrders.InvoiceNumber)
OR Not Exists
(Select *
FROM tblPayments as P
WHERE P.InvoiceNumber = TblOrders.InvoiceNumber);

The query comes out fine except for the fact that if an invoice is not paid
in full, it doesn't show up on the query. Any type of payment will result in
the invoice not showing up on this query. Is there a way to modify this code
so that any remaining balance is show on this report.

Thank you... HAPPY NEW YEAR!

 

[Smartin]

Hello.. I have the following aging query:

SELECT tblOrders.AcctNumber, tblOrders.InvoiceNumber, tblOrders.InvoiceDate,
IIf(DateDiff("d",InvoiceDate,Date())<=30,StorageCharges,0) AS [0-30], IIf
(DateDiff("d",InvoiceDate,Date())>30 And DateDiff("d",InvoiceDate,Date())<=60,
StorageCharges,0) AS [31-60], IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,StorageCharges,0) AS [61-90], IIf
(DateDiff("d",InvoiceDate,Date())>90,StorageCharges,0) AS [91+]
FROM tblOrders
WHERE StorageCharges <
(SELECT Sum(PaymentAmount)
FROM tblPayments as P
WHERE P.InvoiceNumber = TblOrders.InvoiceNumber)
OR Not Exists
(Select *
FROM tblPayments as P
WHERE P.InvoiceNumber = TblOrders.InvoiceNumber);

The query comes out fine except for the fact that if an invoice is not paid
in full, it doesn't show up on the query. Any type of payment will result in
the invoice not showing up on this query. Is there a way to modify this code
so that any remaining balance is show on this report.

Thank you... HAPPY NEW YEAR!

For one, methinks you have the wrong sign:
WHERE StorageCharges <
should be
WHERE StorageCharges >

Try this (your SELECT and FROM clauses are unchanged):

SELECT tblOrders.AcctNumber, tblOrders.InvoiceNumber, tblOrders.InvoiceDate,
IIf(DateDiff("d",InvoiceDate,Date())<=30,StorageCharges,0) AS [0-30], IIf
(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,
StorageCharges,0) AS [31-60], IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,StorageCharges,0) AS [61-90], IIf
(DateDiff("d",InvoiceDate,Date())>90,StorageCharges,0) AS [91+]
FROM tblOrders

WHERE StorageCharges >
(SELECT Sum(Nz(PaymentAmount,0))
FROM tblPayments as P
WHERE P.InvoiceNumber = TblOrders.InvoiceNumber);

Get rid of the OR portion... I don't fully understand why it doesn't
work, but the above does work in my testing.

 

[LiquorGuy via AccessMonster.com]

Hello.. I have the following aging query:

[quoted text clipped - 20 lines]

Thank you... HAPPY NEW YEAR!

For one, methinks you have the wrong sign:
WHERE StorageCharges <
should be
WHERE StorageCharges >

Try this (your SELECT and FROM clauses are unchanged):

SELECT tblOrders.AcctNumber, tblOrders.InvoiceNumber, tblOrders.InvoiceDate,
IIf(DateDiff("d",InvoiceDate,Date())<=30,StorageCharges,0) AS [0-30], IIf
(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,
StorageCharges,0) AS [31-60], IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,StorageCharges,0) AS [61-90], IIf
(DateDiff("d",InvoiceDate,Date())>90,StorageCharges,0) AS [91+]
FROM tblOrders

WHERE StorageCharges >
(SELECT Sum(Nz(PaymentAmount,0))
FROM tblPayments as P
WHERE P.InvoiceNumber = TblOrders.InvoiceNumber);

Get rid of the OR portion... I don't fully understand why it doesn't
work, but the above does work in my testing.

THE SIGN CHANGE works.. Thanks! However I would like to calculate the
remaining upaid portion and return that value in the query. RIght now the
query just returns the full balance irregardless of the payment amount.

 

[Smartin]

Hello.. I have the following aging query:

[quoted text clipped - 20 lines]

Thank you... HAPPY NEW YEAR!

For one, methinks you have the wrong sign:
WHERE StorageCharges <
should be
WHERE StorageCharges >

Try this (your SELECT and FROM clauses are unchanged):

SELECT tblOrders.AcctNumber, tblOrders.InvoiceNumber, tblOrders.InvoiceDate,
IIf(DateDiff("d",InvoiceDate,Date())<=30,StorageCharges,0) AS [0-30], IIf
(DateDiff("d",InvoiceDate,Date())>30 And
DateDiff("d",InvoiceDate,Date())<=60,
StorageCharges,0) AS [31-60], IIf(DateDiff("d",InvoiceDate,Date())>60 And
DateDiff("d",InvoiceDate,Date())<=90,StorageCharges,0) AS [61-90], IIf
(DateDiff("d",InvoiceDate,Date())>90,StorageCharges,0) AS [91+]
FROM tblOrders
WHERE StorageCharges >
(SELECT Sum(Nz(PaymentAmount,0))
FROM tblPayments as P
WHERE P.InvoiceNumber = TblOrders.InvoiceNumber);

Get rid of the OR portion... I don't fully understand why it doesn't
work, but the above does work in my testing.

THE SIGN CHANGE works.. Thanks! However I would like to calculate the
remaining upaid portion and return that value in the query. RIght now the
query just returns the full balance irregardless of the payment amount.

Cool. So now, wherever you have "StorageCharges" in the SELECT clause,
you would like to see unpaid balance?

Off the cuff, since this involves an aggregate query everywhere you have
"StorageCharges" (which is a lot of places!) I think I would write
another query to calculate Unpaid, and substitute that in the original
query. I didn't test this one. Probably best to take it step by step.
Make sure the subquery that follows works before you do anything else.

Assuming you want to do this by Invoice (as opposed to by Account) the
subquery would look something like

Query Unpaid:
SELECT
R.InvoiceNumber,
(R.StorageCharges - SUM(Nz(P.PaymentAmount,0))) AS Balance
FROM
tblOrders AS R LEFT JOIN tblPayments AS P
ON R.InvoiceNumber = P.InvoiceNumber
GROUP BY
R.InvoiceNumber;

Assuming that works... In your original query, replace "StorageCharges"
with "Unpaid.Balance". You will also need to join the Unpaid query to
the main query in your FROM clause:

becomes

FROM
tblOrders LEFT JOIN Unpaid
ON tblOrders.InvoiceNumber = Unpaid.InvoiceNumber

WOW I hope this works...

 

[LiquorGuy via AccessMonster.com]

[quoted text clipped - 26 lines]

remaining upaid portion and return that value in the query. RIght now the
query just returns the full balance irregardless of the payment amount.

Cool. So now, wherever you have "StorageCharges" in the SELECT clause,
you would like to see unpaid balance?

Off the cuff, since this involves an aggregate query everywhere you have
"StorageCharges" (which is a lot of places!) I think I would write
another query to calculate Unpaid, and substitute that in the original
query. I didn't test this one. Probably best to take it step by step.
Make sure the subquery that follows works before you do anything else.

Assuming you want to do this by Invoice (as opposed to by Account) the
subquery would look something like

Query Unpaid:
SELECT
R.InvoiceNumber,
(R.StorageCharges - SUM(Nz(P.PaymentAmount,0))) AS Balance
FROM
tblOrders AS R LEFT JOIN tblPayments AS P
ON R.InvoiceNumber = P.InvoiceNumber
GROUP BY
R.InvoiceNumber;

Assuming that works... In your original query, replace "StorageCharges"
with "Unpaid.Balance". You will also need to join the Unpaid query to
the main query in your FROM clause:

becomes

FROM
tblOrders LEFT JOIN Unpaid
ON tblOrders.InvoiceNumber = Unpaid.InvoiceNumber

WOW I hope this works...

I tried that query and I am getting an error:

you tried to execute that does not include the specified expression 'R.
StorageCharges - SUM(Nz(P.PaymentAmount,0))' as part of an aggregate function.

 

[Smartin]

Hello.. I have the following aging query:

[quoted text clipped - 26 lines]

remaining upaid portion and return that value in the query. RIght now the
query just returns the full balance irregardless of the payment amount.

Cool. So now, wherever you have "StorageCharges" in the SELECT clause,
you would like to see unpaid balance?

Off the cuff, since this involves an aggregate query everywhere you have
"StorageCharges" (which is a lot of places!) I think I would write
another query to calculate Unpaid, and substitute that in the original
query. I didn't test this one. Probably best to take it step by step.
Make sure the subquery that follows works before you do anything else.

Assuming you want to do this by Invoice (as opposed to by Account) the
subquery would look something like

Query Unpaid:
SELECT
R.InvoiceNumber,
(R.StorageCharges - SUM(Nz(P.PaymentAmount,0))) AS Balance
FROM
tblOrders AS R LEFT JOIN tblPayments AS P
ON R.InvoiceNumber = P.InvoiceNumber
GROUP BY
R.InvoiceNumber;

Assuming that works... In your original query, replace "StorageCharges"
with "Unpaid.Balance". You will also need to join the Unpaid query to
the main query in your FROM clause:

FROM tblOrders

becomes

FROM
tblOrders LEFT JOIN Unpaid
ON tblOrders.InvoiceNumber = Unpaid.InvoiceNumber

WOW I hope this works...

I tried that query and I am getting an error:

you tried to execute that does not include the specified expression 'R.
StorageCharges - SUM(Nz(P.PaymentAmount,0))' as part of an aggregate function.

Ah yes, sorry...

make the GROUP BY
R.InvoiceNumber
, R.StorageCharges - SUM(Nz(P.PaymentAmount,0))

 

[LiquorGuy via AccessMonster.com]

[quoted text clipped - 41 lines]

you tried to execute that does not include the specified expression 'R.
StorageCharges - SUM(Nz(P.PaymentAmount,0))' as part of an aggregate function.

Ah yes, sorry...

make the GROUP BY
R.InvoiceNumber
, R.StorageCharges - SUM(Nz(P.PaymentAmount,0))

Now it's saying I cant have aggregate function in GROUP BY Clause