Copying data from record to record

[jimbart]

I have a similar problem: in some records of a database, an "empty" field indicates that it should contain the data in the immediately prior record. For example, if Jones is paid for tasks 001, 002 and 003, the first Jones record (task 001) will contain all data (name, ID number, etc), the subsequent records only the data that changes (task, amounts, etc). I need a Macro (or Query?) that says "If this part of the record is blank, and this part is not, copy the data from the record above it."
My "programming" skills are minimal and limited to "Very Good" in 1-2-3 and "Fair" in FoxPro

 

[Travis]

What you are asking could be alot of trouble in the long run.

The best way to handle this arrangement would be to make two tables out of this.

The first:

ID, Name, etc.

The second:

TaskID, ID (from first table), etc.


This set up will reduce the multiple data entries.

The only way I can see to possible do what you want cannot be done with a simple query but would require utilizing code.

Public Sub UpdateMyData()
Dim rst as recordset
Set rst1 = Currentdb.OpenRecordset ("Select * From tblMyTable") 'Writing here
Set rst2 = Currentdb.OpenRecordset ("Select * From tblMyTable",dbOpenSnapShot)'Reading here
rst1.MoveFirst
rst2.MoveFirst
Do While Not rst.EOF
If rst1.BOF then rst.MoveNext
if nz(rst1!Field1,"")="" then rst1!Field1 = rst2!Field1
if nz(rst1!Field2,"")="" then rst1!Field2 = rst2!Field2
if nz(rst1!Field3,"")="" then rst1!Field3 = rst2!Field3
rst1.movenext
rst2.movenext
loop
end sub

You will have to add an if for each field you want to update when the record is empty.

 

[jimbart]

Thanks, Travis ... this may be a starting point. A problem may occur because several persons can code to the same task. Jones may code to tasks 001, 002 & 003, but Smith may code to tasks 002, 003, 005 & 007, or to the same tasks as Jones.
Beginning to think the way to do it is export to Excel, manipulate there & then import & go on ... Must be my spreadsheet brain trying to do database functinons!