I need help to create this report...please 1

[paulmak]

I have a recordset with 5 different 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 starting from the first year as follows:
Because the first year on two programs 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 different from all other years funding The formula to calculate the year 1 funding is the Program Type(B+C+D) - Program Type A.
And the remaining year's funding calculation is the Program Type(B+E) - Program Type A.

I am thinking the way to do this is first to write a "For....Loops" to group/seperate the different programs 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. But if you can find a better way to achieve the end result, just give it a shot.


Thanks.

Paul

 

[mp9]

Can't you just use the crosstab query wizard to easily build a query to model what you need, then use a report wizard to create a report based on your crosstab query.

The resulting format might not be perfect but it would save you a lot of work. Plus, if you didb't want to keep the crosstab query you could just cut and paste the SQL into the report's record source.

HTH

http://www22.brinkster.com/accessory/