Please help very urgent to give ageing report

Please give me an sql to generate  the report from
statement table,

I have three column in my table
1. Month
2. year
3. Amount


 I want to show 
1. if the month is  current month it should be shown as ' Current'
2. If the previous month it should be shown as '30'
3 if before previous month it should  be '60'
4  if before '60' it should  be '90'
5. otherwise it should be '120'




Please help


0
Utf
3/29/2010 12:15:01 PM
access 16762 articles. 3 followers. Follow

4 Replies
735 Views

Similar Articles

[PageSpeed] 9

Have a look at the Partition() function. A good helpfile for it is at:

http://accessmvp.com/Arvin/PartitionFunctionHelp.txt
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"pol" <pol@discussions.microsoft.com> wrote in message 
news:B7717D0E-5ADD-4CE1-A9E5-A031EB314F7F@microsoft.com...
> Please give me an sql to generate  the report from
> statement table,
>
> I have three column in my table
> 1. Month
> 2. year
> 3. Amount
>
>
> I want to show
> 1. if the month is  current month it should be shown as ' Current'
> 2. If the previous month it should be shown as '30'
> 3 if before previous month it should  be '60'
> 4  if before '60' it should  be '90'
> 5. otherwise it should be '120'
>
>
>
>
> Please help
>
> 


0
Arvin
3/29/2010 1:08:11 PM
Pol -

You should not be using Month and Year as column names, as these are 
reserved words.  I have assumed they are really MyMonth and MyYear (change to 
your real column names).  Try this:

Select MyMonth, MyYear, amount, 
nz(Choose(DateDiff("m",DateSerial([MyYear],[MyMonth],1),DateSerial(Year(Date()),Month(Date()),1))+1," Current","30","60","90","120"),"120") AS Aging

-- 
Daryl S


"pol" wrote:

> Please give me an sql to generate  the report from
> statement table,
> 
> I have three column in my table
> 1. Month
> 2. year
> 3. Amount
> 
> 
>  I want to show 
> 1. if the month is  current month it should be shown as ' Current'
> 2. If the previous month it should be shown as '30'
> 3 if before previous month it should  be '60'
> 4  if before '60' it should  be '90'
> 5. otherwise it should be '120'
> 
> 
> 
> 
> Please help
> 
> 
0
Utf
3/29/2010 1:33:01 PM
If I have customer code how I can show 
as follows

customer code            current       30days    60day        90day     120day
sadsad                        10             20          30.50         80.26 
     30.40

Can you give one example sql for that . Pls very urgent
With many thanks

Pls help



"Daryl S" wrote:

> Pol -
> 
> You should not be using Month and Year as column names, as these are 
> reserved words.  I have assumed they are really MyMonth and MyYear (change to 
> your real column names).  Try this:
> 
> Select MyMonth, MyYear, amount, 
> nz(Choose(DateDiff("m",DateSerial([MyYear],[MyMonth],1),DateSerial(Year(Date()),Month(Date()),1))+1," Current","30","60","90","120"),"120") AS Aging
> 
> -- 
> Daryl S
> 
> 
> "pol" wrote:
> 
> > Please give me an sql to generate  the report from
> > statement table,
> > 
> > I have three column in my table
> > 1. Month
> > 2. year
> > 3. Amount
> > 
> > 
> >  I want to show 
> > 1. if the month is  current month it should be shown as ' Current'
> > 2. If the previous month it should be shown as '30'
> > 3 if before previous month it should  be '60'
> > 4  if before '60' it should  be '90'
> > 5. otherwise it should be '120'
> > 
> > 
> > 
> > 
> > Please help
> > 
> > 
0
Utf
3/29/2010 2:34:01 PM
Pol -

If you saved your first query as "AgingReport", then you can run a crosstab 
query on that query.  Use the crosstab query wizard, selecting the customer 
code for the row headings, and the 'Aging' column for the column headings, 
and the Amount column for the Value  (summed I assume).

If you get started and are stuck, post your SQL so we can help you tweak it.

-- 
Daryl S


"pol" wrote:

> If I have customer code how I can show 
> as follows
> 
> customer code            current       30days    60day        90day     120day
> sadsad                        10             20          30.50         80.26 
>      30.40
> 
> Can you give one example sql for that . Pls very urgent
> With many thanks
> 
> Pls help
> 
> 
> 
> "Daryl S" wrote:
> 
> > Pol -
> > 
> > You should not be using Month and Year as column names, as these are 
> > reserved words.  I have assumed they are really MyMonth and MyYear (change to 
> > your real column names).  Try this:
> > 
> > Select MyMonth, MyYear, amount, 
> > nz(Choose(DateDiff("m",DateSerial([MyYear],[MyMonth],1),DateSerial(Year(Date()),Month(Date()),1))+1," Current","30","60","90","120"),"120") AS Aging
> > 
> > -- 
> > Daryl S
> > 
> > 
> > "pol" wrote:
> > 
> > > Please give me an sql to generate  the report from
> > > statement table,
> > > 
> > > I have three column in my table
> > > 1. Month
> > > 2. year
> > > 3. Amount
> > > 
> > > 
> > >  I want to show 
> > > 1. if the month is  current month it should be shown as ' Current'
> > > 2. If the previous month it should be shown as '30'
> > > 3 if before previous month it should  be '60'
> > > 4  if before '60' it should  be '90'
> > > 5. otherwise it should be '120'
> > > 
> > > 
> > > 
> > > 
> > > Please help
> > > 
> > > 
0
Utf
3/29/2010 2:54:01 PM
Reply:

Similar Artilces:

CRM / SQL Reporting Co-Exist
We are trying to run Microsoft CRM and SQL Server Reporting on the same server. We have experiemented with several approaches but with no luck. Either SQL Server Reporting's Report Manager will not run with an error that session state in disenabled in ASP.Net or I get an error that there is a problem with the assembly Microsoft.CRM.Platform.Types. Does anyone know where I could find a document on how to make CRM and SQL Reporting co-exist on the same or server. It must be possible since many people run Small Business Server 2000 or 2003. Regards, George Vessels ...

Formula Help (to many expresions)
Could one of you give me a hand with this... I'm trying to put a formula in a spreadsheet that has too many expressions in it. I understand there is a limit to the number of equations that can be in a formula but there must be a way around the cap. Or maybe another way to write the formula? What I am trying to say in the formula is that if... If X is less than 09 then B1 = what's in cell C2 If X is less than 25 then B1 = what's in cell C3 If X is less than 51 then B1 = what's in cell C4 The expression I have written looks like this... =IF(X<10,"N/A",IF(X<...

Vendor summary report
Hi, This is my first post here. I am trying to generate a report in GP V8.0 that will allow me to identify local vendors (by zip?) and provide a summary total of how much we have purchased from them during different time periods. Thanks in advance for any help. -- rod Have you tried to create a smartlist under payables transactions? You can then export to excel and sort and subtotal any way you like. "mercfinance" wrote: > Hi, This is my first post here. I am trying to generate a report in GP V8.0 > that will allow me to identify local vendors (by zip?) and provid...

Can I see the summary report of each resouce's booking status ?
Here we using AutoAcceptAgent to booking resource account like meeting room. Is there any method to view the summary report ? for example, a summary report show which meeting room booked by which one for what agenda. I have tried to use public folder with email address, but can't register by AutoAcceptAgent. Any one has any idea of such request ? Pls help soon. Thanks. On Thu, 20 Jul 2006 19:14:02 -0700, Enid <Enid@discussions.microsoft.com> wrote: >Here we using AutoAcceptAgent to booking resource account like meeting room. >Is there any method to view the summary repor...

mscvr71.dll help
How do I make my VS.net 2003 software not depend on msvcr71.dll? Thanks. Use static linking. I don't know where to set the option in VS7, but it used to be under Code Generation where you selected the desired C runtime library to use. In VS6 we had a choice between a dynamically-linked runtime and a statically-linked runtime. I've not had to make this choice under VS7 so I'm not sure where, in their overly-clever and completely gratuitous reimplementation of the user interface, this has been hidden. joe On Sat, 21 Feb 2004 22:24:56 GMT, anonymous@coolgroups.com wrote: &g...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

reported error (0x80070057)
When I try to send an email to several addresses using Outlook 2003, I get the error: Task 'xxx - Sending' reported error (0x80070057) : 'Could not complete the operation. One or more parameter values are not valid.' I see no apparent problem with the addresses. Please tell me in simple English (well, as simple as possible) how to solve this problem. I am not an expert user. Thank you. What type of mail account is this? Exchange, POP3, IMAP? "lemonscented" <lemonscented@discussions.microsoft.com> wrote in message news:EC994AB1-B72B-4...

looking for cash drop and paid out reports
Does anyone have a report on these transactions that can be selected by date? Any help would be appreciated. Larry ...

SmartList Restrictions help
I built a SmartList that is based on the Year-to-Date Transaction Open file, and has the Account Master linked to it. I want to restrict it to accounts that begin with 36, 38, or 2504. I tried adding a restriction that says "Account Number:Account_Segment_Pool1 begins with 36 OR 38 OR 2504", but I got no results when I did it that way. I also set up 3 separate restrictions, but that didn't work either. Is this possible? I can't find much information about how to write restrictions in SL Builder. ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

help plz
my account has been inactive how to i reacctivate it? What account and what does this have to do with Outlook? "heather" <tracyliepke@yahoo.com> wrote in message news:066001c36c53$bb68d180$a501280a@phx.gbl... > my account has been inactive how to i reacctivate it? ...

Help with queries
Hi Guys, This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query. Any help will be highly appreciated!! Here is what I need... I have relatively small ms acces database with about 1000 I have 3 colums date ipaddress sitename 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 33.4.4.4 B 12/09 33.4.4.55 C What i need is if an ipadress is recorded more t...

How do I subscribe, please?
Hi! I know this is a lame question..... but how do I subscribe to this list, please? I use AOL as my ISP and yahoo as my e mail. Please reply to me at mhendersona2@yahoo.com. Then I'll subscribe and ask a *real* question....... Thanks! Mary Hi Mary, (posted with email copy) This is a public newsgroup, and in my opinion the word subscribe is a misnomer. The fact that you can read the newsgroup is what is meant by subscribed. There is no permission to be granted from an administrator in order to post, you posted and your posting will remain posted. You will see ...

Both sides of a report page
Is there a way to utilize both sides of a report page as if printing a book? On Wed, 9 Jan 2008 08:06:03 -0800, TRS <TRS@discussions.microsoft.com> wrote: >Is there a way to utilize both sides of a report page as if printing a book? Yes. But it is dependant on the printer your using, not on Access. Chuck -- ...

received/not invoiced report #5
Hello: All things being equal, is there any good reason as to why an item would not appear on the POP Received/Not Invoiced Report one afternoon but land on that report the next morning? The item should have been on the report that afternoon. The client says that the receipt for the item had been posted, prior to that report having been run that first time. She, also, says that the report option used both that afternoon and the next morning were the same. Could it just have been a glitch in the system that prevented the item from showing on the report that afternoon? Thanks! child...

Need help to choose loyalty program integrated with website
Hello. My name is Alex and I am working for franchise company using RMS system. We are looking for loyalty program integrated with web store. We have 12 franchisee stores using RMS and they are all conneted to our HQ system in main office. We want customers to earn point for each sales and redeem their points only at our website. (not on off-line store) Can anybody recommend best solution for our plan? Thank you. ...

Help And Advive For A Novice
HELP AND ADVICE FOR A NOVICE This Is Not A Question About Using Excell But How To Download E -books I Have Purchased 2 E-books (a)f1 Excell And (b) Financial Statements. Encountered Difficulty In Downloading Because The Instructions Were Not Very Clear For Novice And Message Flagged Up "zip File Corrupted" I Have Utilised My Permitted Downloads. Therfore I Have Not In A Position To Use The E-books -- Joe Williams ------------------------------------------------------------------------ Joe Williams's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23...

Pass infomration from a form to a report...
I have a form that displays a certain fields from a specific record. How can the selected values be passed to create a report? From my understanding reports can only get information from tables and queries. How can I query th information from a form? Thank you for any help, George On Fri, 26 Mar 2010 12:13:31 -0400, George <MyEmail@FakeEmailAddress.com> wrote: >I have a form that displays a certain fields from a specific record. How > can the selected values be passed to create a report? > From my understanding reports can only get information from ta...

formula help #42
What formula would I use to search down a column find a name and report the number in the next column, this would be multiple times, the numbers to be added together. The added number reported then to be multiplied by another number and then to be subtracted from another fixed number in a specific cell. Thanks in advance Jason You can sum the corresponding cells matched without having a dedicated column of numbers. =SUMIF(A1:A100,"Name",B1:B100) =(SUMIF(A1:A100,"Name",B1:B100)*AnotherNumber)-SpecificCell HTH, Paul -- "Boenerge" <Boenerge@discussions...

Sum of Top Values in Access Report
In an Access report, I'm presenting the top 15 cost values in the detail section. I have a counter to do start a new print page. That is working, but I also want to present the group total (=sum[netofreturns]) with the total of the top 15 values [top15only] and calculate the percentage of the top 15 to the group total (top 15/group total) in the group footer. ...

HELP! Balloon Payments in Debt Reduction
I have had this problem for so long it is getting really frustrating. When I put a debt into the debt reduction plan, it always puts a balloon payment on my first payment. Then, if I don't make that payment, it just builds and builds and builds with each subsequent payment I do make. How do I get rid of this? I removed the debt from my reduction plan the first time and that cleared it. But, the moment I started paying on the debt, it returned. Now, it's to the point that I can't get rid of it at all. When I remove the debt from the plan, it will do the same thing w...

SUMPRODUCT help #7
Hello: My question is if I have 3 or 4 criteria that need to be met before excel acknowledges the row and then once it does it will sum the accepted rows and return that sum into another sheet. Category Price Date Customer # InorgStock $30.00 10/20/2005101643 Inorg CB $415.80 1/23/2006 109597 InorgStock $15.00 1/23/2006 101643 Matrix Mod $50.00 1/23/2006 101643 If I want excel to only add up the extended price for the columns that met the criteria Customer Number=101643 and Category=inorgstock and Category=Inorg CB and Category=Matrix Mod and Document Date is >=10/20/05 but <...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...