aging query and report

[janschutz]

I need to create an aging report. I have the amount ,the due date of the
amount. I need to show current period 1 to 30 days late, 31 to 60 days late,
61 to 90 days late and over 91 days.

 

[Tom Ellison]

Dear Jan:

I would typically create a subquery for each "aging" bracket column. For
details on writing this query, please provide detials of the columns and
tables from which this is to be done.

Tom Ellison


"(e-mail address removed)"

 

[janschutz]

First the data is coming from an external source that Access is getting the
data through ODBC. Second the Data File/table is called Public_APOPEN I
created a query that contains the vendor#, voucher#, invoice#,date_due and
the amount field.

 

[Tom Ellison]

Dear Jan:

The query might be:

SELECT [vendor#],
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() AND Date() - 30)
AS Current,
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() - 31 AND Date() - 60)
AS [1 to 30]
FROM Public_APOPEN T
ORDER BY [vendor#]

The above is just a start, but you should be able to extend it for
additional aging periods.

Warning: This query depends on the system date being set accurately. It
might be better to show the user a text box with the current system date in
it, but allow them to correct that date. Change the query to refer to this
control.

Tom Ellison


"(e-mail address removed)"

 

[Phil]

I need to create an aging report. I have the amount ,the due date of the
amount. I need to show current period 1 to 30 days late, 31 to 60 days late,
61 to 90 days late and over 91 days.

Tom uses seperate queries.I have a different approach.

You have two fields. DueDate, and InvTotal. Then you have AgingDate,
the date you are running the teh aging for. Now() works OK as well.

In query view:, here are your four fields:

30Days:iif([AgingDate]-[DueDate]>=1 and
[AgingDate]-[DueDate]<=30,[InvTotal],0]

60Days:iif([AgingDate]-[DueDate]>=31 and
[AgingDate]-[InvDate]<=60,[InvTotal],0]

90Days:iif([AgingDate]-[DueDate]>=61 and
[AgingDate]-[DueDate]<=90,[InvTotal],0]

Over90:iif([AgingDate]-[InvDate]<=91,[InvTotal],0]


What it does is drop the invoice amount into the proper bucket, puts
zeros in the rest. Sort by customer, and subtotal on those fields. I
like it because it gives it to you in one query, and in my experience,
runs a little faster.

Phil

 

[janschutz]

Were do I type this statement? Would it be in query window and a build line?

Dear Jan:

The query might be:

SELECT [vendor#],
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() AND Date() - 30)
AS Current,
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() - 31 AND Date() - 60)
AS [1 to 30]
FROM Public_APOPEN T
ORDER BY [vendor#]

The above is just a start, but you should be able to extend it for
additional aging periods.

Warning: This query depends on the system date being set accurately. It
might be better to show the user a text box with the current system date in
it, but allow them to correct that date. Change the query to refer to this
control.

Tom Ellison


"(e-mail address removed)"

First the data is coming from an external source that Access is getting
the
data through ODBC. Second the Data File/table is called Public_APOPEN I
created a query that contains the vendor#, voucher#, invoice#,date_due and
the amount field.

 

[Tom Ellison]

Dear Jan:

Open a new query and select the SQL View for the query. Paste in the code I
posted.

Tom Ellison


"(e-mail address removed)"

Were do I type this statement? Would it be in query window and a build
line?

Dear Jan:

The query might be:

SELECT [vendor#],
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() AND Date() - 30)
AS Current,
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() - 31 AND Date() - 60)
AS [1 to 30]
FROM Public_APOPEN T
ORDER BY [vendor#]

The above is just a start, but you should be able to extend it for
additional aging periods.

Warning: This query depends on the system date being set accurately. It
might be better to show the user a text box with the current system date
in
it, but allow them to correct that date. Change the query to refer to
this
control.

Tom Ellison


"(e-mail address removed)"

First the data is coming from an external source that Access is getting
the
data through ODBC. Second the Data File/table is called Public_APOPEN
I
created a query that contains the vendor#, voucher#, invoice#,date_due
and
the amount field.

:

Dear Jan:

I would typically create a subquery for each "aging" bracket column.
For
details on writing this query, please provide detials of the columns
and
tables from which this is to be done.

Tom Ellison


"(e-mail address removed)"
message
I need to create an aging report. I have the amount ,the due date of
the
amount. I need to show current period 1 to 30 days late, 31 to 60
days
late,
61 to 90 days late and over 91 days.

 

[janschutz]

Thanks, I will give that a try.

Dear Jan:

Open a new query and select the SQL View for the query. Paste in the code I
posted.

Tom Ellison


"(e-mail address removed)"

Were do I type this statement? Would it be in query window and a build
line?

Dear Jan:

The query might be:

SELECT [vendor#],
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() AND Date() - 30)
AS Current,
(SELECT SUM(amount)
FROM Public_APOPEN T1
WHERE T1.[vendor#] = T.[vendor#]
AND date_due BETWEEN Date() - 31 AND Date() - 60)
AS [1 to 30]
FROM Public_APOPEN T
ORDER BY [vendor#]

The above is just a start, but you should be able to extend it for
additional aging periods.

Warning: This query depends on the system date being set accurately. It
might be better to show the user a text box with the current system date
in
it, but allow them to correct that date. Change the query to refer to
this
control.

Tom Ellison


"(e-mail address removed)"
First the data is coming from an external source that Access is getting
the
data through ODBC. Second the Data File/table is called Public_APOPEN
I
created a query that contains the vendor#, voucher#, invoice#,date_due
and
the amount field.

:

Dear Jan:

I would typically create a subquery for each "aging" bracket column.
For
details on writing this query, please provide detials of the columns
and
tables from which this is to be done.

Tom Ellison


"(e-mail address removed)"
message
I need to create an aging report. I have the amount ,the due date of
the
amount. I need to show current period 1 to 30 days late, 31 to 60
days
late,
61 to 90 days late and over 91 days.