Help in inserting missing data in a table

[Smart_eng]

Hey hi
I really need a help in how to fix some records in my access table. It is a huge table more than 12k records!

In one of the field there are some data missing. The logic to reconstruct them is easy but I am not sure how to apply it in Access.

I have three columns one is the student ID, Year, term1 and term2

ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003
3311 2004 005 005


In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311

ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004 003 004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003 004 005
3311 2004 005 005


Any advice on how to do that automatically L
Thanks

 

[spikepl]

How many "faulty" records do you have? RUn a select query to see them. If don't know how do an access query tutorial.

 

[Smart_eng]

How many "faulty" records do you have? RUn a select query to see them. If don't know how do an access query tutorial.

Hi
A lot around 900 records which will be very time consuming to do them manually?

 

[spikepl]

Will it?

First do a select query, picking out all records to be updated, and the values to update them with. To pick up values from prior record, use Get value from another record form here http://allenbrowne.com/subquery-01.html

Once you got the above going, you can in the query designer change the query from SELECT to UPDATE by pressing the UPDATE Query button and fiulling in the information - play with it on a copy of the db,