Aging report

I would like to create a report that shows all 'Bad" records  0 -30
days, 31-60 days, 61 -90 days., 90 - 180 days
The data would come from a table called transactions, bases on the
status field.

6/10/2007 5:03:27 AM
access 16762 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 24

In article <>, says...
> Hi,
> I would like to create a report that shows all 'Bad" records  0 -30
> days, 31-60 days, 61 -90 days., 90 - 180 days
> The data would come from a table called transactions, bases on the
> status field.
This is just by way of example since you did not furnish any information to 
work with.

Open Northwind sample database and select new query and past this into the SQL 
view. (watch for line wrapping)

SELECT   Customers.CompanyName,
  (SELECT SUM(IIF(DATEDIFF("d",[a.OrderDate],[a.ShippedDate]) < 5,+1,0))
  FROM   Orders AS a
  WHERE  Orders.CustomerID = a.CustomerID) AS [Less than 5 days],
  (SELECT SUM(IIF(DATEDIFF("d",[a.OrderDate],[a.ShippedDate]) BETWEEN 6 AND 10,
  FROM   Orders AS a
  WHERE  Orders.CustomerID = a.CustomerID) AS [6 - 10 days],
  (SELECT SUM(IIF(DATEDIFF("d",[OrderDate],[ShippedDate]) BETWEEN 11 AND 15, +
  FROM   Orders AS A
  WHERE  Orders.CustomerID = a.CustomerID) AS [11 - 15 days],
  (SELECT SUM(IIF(DATEDIFF("d",[OrderDate],[ShippedDate]) > 15, +1,0))
  FROM   Orders AS A
  WHERE  Orders.CustomerID = a.CustomerID) AS [Greater than 15 days]
FROM     Customers
         INNER JOIN Orders
           ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID,Customers.CompanyName;
6/10/2007 11:49:13 PM

Similar Artilces:

Age calculator
Hello, I hope someone can help me with this. I need to calculate someone's current age based on DOB and indicate if someone is closer to their last birthday or next birthday. Thank you MD On Feb 16, 8:50=A0pm, "Marc D" <> wrote: > I need to calculate someone's current age based on DOB First, what you don't want to do: some arithmetic involving either 365 or 365.25. Whether you use one or the other with ROUND or INT, we can find an example with an off-by-one error. Second, ostensibly, you want to use DATEDIF(A1,A2,"y"), where...

Calculating age in a label or text box on a form
Hello All, I have a text box for birthdate for a user to enter the birthdate and I want to the age for the user to be automatically calcuated and displayed in another label called "Age." What formula can I use? Missy ...

I see many example of age from date of birth Howevere when the year of the date of birth is over 1929 ie age 88 the age turn out to be negative say dob is 01 jan 1928 or less Can any ne help? klam Klam, have a look at this about age functions and why some work and others do not. Jeanette Cunningham "Klam" <> wrote in message >I see many example of age from date of birth > Howevere when the year of the date of birth is over 1929 ie age 88 t...

can I have a fill-in field in a report ?
I have a report that gives a value in the report footer. The value needs to be in euros, numbers and words. I can calculate the numbers okay, but as far as I am aware there is no function to convert numbers into words in Dutch. Thus, I need to have a fill-in field for the value in words, with the prompt including the value in numbers ! Is this possible ???? I will be eternally grateful for any answers ... Rod See: Convert Currency ($500) into words (Five Hundred Dollars) at: Since you clearly understand English as well, it shoul...

Reports per Mail to Blackberry Problem
Hi all, we are trying to implement the reporting services, to create daily report and send them via eMail to certain users in the morning. Now, creating and sending of the reports as PDF or Excel-Files works fine. BUT when the Mails are replicated to the Blackberry Devices the Attachments are not sent to it, as i understood the files are only created as "Temporary" files. When you take the mail from the reporting services and forward it to yourself (and so also to the Blackberry) the attachment is synched too. Any idea, how i could force the Reporting services to ... generating reports
I would like suggestions(or modifications) on ways to architect the following: Goal is to create a set of sql server reports 2008 that has a table of contents and is exported to word 2003 or word 2007. 1. I am going to have each sql server report 2008 as separate RDL files. 2. I am going to tie all the reports together in a Visual Basic.NET application by Building Reports from the Object Collection using ReportViewer. 3. I then need to create a table of contents that exports to word. To accomplish this task I am thinking of a. either using jquery so that I can add auto...

CRM4 with Rollup 8 User Summary Report black blackground overtakes
Hi, Wondering if anyone would have similar experience with Rollup 8 and CRM4 where the user summary report showing all black blackground in the report viewer? They showed up ok when export to excel. We have SSRS 2008, CRM4, with rollup 8 installed. And if so, how do you fix it? Is it a problem with the report viewer or the report definition? SW Found out to be a bug in IE8. If you have SSRS2008 and IE8, then you will see the black backgound overtook the entire report. If you have SSRS2005 or IE7, then it's ok. "SW" wrote: > Hi, > Wondering if anyone would hav...

Report Source
I have a big picture question regarding the recordsource of a report. In the past I setup a huge query and most reports run off this query. By huge, I mean the normalization is reversed and I have the text I need to display on the report and the number I need to filter it. I build a criteria string from the users selections in my unbound report form. The final command is this: DoCmd.OpenReport [Report], acViewNormal, , [Criteria] I guess my question is, is this the right way to do things? It seems to me the report output will get slower the more information is entered into the tables. ...

How to Pass Filter from Query to Report?
Hello, I would appreciate any help with this problem that's been driving me nuts. I have a query of inventory items with only a subset of fields from the complete inventory datasheet. I have designed a report that will take the items from the query and print tags. Sometimes, I would like to print only a few tags. I apply a filter to one of the columns in the query datasheet, but this filter has no effect on the report. What can I do so that when I apply a filter to the query, it will also be reflected in the report? I was thinking of putting a formula of some kind in the filter pro...

distribution group / delivery report exchange 2003
an originator from outside my exchange 2003 organisation is sending an email to a distribution list, but he never receive a delivery report global config, message format for domain * (is ok) [x] delivery report [x] non delivery report "Ronald" <> wrote: >an originator from outside my exchange 2003 organisation >is sending an email to a distribution list, >but he never receive a delivery report Did he request one? -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at ...

Custom Reports from My Reports error
When trying to run a custom report from the My Reports dropdown or from the home page I get the error: Unhandled script exception: Cannot find report "[Report Name]". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORTDICT There are no options on this report and it runs fine from the Reports -> Custom reports section. Is this just a bug with the home page? ...

ZZ report error
I have a customer who does a Z report at the end of each shift and a ZZ report at the end of the day after his final Z report. When he runs the ZZ report it prints out the next Z report with all zeros. He is 100% sure that he is choosing the ZZ report option, but it seams to just print the next Z report which is all zeros since he just zeroed out. I have yet to see this in person but did see a copy of his report. Is there anything that would not allow him to print a ZZ report and just default to a Z report? Very strange. Thanks when you run a ZZ report it automatically prints a Z...

vlookup in aging
it's been a long day and i know this is simple to do, but i think i am burned out: =VLOOKUP(J2,{0,"0-30";30,"60";61,"120";121,"180";181,"300";241,"300";301,"365+"},2) need to be able to carry over to my pivot joe As-is, your formula looks ok in creating the "buckets" text labels col for your pivoting. It returns the required text label ("bucket descript") depending on the numerical values in J2 down. Maybe just add a simple front IF check for blank cells and do an "add zero&quo...

Problem with Report Filter
Hi, I'm trying to make my reports in REPORTING SERVICE 2000! The report are ok, but when I try do add a filter in CRM, appear a msg saying that I can't add a filter in my report! Any one can help me ?? Hi, You will need to prepare your report for filtering. you can do this by adding an alias to the filteredview with the prefix "CRMAF_". That means that you should not use this as query: "select * from filteredaccount" instead use this query: "select * from filteredaccount as CRMAF_filteredaccount" then you can use prefiltering in your report. Hope t...

Report exporting different data from what is on the report
I have a standard Access 2007 report (based on a query) in my database that pulls information on a specific item. The report runs fine but when the user right clicks on the report and exports to Excel, they get the information for a different item. I had them delete the export file and try renaming it. Neither worked. The item they are getting information on is one that they exported prior. Now it is the only record that will export, regardless of what the report is showing. Help Please. SK - Since I have never seen this, I wonder if it is one of those user errors ...

age conversion
I'm trying to code a formula to calculate age for a patient based on the date of birth. could anyone tell me the formula. date of birth is given, we need the database to tell us the age See: -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - Reply to group, rather than allenbrowne at mvps dot org. "holly" <> wrote in message > I'm trying to code a formula to calculat...

Open Order Report
How do i lock the data on the open order report so it will display the order information even thought it has been shipped and invoiced? You can't. Once an order has shipped and invoiced, the order is moved to history. It is no longer an open order. "Big Arch" wrote: > How do i lock the data on the open order report so it will display the order > information even thought it has been shipped and invoiced? ...

FRX reporting
How do you prepare an FRX report that includes some summarized rows (e.g. "regular pay" includes accounts for regular, vacation, sickleave, etc.) and some detail rows that show account detail? The Print Control "XD" in the row format suppresses the account detail in a detail report but also suppresses the summary line entirely. Do this in your row setup. For the Summary Account use ???? in you account string and for the detail use the full account number. "Tom H." wrote: > How do you prepare an FRX report that includes some > summarized rows (e.g. "...

Report #2
Hi How do create a report for my Chq Account where I can see the following information. Here is an example: Balance at the 30th Oct 2006 Total Paid In Total Paid Out Balance on 28th Nov 2006 Thank you in advance. Kind regards Apr, I'm assuming that you are using Money 2007. This should be similar in other versions though. Go to Reports Home, under Income and expenses, choose Account transactions. When the report opens, from the left panel choose Customize... On the Rows & Columns tab select "Months" from the Subtotal by: list and "Use total of prior transacti...

How to make aging possible in Excel for an age trial balance shee.
Can anyone help? I am trying to create an age trial balance sheet in Excel, however can't get it to age accordingly. Hi, Anne. :) We'll need more information than that, such as what cells your due dates are in, how many days out they go before they're "aged" and how many agings you want, etc. ******************* ~Anne Troy "Anne L" <Anne> wrote in message > Can anyone help? I am trying to create an age trial balance sheet in Ex...

emailing a report or Word mail merge
Hey there! I've been thumbing around with this problem for so long I have completely lost the plot. Yes, I know this is an FAQ but I'd appreciate some help on the architechture as well as the coding of this. I have an Access DB that has a table of new clients. Each client get a welcome letter emailed to them, and their email addys are housed in the table of new clients. It's a one page letter. What I want is for each client to get their one page letter in the body of an email, not as an attachment. I can think of maybe half a dozen ways to do this. But if ...

Accessing Modified Reports
I used Report Writer to modify the PM Transacation Posting Journal which is set up to run automatically when posting a batch. I then went back to GP/security and granted users/companies access to the modified report. However, when the report is run, the original reports is being run and not the modified report. Everyone is pointing to the same reports dictionary located on the server. What else do I need to do to access the modified report? Do you have security turned on for your company? Setup-company-company, checkbox Also, the report that you modified, is your locatoin of your dynami...

WriteOff Report
Is There a way to print a writeoff report or to add that field to de RM Customer Sales Summary Report. the field is not listed in the toolbox Thanks ...

Chart OUTLOOK item reports via Excel or ?
I want to create reports for OUTLOOK items (particularly tasks). I'd like to be able to see charts that display things like the amount of time spent on tasks for various clients, the type of work done, and the dates (including time of day). I'd like to be able to look at these reports daily if possible. Is there a way that I can do this? I thought that there must be a way by exporting the items into excel and then manually setting up charts but this would be time consuming to do each time. Any help is appreciated (I've spent days trying to figure this out) ...

I am doing an aging report. Which table should I look at for the invoices was not paid or not totally paid? What's the meaning of the values of dcstatus in rm00401? Thanks. Frank, For dcstatus 1 = Work, 2 = Open, 3 = History. Basically indicating which tables the document will reside in. Partially paid receivables invoices should be in RM20101 and should have some amount in the CURTRXAM field. Payment info should be in RM20201. Fully paid invoices should be in RM30101. Payment info should be in RM30201. -- Jim@TurboChef "Frank Wang" wrote: > I am doing an agin...