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 3 2084
"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.
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tiago Miguel Silva |
last post by:
I there. I´m almost desperate with a report that i´m doing!
I am creating a report using the push method, being the data returned by a
typed dataset.
The report shows activities by week and...
|
by: Andrea |
last post by:
I'm having some difficulty creating a report in Access and I need some
suggestions. My company issues "Return Authorizations" when customers
need to return products. A customer calls in and we...
|
by: AR Hall |
last post by:
This code goes through 3 work sheets in the same XL workbook, day, eve and
night shift
in each sheet their are columns representing production work orders and
reject and downtime for each order...
|
by: Sami |
last post by:
Could someone explain clearly how to go about doing this? I have
tried setting up the structure on numerous occasions, but it never
seems to work. Could someone please help me out? Thanks!
|
by: Neil MacDonald |
last post by:
Hi. Thanks in advance for your help.
I have a database in which one report will not show the text from a memo
field. If I run the underlying query, the field shows up in the table,
with all...
|
by: Bonj |
last post by:
I'm mainly experienced in VB6, haven't really got to grips
with .NET very much. I'd like to create, more as a
practice project than anything, a simple ASP.NET web
application that will just do...
|
by: Shourie |
last post by:
I've noticed that none of the child controls events are firing for the first time from the dynamic user control. Here is the event cycle.
1) MainPage_load
2) User control1_Load
user clicks a...
|
by: Teemu |
last post by:
Hello.
Please bear with me if this seems like a basic question (I'm just getting started with all this ASP.NET and WebControls stuff).
Anyway, I'm trying to programmatically build a...
|
by: Keith C. Perry |
last post by:
Ok, I've tried a number of things here and I know I'm missing something but at
this point my head is spinning (i.e. lack of sleep, too much coffee, etc...)
My environment is PG 7.4.3 on Linux...
|
by: Bill |
last post by:
I have a 200 record database that includes a date/time field,
AnnivDate, for a wedding anniversary. AnnivDate has nulls and some
incorrect year data. I have been creating the Access database...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |