I need help on a "For...Loops"..I think....
Author Message I need help on a "For...Loops"..I think....

I have a recordset with 5 diff fields, ID (Text), Program (Text), Program Type (Text), Year (Number) and Amount (Currency). For example:

ID       Program         Program Type         Year             Amount
1          Maths             Type A                 2002              1000
1          Maths             Type A                 2003              1000
1          Maths             Type A                 2004              1000
1          Maths             Type B                 2002              1200
1          Maths             Type B                 2003              1200
1          Maths             Type B                 2004              1200
1          Maths             Type C                 2002                800
1          Maths             Type D                 2003                500
1          Maths             Type E                 2003              1500
2        Physics            Type A                 2004              2500
2        Physics            Type A                 2005              2500
2        Physics            Type A                 2006              2500
2        Physics            Type B                 2004              3000
2        Physics            Type B                 2005              4000
2        Physics            Type B                 2006              2000
2        Physics            Type C                 2004              1800
2        Physics            Type D                 2004              1300

What I need is to use this data to build a report so that it will show the Amoount for each program start from first year as follows:
Because the first year on two program can be different so that it uses Year 1 , Year 2 and so on
Program      Year 1      Year 2...........and so on
Maths           \$\$             \$\$
Physics        \$\$             \$\$

Funding on Year one is differnet from all other years funding The formula to calculate the year 1 funding is Program Type(B+C+D) - Program Type A.
And the remaining years calculation is Program Type(B+E) - Program Type A.

I am thinking the way to do this is first to write a "For....Loops" to group the different program i.e. Maths and Physics. Then write another "For....Loops" within the first one to determine the "Year 1", "Year 2" and so on, then calculate the amount for each year.

My problem is I am not sure how to write thh two "For...Loops" and how to use code to determine what the Program Type and add the amount based on the formula. Please help. Thanks.

Any suggestion would be appreciated.

Paul

Fri, 29 Apr 2005 02:01:44 GMT

 Page 1 of 1 [ 1 post ]

Relevant Pages