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  
Share this Question
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
90119. 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.  
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 90119. 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.  
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 90119. 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.   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 1769
 replies: 3
 date asked: Nov 12 '05
