Generating Records of all date

[Guest]

Dear MVPs,

I have one Query as below,

Query1:

EquID StartuseDay EnduseDay TotalMonths MonthDepreciation
E0001 2002-6-15 2007-6-14 60 $10,000

it is an examples of one record,

i need to generating another query show below datas base on this record,

EquID Dates MonthDepreciation
E0001 2002-6-14 $10,000
E0001 2002-7-14 $10,000
E0001 2002-8-14 $10,000
.....
E0001 2007-6-15 $10,000

the records date is one month add one by one till 60 months,
Waiting for your kind response!

 

[John Spencer]

You need another table. That table should have one column of numbers (1 to
60 or whatever the maximum number of months might be).

Now you can use that table and your original query with no join.

SELECT EquiID
, DateAdd("m",1-NumbersTable.NumberColumn,EndUseDay) as Dates
, MonthDepreciation
FROM [Your Original Query] as A, NumbersTable
WHERE NumbersTable.NumberColumn <=A.TotalMonths
ORDER BY DateAdd("m",1-NumbersTable.NumberColumn,EndUseDay)

 

[Guest]

Oh, Wonderfully, i achieved what i want,
Thank yo so much, John,

--
Best Regards
Xuehong

You need another table. That table should have one column of numbers (1 to
60 or whatever the maximum number of months might be).

Now you can use that table and your original query with no join.

SELECT EquiID
, DateAdd("m",1-NumbersTable.NumberColumn,EndUseDay) as Dates
, MonthDepreciation
FROM [Your Original Query] as A, NumbersTable
WHERE NumbersTable.NumberColumn <=A.TotalMonths
ORDER BY DateAdd("m",1-NumbersTable.NumberColumn,EndUseDay)