Please help very urgent to give ageing report

  • Thread starter Thread starter pol
  • Start date Start date
P

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top