Copying rows of data to other rows within a table using vba

[sparkes84]

Hi,

I haven't a clue how to start; therefore any help would be appreciated (seriously!).

I've attached a basic example of what I'm trying to do.

So lost and confused!
sparkes84

 

[Galaxiom]

Not what you do.
The multiple records should be in another table.

Fields A, B and C should be in one table with another field as a Primary Key.

Field D should be in a different table with another field (the Foreign Key) to indicate which record from the first table it is related to by the matching Primary Key.

When you want to present data in the format you show, make a query that joins the two tables on the key.

 

[sparkes84]

Dear GalaxiomAtHome,
That's very kind of you, to reply to swiftly! The data that I have been supplied with is from a really terrible database which I have no control over, nor does it have any unique id which enables you to categorically state that the record belongs to that particular "study". So, if I split into two tables as you suggest, I wouldn't know which rows of column D matched columns A-C. Does that make sense?

Is there not a way of looking at a previous row's data and copying it into the current row and continuing until the next entry of data, and then starting again?

Thanks for your help,
sparkes84

 

[The_Doc_Man]

If the particular table is not indexed, or if the index is not marked as unique, or if the items you wanted to append multiple times were not participants in any unique indexes, it is possible to build a tightly selective query that would append new rows based on the fields of the selected records.

If any of the fields have a unique index, this is not possible.

There is also the question of whether proper normalization is being followed. I am not allowed to download databases so I can't look at what you posted.

The implication of being able to add non-indexed fields to another the record in the databse makes sense only for a couple of rare cases, but otherwise strongly resembles something that someone else would do because they didn't understand normalization.

The data that I have been supplied with is from a really terrible database which I have no control over

If this is a school assignment, tell your teacher (and you can copy my response verbatim), Crappy databases should NEVER be used to teach anyone anything except how to tell when it is time to delete the file and start over again.

If this is a work assignment, tell your boss (and you can copy my response verbatim), Crappy databases should NEVER be used for any real business that might depend on such poor designs. Cheap always shows up when you really don't want it to.

But then, of course, I'm known to have no patience with crappy databases.

 

[Endre]

This code will do what you require: However, it is imperative that the records in your database really are interspersed with the gaps as you suggest. This opens the table and cycles through each record. If the A column has a value, then store ABC and go to the next record. If A does not have a value, then write the last stored ABC values and update. Repeat through all records. (I have to answer this as my surname is Sparkes)

Function Redo_Data()
Dim rs As Recordset, str_A As String, str_B As String, str_C As String
Set rsf = CurrentDb.OpenRecordset("SELECT * FROM T1;")
Do While Not rsf.EOF
    If IsNull(rsf!A) Or rsf!A = "" Then
        rsf.Edit
        rsf!A = str_A
        rsf!B = str_B
        rsf!C = str_C
        rsf.Update
    Else
        str_A = rsf!A
        str_B = rsf!B
        str_C = rsf!C
    End If
    rsf.MoveNext
Loop
 
End Function