Solved

Transpose Columns to Rows

Posted on 2010-08-25
9
559 Views
Last Modified: 2012-05-10
I am transposing a table and I had it working, until I added my date fields. . .

Here is my original table (tblcurrentschedule)
WorkOrder_Base_ID   Sequence_No   Resource_ID   9/1/2010   9/2/2010  9/3/2010
wo-12345                   10                    200-415          18                        
wo-12345                   20                    225-395                        18          
wo-12345                   30                    200-415                                        5

Here is what I would like it to look like (tbloperationsched)
WorkOrder_Base_ID   Sequence_No   Resource_ID   Start_Date
wo-12345                   10                    200-415          9/1/2010                        
wo-12345                   20                    225-395          9/2/2010        
wo-12345                   30                    200-415          9/3/2010

My problem comes into play when I transpose the dates into one field called "Start_Date"

TIA,  Here is my code.  My loop is incorrect and the fldField value . .

Public Sub TransposeXtab()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rst As DAO.Recordset
'Dim i As Integer, xDate, sql As String
Dim fldField As DAO.Field
Set rs = CurrentDb.OpenRecordset("tblcurrentschedule")     '<<< change to name of table with crosstab data
Set rst = CurrentDb.OpenRecordset("tbloperationsched")     '<<< change to name of table
   
  For i = 3 To rs.Fields.Count - 1
       
       '***  this part use your conversion of week number to date
 
 '      xDate = CDate(Week2Date(rs(i).Start_date, Year(Date)))

       '********
 
        sql = "select * from tblcurrentschedule where reschedule = yes"
        Set rs1 = CurrentDb.OpenRecordset(sql)
        If Not rs1.EOF Then
        rs1.MoveFirst
            Do Until rs1.EOF
              For Each fldField In rs1tblCurrentSchedule.Fields
                 If Field.Value Like "## */*/####" Then 'Date Field
                   With rst
                     .AddNew
                     .Fields("workorder_base_id") = rs1("workorder")
                     .Fields("SEQUENCE_NO") = rs1("SEQUENCE_NO")
                     .Fields("RESOURCE_ID") = rs1("RESOURCE_ID")
                     .Fields("START_DATE") = Mid(fldField.Value, 3)
                     .Update
                   
                   End With
                End If
              Next
              rs1.MoveNext
            Loop
        End If
    Next

rs.Close
rst.Close
rs1.Close
End Sub



0
Comment
Question by:faunnab
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33530118
Why are you using Mid(fldField.Value, 3) when you find a date field? Does it have some prefix not shown above ? If not, then you are truncating to some invalid date and that is probably why it is not working.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 33530399
see method 2 of this link:
         http://support.microsoft.com/kb/202176
0
 
LVL 4

Expert Comment

by:kjetilroe
ID: 33530752

From my perspective, I cant see that it is the Value property you are supposed to use. Isn't it the FieldName Property you are supposed to use for this task, and check if the Value contain a number?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33531759
mwvisa1,

Well, it isn't quite a date field, more so the column name is a date and that column name needs to be posted as a date value into the new column "Start_Date", and incidentally is missing the value that did appear under the date column name...

So I think that faunnab is trying to write a new row for every date column, and only on the instance of having a date column.

It seems the format of the date column name is not as it appears in the example and exemplified by the test : Field.Value Like "## */*/####" Then 'Date Field
But it really should be fieldname...

If START_DATE is a real datetime type column, then there are a couple of potential problems and you need to first cast your string field into a date recognisable form... maybe use cdate(mid(field.value,3))

Of course, you could also write a crosstab query (noticing the Access tag), or if a finite number of columns, could write a union query and if you can express the data as a query, then either build your new table by doing an insert directly from the query, or, if not needing to convert the table, can leave it as a query and use that in your applications...

Check out : http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21180383.html slightly different but some relevant points about converting string values used for an insert (again for Access).



0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33531933
Good catch on field.value, Mark. And thanks for correction, I did mean the column name. My point was if it truly is "9/1/2010" as column name, then when converting that to value of the newly created start_date field I would think we want the whole field name and not just starting from character 3 which the mid is doing.

Possibly the issue is that the above is not September 1st but rather January 9th and this is an attempt to account for that.
0
 

Author Comment

by:faunnab
ID: 33533241
mwvisa1 -  I copied one of my old codes, that was doing the exact same transposing I wanted in this database . . . I will check to see if I can correct it.

mark_willis - yes, you are correct.  "9/1/2010" is the column header and I would like to add a row with this, and put it in field "start date"

Thanks you guys. . . I will look at the links and also see if I need to format 9/1/2010 as a date field (I think it already is, but I will double check.

This is relatively new to me, so I don't know a majority of the code I need.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 33535539
Dont forget it is field name not value you need to check...

Based on the column names being 9/1/2010, have a look at :

Sub UnPivot_Schedule()
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
   
    Set rst = CurrentDb.OpenRecordset("tblcurrentschedule")
    rst.MoveFirst
   
    Do Until rst.EOF
       For Each fld In rst.Fields
           If fld.Name Like "*/*/####" And fld.Value > 0 Then 'Date Field
              MsgBox rst.Fields("workorder_base_id") & " : " & rst.Fields("Sequence_No") & " : " & fld.Name & " : " & fld.Value
           End If
           Next fld
       rst.MoveNext
       Loop
   
    rst.Close
    Set rst = Nothing
    Set fld = Nothing
End Sub
0
 

Author Comment

by:faunnab
ID: 33535990
Thanks Mark.  Yes, the field name was the problem!

I have it working now, but I'm getting a record created for each date column.  But I just saw your post and that may fix my problem.  I will compare/test your code.

Here is what I have so far . .

Public Sub TransposeXtab()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rst As DAO.Recordset
'Dim i As Integer, xDate, sql As String
Dim fldField As DAO.Field
Set rs = CurrentDb.OpenRecordset("tblcurrentschedule")     'crosstab data
Set rst = CurrentDb.OpenRecordset("tbloperationsched")     'updated table
   
  For i = 3 To rs.Fields.Count - 1
 
        sql = "select * from tblcurrentschedule where reschedule = yes"
        Set rs1 = CurrentDb.OpenRecordset(sql)
        If Not rs1.EOF Then
            rs1.MoveFirst
            Do Until rs1.EOF
              For Each fldField In rs1.Fields
                 If fldField.Name Like "*/*/####" And fldField.Value > 0 Then 'Date Column/Field
                   With rst
                     .AddNew
                     .Fields("workorder_base_id") = rs1("workorder")
                     .Fields("SEQUENCE_NO") = rs1("SEQUENCE_NO")
                     .Fields("RESOURCE_ID") = rs1("RESOURCE_ID")
                     .Fields("DATE") = fldField.Name
                     .Update
                   End With
                End If
                Next
              rs1.MoveNext
           Loop
        End If
    Next
rs.Close
rst.Close
rs1.Close
End Sub
0
 

Author Comment

by:faunnab
ID: 33536683
I got it.  My first for loop was adding items.

Thanks for your help!!  All of you!!


Public Sub TransposeXtab()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rst As DAO.Recordset
'Dim i As Integer, xDate, sql As String
Dim fldField As DAO.Field
Set rs = CurrentDb.OpenRecordset("tblcurrentschedule")     'crosstab data
Set rst = CurrentDb.OpenRecordset("tbloperationsched")     'updated table
   
        sql = "select * from tblcurrentschedule where reschedule = yes"
        Set rs1 = CurrentDb.OpenRecordset(sql)
        If Not rs1.EOF Then
            rs1.MoveFirst
            Do Until rs1.EOF
              For Each fldField In rs1.Fields
                 If fldField.Name Like "*/*/####" And fldField.Value > 0 Then 'Date Column/Field
                   With rst
                     .AddNew
                     .Fields("workorder_base_id") = rs1("workorder")
                     .Fields("SEQUENCE_NO") = rs1("SEQUENCE_NO")
                     .Fields("RESOURCE_ID") = rs1("RESOURCE_ID")
                     .Fields("DATE") = fldField.Name
                     .Update
                   End With
                   rs1.MoveNext
                   Exit For
                End If
                Next
              Loop
        End If
rs.Close
rst.Close
rs1.Close
End Sub
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now