query to manipulate data and create new table

[mitchem1]

For the sample table below,

Station Offset Slope
A 3 21
A 5 19
A 7 14
B 1 8
B 1.5 6
B 3 5
B 7 2

Would it be possible to use a query (or any other way) to create a new table out that looks like this?

A 3 21 5 19 7 14
B 1 8 1.5 6 3 5 7 2

In other words it alternates offset/slope/pffset/slope/etc. for the same station and then starts with the next station. THanks in advance.

 

[KenHigg]

Kind of like this?


kh

 

[mitchem1]

Ken,
That is exactly what I'm looking for. My table has a third column, so if I can figure out how to add that to the mix, I'll be in business (very little coding experience here). Thanks a lot!

 

[mitchem1]

Hi Ken,
I have been working with your code and think I'm getting close, but still not getting exactly what I need. For the sample table below:

station * offset * elevation
49330 * -0.125 * 570.94
49330 * -0.03 * 573.78
49330 * 10.305 * 573.17

I get:

station * data
49330 * -0.125 570.94
49330 * -0.03 573.78
49330 * 10.305 573.17

What I need is:

station * data
49330 * -0.125 570.94 -0.03 573.78 10.305 573.17

Is this possible? Thanks so much for all of your help.

 

[KenHigg]

Are there only three flds in the table?

kh

 

[mitchem1]

Yes, 3 in the original table. Currently, I have only 2 in the temp table as I want all of the data from offset and elevation fields in the same field.

 

[KenHigg]

Can you put this table with several hundred records in a seperate .mdb and post it?

kh

 

[mitchem1]

tblStationData is the original table. Thanks again

 

[KenHigg]

Public Sub s_runMe()
    Dim cn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
    
        
    rs1.Open "qrySortStation", cn, adOpenDynamic, adLockOptimistic
    rs2.Open "tblConvertedData", cn, adOpenDynamic, adLockOptimistic
    
    rs1.MoveFirst
    rs2.AddNew
    rs2![station] = rs1![station]
    rs2![Data] = CStr(rs1![offset]) & " " & CStr(rs1![elevation])
    rs1.MoveNext
       
        
        Do While Not rs1.EOF
        If CStr(rs1![station]) = CStr(rs2![station]) Then
            rs2![Data] = rs2![Data] & " " & CStr(rs1![offset]) & " " & CStr(rs1![elevation])
        Else
            rs2.AddNew
            rs2![station] = rs1![station]
            rs2![Data] = CStr(rs1![offset]) & " " & CStr(rs1![elevation])
        End If
                
        rs1.MoveNext

        Loop
        
    rs1.Close
    
    MsgBox "Done"
        
End Sub

 

[mitchem1]

That works perfect! Thanks a lot Ken.

 

[mitchem1]

Let me know if you have any questions. Thanks.

 

[KenHigg]

Mitch ask me to look at this and I don't see the problem. The problem is that it doesn't seem to make to the very last record in the first recordset:

Do Until rs1.EOF
        If CStr(rs1![station]) = CStr(rs2![station]) Then
            rs2![Data] = rs2![Data] & " " & CStr(rs1![offset]) & " " & CStr(rs1![elevation])
        Else
            rs2.AddNew
            rs2![station] = rs1![station]
            rs2![Data] = CStr(rs1![offset]) & " " & CStr(rs1![elevation])
        End If
                
        rs1.MoveNext

        Loop

Anyone see a problem?

kh

 

[KenHigg]

Mitch, Did you ever figure this out?

kh

 

[mitchem1]

Unfortunately, no I did not. Can't figure out why the one record is omitted. Were you able to run the program and see what I was talking about?

 

[KenHigg]

I've got a few minutes now. I'll take a look...

kh

 

[KenHigg]

Not that it really matters, but which one should I look at: the GRO or TEM?

kh

 

[KenHigg]

Never mind - The problem was I failed to place an update command in the code. See below:

Do While Not rs1.EOF
        If CStr(rs1![station]) = CStr(rs2![station]) Then
            rs2![Data] = rs2![Data] & " " & CStr(rs1![offset]) & " " & CStr(rs1![elevation])
        Else
            rs2.AddNew
            rs2![station] = rs1![station]
            rs2![Data] = CStr(rs1![offset]) & " " & CStr(rs1![elevation])
        End If
        
        rs2.Update
                
        rs1.MoveNext

Loop

kh

 

[mitchem1]

Thanks a lot Ken. That solved the problem. I really appreciate it.