By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Help with creating report

P: n/a
I am trying to figure out how to create a report that will return orders
current, over 30, over 60, over 90, and over 120. In one table I have
Cust_Code, INV_Date, INV_Due_Date, INV_Bal and in another table is the
customer information and there current balance. How do I display each
company with the appropriate current, over 30, over 60, over 90, and over
120 and if the value for a total is 0 then display a - (dash).
Thanks for any help

Greg
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Greg" <gk******@itlan.net> wrote in
news:Wo******************@nwrdny01.gnilink.net:
I am trying to figure out how to create a report that will
return orders current, over 30, over 60, over 90, and over
120. In one table I have Cust_Code, INV_Date, INV_Due_Date,
INV_Bal and in another table is the customer information and
there current balance. How do I display each company with the
appropriate current, over 30, over 60, over 90, and over 120
and if the value for a total is 0 then display a - (dash).
Thanks for any help

Greg
The general idea is to calculate the number of days between
INV_Due_Date and today, and divide by 30. The integer portion is 0
for less that 30, 1 for between 30 and 59, 2 for 60 to 89, 3 for
90-119. Nest that in an iif( x > 4, 4, x) and use that as a source
for a crosstab query. Use the crosstab query in a standard query to
pull in company info. Build your report on this last query.

Select Cust_Code,
30 * iif((now()-Inv_due_Date)\30>4, 4,
(now()-Inv_due_Date)\30) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code, iif((now()-Inv_due_Date)\30>4, 4,
(now()-Inv_due_Date)\30);

Remember the \ makes integer divisions.

Bob Q.
Nov 12 '05 #2

P: n/a
Bob,
Thanks for the help...... not sure if I completely get ti but will give it a
shot and see. The final results that I am looking for will show how the
total that is current, the total over 30, the total, over 60, etc.........
for each company.

Thanks again,

Greg

"Bob Quintal" <bq******@generation.net> wrote in message
news:08******************************@news.teranew s.com...
"Greg" <gk******@itlan.net> wrote in
news:Wo******************@nwrdny01.gnilink.net:
I am trying to figure out how to create a report that will
return orders current, over 30, over 60, over 90, and over
120. In one table I have Cust_Code, INV_Date, INV_Due_Date,
INV_Bal and in another table is the customer information and
there current balance. How do I display each company with the
appropriate current, over 30, over 60, over 90, and over 120
and if the value for a total is 0 then display a - (dash).
Thanks for any help

Greg

The general idea is to calculate the number of days between
INV_Due_Date and today, and divide by 30. The integer portion is 0
for less that 30, 1 for between 30 and 59, 2 for 60 to 89, 3 for
90-119. Nest that in an iif( x > 4, 4, x) and use that as a source
for a crosstab query. Use the crosstab query in a standard query to
pull in company info. Build your report on this last query.

Select Cust_Code,
30 * iif((now()-Inv_due_Date)\30>4, 4,
(now()-Inv_due_Date)\30) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code, iif((now()-Inv_due_Date)\30>4, 4,
(now()-Inv_due_Date)\30);

Remember the \ makes integer divisions.

Bob Q.

Nov 12 '05 #3

P: n/a
Which Query is the Corsstab to be used? If I try to use the query you
supplied below I am unable to put the INV_Due_Date as the Column Header
without having a repeating Cust_Code.

Thanks,
Greg

"Bob Quintal" <bq******@generation.net> wrote in message
news:08******************************@news.teranew s.com...
"Greg" <gk******@itlan.net> wrote in
news:Wo******************@nwrdny01.gnilink.net:
I am trying to figure out how to create a report that will
return orders current, over 30, over 60, over 90, and over
120. In one table I have Cust_Code, INV_Date, INV_Due_Date,
INV_Bal and in another table is the customer information and
there current balance. How do I display each company with the
appropriate current, over 30, over 60, over 90, and over 120
and if the value for a total is 0 then display a - (dash).
Thanks for any help

Greg

The general idea is to calculate the number of days between
INV_Due_Date and today, and divide by 30. The integer portion is 0
for less that 30, 1 for between 30 and 59, 2 for 60 to 89, 3 for
90-119. Nest that in an iif( x > 4, 4, x) and use that as a source
for a crosstab query. Use the crosstab query in a standard query to
pull in company info. Build your report on this last query.

Select Cust_Code,
30 * iif((now()-Inv_due_Date)\30>4, 4,
(now()-Inv_due_Date)\30) as Aging,
Sum(INV_Bal)
From Invoices
Group by cust_code, iif((now()-Inv_due_Date)\30>4, 4,
(now()-Inv_due_Date)\30);

Remember the \ makes integer divisions.

Bob Q.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.