Update Record 1 with a value on Record 2

[AccNovice]

Let me first start by apologizing for this vague question. Unfortunately I am so ignorant that I do not even know what to ask for. I tried to look for the answer but each time I read an article I get even more confused. I am trying to do the following but have no idea where to start:

I have a table with the following records =>

FieldA FieldB FieldC Record
890 093437 20081118 1
890 094333 20081118 2
890 105633 20100624 3
900 144039 20091207 4
900 145624 20091207 5
900 163412 20090519 6

I want to populate a table using the above mentioned records and it should look like this =>

FieldA FieldB FieldC FieldD FieldE
890 093437 20081118 094333 (FieldB of Record 2) 20081118 (FieldC of Record 2)
890 094333 20081118 105633 (FieldB of Record 3) 20100624 (FieldC of Record 3)
890 105633 20100624 000000 (Fixed value) 20100728 (Today’s Date)
900 144039 20091207 145624 (FieldB of Record 5) 20091207 (FieldC of Record 5)
900 145624 20091207 163412 (FieldB of Record 6) 20090519 (FieldC of Record 6)
900 163412 20090519 000000 (Fixed value) 20100728 (Today’s Date)

How do I achieve this? I would really appreciate it if somebody can steer me in the right direction.

 

[jzwp22]

First of all, you should not populate a table with data you already have in a table whether it is in the current record or another related record. You can use a query with nested queries to get what you need. Once you have the data all in one place, what do you plan to do with it?

But before we can move futher, I have to know more about fields B and C. Field C appears to be a date expressed in as an integer, so I would assume that the datatype of the field is a long number, correct. Field B appears to be a number but the leading zero would indicate that the datatype of the field is text. Is that correct? I assume that the data is in order by Field C and then Field B, so for record 1 you want to grab the two fields from the next record with field A of the current record and field A of the next record to select is the same and that field C of the record to select is >= field C of the current record and field B of the record to select is > field C of the current record. If field A of the current record <> field of the next sequential record display 000000 for field B and the current date for field C. At some point, I assume that for the record with 000000 for field B and the current date for field C will eventually be changed?