Help with code

[John K]

Hello,
I am new to access and I do understand visual basic a little bit. What I am trying to do is create a new table from one I have now in a database. The first field is the key several other fields and 4 that contain numerical data. What I want to do is add a copy of several of the fields to the same record. The data from record add to each record then use a formula that I already have to perform calculations and save that info in a new field at the end of each record. I want to start with record 1 and perform the caculations for each record in the table. Then take the numerical and other info from record 2 add it to the end of each of the records and continue this process for every record within the table.
Example.
Field 1 field 2 field 3 field 4 field 5 field 6 new feilds field 7 feild 8 field 9
key1 info 1 2 3 4 key1 info new value
key2 info 2 3 4 5 key1 info new value
key3 info 3 4 5 6 key1 info new value
key1 info 1 2 3 4 key2 info new value
key2 info 2 3 4 5 key2 info new value
key3 info 3 4 5 6 key2 info new value

Field 1 will now become a foreign key and I dont need to store the numerical data again but I do need fields 7 and 8 so I understand what values was used to create the value in field 9. I hope you can understand what I am trying to do.
Thanks,
John

 

[Rx_]

Only have time to provide a structure. Feel free to post your code for additional comments. Assuming that you don't have millions of records, a cursor is slower but might be your first solution to use code. This is just a framework to give you an idea.
Set rsComments = CurrentDb.OpenRecordset("YourTableName", 2, dbSeeChanges)
630 If Not rsComments.EOF And Not rsComments.BOF Then ' Not an empty table
640 rsComments.MoveFirst ' start at first record
660 Do While Not rsComments.EOF ' a loop to move the cursor
670 rsComments.Fields(1) = rsComments.Fields("Area").value
680 rsComments.Fields(2) = rsCommentsReport.Fields(1) * MyFormula1
690 rsComments.Fields(3) = rsCommentsReport.Fields(2) * MyFormula2
691 rsComments.Fields(4) = rsCommentsReport.Fields(3) * MyFormula3
rsComments.Fields(5) = MyCol4Saved ' the previous col 4 goes into this col 5
MyCol4Saved = rsComments.Fields(4)
MyCol5Saved = rsComments.Fields(5)

720 rsCommentsReport.Update
730 rsComments.MoveNext ' moves to next record
740 rsCommentsReport.AddNew ' either add a new record or move next on your existing data (comment this out)
750 Loop
760 End If

In a nutshell - create a recordset to the table
move the cursor to
Use the fields (field number) or fields (name) to read the value.
The returned value can stay the same or be changed with your formula.
The returned value can be stored in a variable to apply to the next record (move next)

The loop moves until the EOF (end of file) for the recordset is reached.
This is just some key features for you to look at. You will need to apply your specific implementation of code to this framework.