Aging report

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.
TIA

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

1 Replies
1101 Views

Similar Articles

[PageSpeed] 47

In article <1181451807.600064.310670@a26g2000pre.googlegroups.com>, 
myxmaster@hotmail.com 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.
> TIA
> 
> 
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,
+1,0))
  FROM   Orders AS a
  WHERE  Orders.CustomerID = a.CustomerID) AS [6 - 10 days],
  (SELECT SUM(IIF(DATEDIFF("d",[OrderDate],[ShippedDate]) BETWEEN 11 AND 15, +
1,0))
  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;
0
Michael
6/10/2007 11:49:13 PM
Reply:

Similar Artilces:

How to encorporate Crystal Reports with VC++6.0/7.0
Hi, I want to some reports from the database from my VC++ application.Can any one tell me how can i do it ? i want crystal reports like stuff.Can i encorporate it with VC++? Help me out Reny ...

Age Formula
Hi all, I need to compute ages (as of 6-1-04) from dates of birth on a speadsheet I'm working on. I have NO idea where to start on that. Help please? Thanks in advance! Trish try this =DATEDIF(C6,"6/10/04","Y") -- Don Guillett SalesAid Software donaldb@281.com "Trish" <anonymous@discussions.microsoft.com> wrote in message news:1a55a01c44e51$e25e60d0$a501280a@phx.gbl... > Hi all, > > I need to compute ages (as of 6-1-04) from dates of birth > on a speadsheet I'm working on. I have NO idea where to > start on that. Help please? &...

Tender Report Issue
Hello! This has come up a couple of times but most recently with a big sale. If we have a transaction that is paid by two checks (for example) of the exact same amounte, the HQ tender report will only show one of the checks. We've had this issue with other tender types - if the exact amount is put through with a particular tender type (for example customer wants to use two different visa's for $100 and $100). Has anyone had this issue? Is there a way to have the tender report be correct? Thanks in advance. Diana ...

Rotate a Report on top of another report?
Is it possible to take one report and rotate it's contents (90) into the report footer of another report? Or is there a way to rotate a subreport? "Michael" <michael@kisielrudnik.com> wrote in message news:uiZIDIPtHHA.4796@TK2MSFTNGP04.phx.gbl... > Is it possible to take one report and rotate it's contents (90) into the > report footer of another report? > ...

Notes In a Report
I have a report that looks like the following: Market Project#1 Note Project#2 Note Project#3 Note Market2 Project#1 Note Project#2 Note Project#3 Note Sometimes a given Project# has multiple notes entered. Right now my report will handle the multiple notes by rewriting the Project# each time. I would like to list all the notes for a given Project# under that Project#: Market Project#1 Note1 Note2 Project#2 Note1 Project#3 Note1 Note2 Any ideas? Thanks Open report in design view, click on the tex...

Self Service & Reporting
Hi all, Does anyone know if there's a way to allow a client to report on all issues for themselves and their particular company? It'd also be nice if they could submit queries online rather than just via eMail. Dre Hi Andre, You will need to do something with the SDK to achieve this functionality. Basically, take the example of inserting a case/incident and build on it. Stephen "Andre Boyle" <andre@with.us> wrote in message news:%23KkUzETqEHA.3988@tk2msftngp13.phx.gbl... > Hi all, > > Does anyone know if there's a way to allow a client to repo...

aging report
I have a cell with a number in it and i want to put that number in another column based on number in that column. days 1-10 days 11-20 days 21-30 days > 30 days a1: 10 I want the number to show up in the corresponding column and put a zero in the other columns. I have a formula for the 1-10 days and the >30 days, but i need some help with a formula for the 11-20 and 21-30 days. thanks For the 11-20 days column, you could use a formula like: =IF(AND($A2>=11,$A2<=20),$A2,0) Then, copy the formula to the 21-30 days column, and adjust the 1...

Savings balance error in Monthly Report
I don't ususally run the monthly reports, but I did this month and noticed that Money was reporting that I have $158,388.62 in my Savings Account (which it claims earns 5.5%). This is under the heading of "Invest in your Debt". It is of course advising me to use some of that to pay down my current debt. As much as I wish this balance were correct, it ain't. Anyone know where Money gets this number and interest rate? Only thing I can think of is that maybe it's including my House. ...

Tab-Delimited Reports
My tab-delimited reports are not showing correctly. The columns are not lining up the data and sums. Does anyone know of a fix for this? We have some reports that are 60 pages long and scrubbing them is not an option. Please help. There is no easy answer. What I have been recommended is to modify the reports to take out a lot of the header and footer information and make sure columns line up. An easier answer, in the long run, is to use a report writer or SmartList to create and export to Excel. -- Charles Allen, MVP "Matthew" wrote: > My tab-delimited reports are...

Exchange
We're looking for a tool that would allow us to run a report to see who hasn't read a particular email message. Obviously we can create an email and choose the read receipt option, but when sending an email to a very large group of recipients keeping track of the read receipts to know who hasn't read the message becomes very prohibitive. Is there some way or some sort of reporting tool out there either for use directly in Exchange or in Outlook that would allow us to run a report or something similar against an email message that would detail which of the recipients haven&...

#Name? in a report
I have a report that was working in 2003 but since I've switched to 2007 I now get the #Name? In the report I do some calculating a GroupFooter0 in here I do the following cal. cctime1 = DSum("[Time1]", "[stagestats]", "[Time1] < 999 and class = '" & Class & "'") In the report section in the control Source I have =[cctime1] like I said this worked just fine in access 2003 but not I get #Name? Hopefully someone can tell me what's the difference between 2003 and 2007 that would cause this. Thanks for any help. ...

Automate Report name as header
Please, How can i make the report name automatically usable as an header e.g in excel you can type $[tab] 0r $[file] to use file names and tab names etc hi Deron, Deron wrote: > How can i make the report name automatically usable as an header e.g in > excel you can type $[tab] 0r $[file] to use file names and tab names etc Use =[Report].[Name] as ControlSource. mfG --> stefan <-- ...

daily report
can i create a report log for each day and make a macro button to allow users to purge into a wrkbook. Can the wrkbook be set up to take in each day log as a worksheet? How many days can i do? basically i want a journal of activity recorded on site and to have in here on spreadshhet. OR is it better to link it to another program such as word or access? any ideas as to which is effective for use. THANKS! The number of worksheets in a workbook is limited by your pc's memory. Have you thought of adding a time/date to your log and bringing the log into just one worksheet (limit of...

Modifying reports with VB6
I have a program written in VB6 with an mdb file backend on the server in Access 2000 format. I have reports that I create on my computer that has Access installed that I need people on other computers to be able to print. The problem is the old issue of Access resetting the paper size if it happens to run out of legal paper. The code that I've seen and used to fix this problem depends on being able to create an instance of the Access app. This of course does not work on a PC that doesn't have Access installed. Is there a way to programmatically do this from VB6 without the Access app...

Calculate Age...?
I have a sheet with the formula.. =DATEDIF(A20,NOW(),"Y")&":Yrs "&DATEDIF(A20,NOW(),"YM")&":Months "&DATEDIF(A20,NOW(),"MD")&":Days" where A20 contains a birthday...! Can any of you clever guys tell me why It doesn't seem to be working correctly. Its supposed to return the age "X:Yrs Y:Months Z:Days". Thanx for any assist. Andrew Andrew, The formula works fine for me. Exactly what problems are you having? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, L...

How to create custom reports?
How can i create custom reports? do i need any other software or can it be done with default CRM installation ? thanks Hi Jazz, You will need crystal deport designer to write your own custom report. Rgds, Kyaw "Jazz" <anonymous@discussions.microsoft.com> wrote in message news:05e501c4ac06$e9716300$a301280a@phx.gbl... > How can i create custom reports? > > do i need any other software or can it be done with > default CRM installation ? > > thanks is that a seperate product ... installed on same machine ? does that use crm tables ? and can i show...

The Afternoon Newspaper : Daily Market Report for: Thursday (September 17, 2009)
The Afternoon Newspaper : Daily Market Report for: Thursday (September 17, 2009) NSE India : S&P CNX Nifty Daily Market Report for: Thursday (September 17, 2009) (Based on the activity of the previous trading day) By Dominic Rebello Review of the Previous day: The Nifty rose substantially on Wednesday (September 16, 2009) a net 66.30 points (1.36%) and closed at 4958 point level. The market opened up and continued so throughout the day until closing near the day high. Incidentally, this is the highest close the Nifty has recorded since May 2008. Sentiment was extremely bullish and 40...

Individual Reports
My query look like this: Catergory V Factor Countfield Action VP EQ1 Tax 10 Done Tim .P EQ1 Untax 11 Pending Tim .P EQ2 Tax 9 Done Sam .S JW1 Tax 11 Done John .T JW3 Tax 8 Pending John .T How can I get my report to print each VP on a different (SNP) file, without doing it three times. Example: An SNP file with just Tim .P with 2 records An SNP file with ...

Payroll Report Info in 7.5
Our current 'Direct Deposit Statement of Earnings' report prints Available Sick Time on the report: 1. Which table and field is used for this data? 2. What is the best way to set all available sick time to zero for all employees and never accrue? Thanks UPR00100 is the table. You'll see fields for vacation and sick time available. The answer to resetting the balances depend upon if you are using HR Attendance to accrue or Payroll. If you're using Payroll, you can use a tool like SQL Server Query Analyzer to update the Sick Time Available field. If you're using At...

Account Balance Report bug?
I think what I'm experiencing is similar to the Net Worth report bug discussed here. I've discovered that the Account Balance report does not accurately reflect balances for previous days. Whatever date is picked in the report customization window, the balance is reported as the current days balance. Are these bugs related, and are there solutions? Anyone else experiencing this problem? In microsoft.public.money, David B wrote: >I think what I'm experiencing is similar to the Net Worth report bug >discussed here. I've discovered that the Account Balance report do...

sales reports
Is there a way to generate a report that shows detailed sales with multiple tender types? For example if a customer pays half with visa and half with a gift certificate. thx chris Chris, This can certainly be done, but because of the way the reporting engine works, sales may be double reported. Thank you, Ryan Sakry Program Manager rsakry@rite.us http://www.rite.us 320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax) On 7/15/2010 4:06 PM, christian wrote: > Is there a way to generate a report that shows detailed sales with multiple > tender types? > ...

reporting on drop-down values
We created drop-downs and wish to add the values to a report, but of course the extentionbase tables only show the numbers. Is there a specific table where all the actual text values are stored? Hi use the stringmaps table to get the description of picklist values. I hope it helps. -- Singh http://mscrmshop.blogspot.com MSCRM Consultant Orion Enterprise Sydney "TheWkndr" wrote: > We created drop-downs and wish to add the values to a report, but of course > the extentionbase tables only show the numbers. Is there a specific table > where all the actual text values a...

Determine if a report is REALLY printing
Good evening all! I have a problem that I cannot seem to find a way around. (Isn't that always the case here!) I want to be able to set a bit in my db if a record has run in a report and has actually printed. I have created the code that is marking the record as I desire, but it does if even if the page is only previewed! I was thinking (wrongly it seems now) that the on print event would only fire if the report was actually sent to the printer... But it seems that it fires when the report is written to the screen. Does anyone have any suggestions as to how I should acomplish this?...

Reporting Currency
Hi, We have setup two different currencies for Funactional and reporting. In the payroll module I have to enter values in functional currency(right?) Is there any way I can get the reports of Payroll transactions in reporting currency? Please help me Thanks Jacob ...

call report from form
I need call 1 personalize report from windows GP. In vba I do connect to the class where the object in this case the report that I created. What would be the class for this purpose I have an example of how to call GP reports from VBA. It uses an unsupported technique of calling Dexterity Sanscript from VBA, but works well. Please have your partner email me at the address below (remove the online. first) and let me know the version needed (v8, v9, v10). David Musgrave [MSFT] Senior Development Consultant Escalation Engineer - Great Plains Microsoft Dynamics Support - Asia Pacific Mic...