473,322 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Help with creating report

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
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.
Nov 12 '05 #2
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
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...
2
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...
0
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...
2
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!
1
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...
0
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...
1
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...
0
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...
2
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...
2
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.