Data switching

[aagustin]

Hi.

I'm in need of some advice. I have a table with multiple fields and I need to take on column's data and rearrange them. Hard to explain but here's what I want to do:

Current:
ID Initial Field1 Field2
1 A Y N
2 B N N
3 C N N
4 D Y Y
5 E N Y

Switched to:

ID Initial Field1 Field2
1 E Y N
2 D N N
3 C N N
4 B Y Y
5 A N Y


So, I need to take the value of the Initial field and switch the top value to the bottom one and vice-versa, without impacting any of the other fields in that row. Anyone know how?

Thanks!

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm ...

CONCEPTUALLY,

Lock the BD for Exclusive use.

Create an Array. Fill the array with the ORDERED content of the field. This is probably a loop through the recordset, but it COULD be a query of some type.

Do a LOOP. In the recordset, You are already at EOF, so each record is accessed by a move previous. Update the record with the value in the corresponding (reverse order element of the array.

Hmmmmmmmmmmm ^ Hmmmmmmmmmmm

To easy.

Function RevColOrder()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim ColArray() As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("MyTbl", dbOpenDynaset)

    ReDim ColArray(0)       'Initalize Array bounds

    'Get Col Contents in array
    Do While Not rst.EOF
        ColArray(Idx) = rst!Initial
        Idx = Idx + 1
        ReDim Preserve ColArray(Idx)
        rst.MoveNext
    Loop
    ReDim Preserve ColArray(Idx - 1)    'ReSet array to remove last


    Idx = 0
    rst.MovePrevious
    Do While Not rst.BOF
        rst.Edit
            rst!Initial = ColArray(Idx)
            Idx = Idx + 1
        rst.Update
        rst.MovePrevious
    Loop

End Function

But be CAREFUL! This is NOT a reccomended processs/ procedure. There is NO error checking ... Other safwwty here.

At a minimum, make a copy of your orig table. PLEASE lock the db for exclusive use. Adding records in the midst of this WILL create havoc.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over