[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Transpose Columns to Rows

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
faunnab
Asked:
faunnab
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
puppydogbuddyCommented:
see method 2 of this link:
         http://support.microsoft.com/kb/202176
0
 
kjetilroeCommented:

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Mark WillsTopic AdvisorCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
faunnabAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
faunnabAuthor Commented:
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
 
faunnabAuthor Commented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now