Tough Recordset Calculation Question

[pvsmith5]

I need help on trying to do something.I have about 15 tables that are structured like this:

Year1 Year2 Year3 Year4 Forecast
Record 1 1 2 3 4
Record 2 5 6 7 8

I want to calculate the following formula, with the above number substituted:
[Year2(6)+ Year3(7) + Year4(8) / Year1(1)+ Year2(2)+ Year3(3)] * Year4(4)= Record2(Forecast)

Do you have any ideas on how to do this? Do I have to set a bookmark on Record1? I want to do the same type of calculation using Record2 and Record3 when it loops. Then I want to take the forecast numbers for each record and all 15 tables, and roll them up into one table.

Would I need to set an absolute record number to the counter, then reset it after each two records?

Would I need to write out the values to a separate table, then do the calculations from there?

Should I use a type of dynamic array?

HELP!!!!!!!

 

[MakeItSo]

Should I use a type of dynamic array?

You could do that, but there is no need to:

Assumed your records are in a table called "Table" whereas the "Record" ID is stored in e.g. "RecID" and you want to store the forecasts in a table called "Forecasts", containing fields called "ID, "RecordNo" and "Forecast":

Dim rs as DAO.Recordset, fc as DAO.recordset, BFore as Double, Aftr as Double, Quot as Double
Set rs=Currentdb.Openrecordset("Table", dbopensnapshot)
Set fc=Currentdb.Openrecordset("Forecasts", dbopendynaset)
rs.movefirst
Do while not rs.eof
 BFore=rs!Year1+rs!Year2+ rs!Year3+ rs!Year4
 Quot=rs!Year4
 rs.Movenext
 if not rs.eof then 
   Aftr=rs!Year1+rs!Year2+ rs!Year3+ rs!Year4
   fc.addnew
   fc!ID=DMax("ID", "Forecasts")+1
   fc!RecordNo=rs!RecID
   fc!Forecast=Aftr/Bfore*Quot
   fc.update
  endif
loop
rs.close
fc.close

You might need to play around with this code, and backup your db first! ;-)

Cheers,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[pvsmith5]

THANK YOU.
You made it look so easy.
I'll give this a try right now.

 

[Golom]

Three questions ...

[li]Are you attempting to do this with SQL or with VBA code?[/li]

[li]Do your records actually contain fields with "Record 1", "Record 2", etc.? If not, how do you determine the ordering of the records?[/li]

[li]Just as a rule-of-thumb, it's not generally a good idea to store computed results in tables. Do you require the table update or is a query-based solution acceptable?[/li]