Please help very urgent to give ageing report

[pol]

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

 

[Arvin Meyer [MVP]]

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

http://accessmvp.com/Arvin/PartitionFunctionHelp.txt

 

[Daryl S]

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

 

[pol]

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

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

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

 

[Daryl S]

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

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

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

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