Access report records (lines) sequentially

[DC CS]

Hi,


I have created an Access report where several lines are displayed. The report displays vehicles with their registration number and some other data. The report is bound to a query to retrieve the data.

What I want to achieve, is after the records are displayed in the report, I want to access each registration number on each line, select some data from another table and do some calculations and according to the result, display this calculated data on the report in the month field.

I tried including these calculations in a query, but there are so many calculations, I do not know how to incorporate the calculations in a query. For each month and each registration number, there are about 204 calculations based on criteria.

My report looks something like this:


Any assistance will be appreciated.

[Welshgasman]

You would use a form for that.

[Bulzie]

Are you saying for each Month box, it could run through 204 calculations to get the value it needs? Or based on the registration say, it goes and uses the calculation specific to that?

You might need to do these calculations in code and write to a separate table, then base the report off of that table. Just blank it out before it runs each time.

[Micron]

after the records are displayed in the report, I want to ... display this calculated data on the report in the month field.

Not possible. Once a report is generated, that's it. When February calculations are done, are the January ones done again? If so that's a monstrous amount of processing seeing as how there are more than 2400 for one registration for one year alone. The only way to avoid that is to store the calcs, which we usually advise not to do. I suspect that if there are 10 registrations and 204 per, it's still over 2K of calcs even if you store past data. I also think you'll need to use code, but not much idea of what that would look like at present.

[DC CS]

Thanks for all your input. I thought as much that it would not be possible in a report.
At this stage I think the best approach will be to do the calculations with code and store in a temp table and base the report on this table. Only issue I think, will be that the code for al these calculations will be messy. I will need to write quite a lot of lines to achieve this.

Not sure if there is a simpler way.

Regards

Thanks a mill.