Transposing Data Tables...Need Help!

[eskelies]

I have a sanitized database which I have attached, and for the most part transposes the data to my liking. There is however a few issues that I would like to fix, but cannot determine the problem in my code. Basically, the text field AccountNumber is being recognized as data, along with the account numbers and classes being populated incorrectly. If you run the attachment you will see my error. I appreciate anyones help in this matter. Thank you.

Function test()
Dim i As Integer, x As Integer
  Dim rs As Recordset, rs2 As Recordset
    Set rs = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
      Set rs2 = CurrentDb.OpenRecordset("newtable", dbOpenDynaset)
With rs
    .MoveLast
    .MoveFirst
  End With
With rs2
 
    x = 1
 
        Do Until rs.EOF
 
          Do Until x = rs.Fields.Count
            .AddNew
 
              !Date = rs.Fields(0)
              !AccountNumber = rs.Fields(x).Name
              !ClassNumber = rs.Fields(x)
              !Percent = rs.Fields(x)
            .Update
    x = x + 1
          Loop
      x = 1
            rs.MoveNext
        Loop
    rs.Close
  .Close
End With
Set rs = Nothing
  Set rs2 = Nothing
End Function
 

[ADezii]

I have a sanitized database which I have attached, and for the most part transposes the data to my liking. There is however a few issues that I would like to fix, but cannot determine the problem in my code. Basically, the text field AccountNumber is being recognized as data, along with the account numbers and classes being populated incorrectly. If you run the attachment you will see my error. I appreciate anyones help in this matter. Thank you.

Function test()
Dim i As Integer, x As Integer
  Dim rs As Recordset, rs2 As Recordset
    Set rs = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
      Set rs2 = CurrentDb.OpenRecordset("newtable", dbOpenDynaset)
With rs
    .MoveLast
    .MoveFirst
  End With
With rs2
 
    x = 1
 
        Do Until rs.EOF
 
          Do Until x = rs.Fields.Count
            .AddNew
 
              !Date = rs.Fields(0)
              !AccountNumber = rs.Fields(x).Name
              !ClassNumber = rs.Fields(x)
              !Percent = rs.Fields(x)
            .Update
    x = x + 1
          Loop
      x = 1
            rs.MoveNext
        Loop
    rs.Close
  .Close
End With
Set rs = Nothing
  Set rs2 = Nothing
End Function
 

Just subscribing, will return later.

[ADezii]

I have a sanitized database which I have attached, and for the most part transposes the data to my liking. There is however a few issues that I would like to fix, but cannot determine the problem in my code. Basically, the text field AccountNumber is being recognized as data, along with the account numbers and classes being populated incorrectly. If you run the attachment you will see my error. I appreciate anyones help in this matter. Thank you.

Function test()
Dim i As Integer, x As Integer
  Dim rs As Recordset, rs2 As Recordset
    Set rs = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
      Set rs2 = CurrentDb.OpenRecordset("newtable", dbOpenDynaset)
With rs
    .MoveLast
    .MoveFirst
  End With
With rs2
 
    x = 1
 
        Do Until rs.EOF
 
          Do Until x = rs.Fields.Count
            .AddNew
 
              !Date = rs.Fields(0)
              !AccountNumber = rs.Fields(x).Name
              !ClassNumber = rs.Fields(x)
              !Percent = rs.Fields(x)
            .Update
    x = x + 1
          Loop
      x = 1
            rs.MoveNext
        Loop
    rs.Close
  .Close
End With
Set rs = Nothing
  Set rs2 = Nothing
End Function
 

Sorry eskelies, but you are going to have to fill me in as far as Field Alignments go:

Table1.Date ==> newtable.Date
Table1.AccountNumber ==> newtable.AccountNumber

Table1.1 ==> newtable.?
Table1.2 ==> newtable.?
Table1.3 ==> newtable.?
Table1.7 ==> newtable.?
Table1.8 ==> newtable.?
Table1.9 ==> newtable.?

How does newtable.ClassNumber and newtable.Percent fit into the overall picture?

[eskelies]

AD,

I hope I understand your question. Each fund number should have multiple classes (ie. 1,2,3,7,8,9) to be exact. Each Class will have a percent associated with that. As far as field date is concerned I am going to ultimately have two tables. One for current date and the other prior date. I will need these dates because I will be building queries off these.

Hope this helps!

[ADezii]

AD,

I hope I understand your question. Each fund number should have multiple classes (ie. 1,2,3,7,8,9) to be exact. Each Class will have a percent associated with that. As far as field date is concerned I am going to ultimately have two tables. One for current date and the other prior date. I will need these dates because I will be building queries off these.

Hope this helps!

For the sake of absolute clarity, kindly post the Field data from a single Record in Table1, then a display of how the Transposed data will appear in newtable along with the Field assignments. I understand that a given Account Number has 6 Classes (1, 2, 3, 7, 8, and 9) associated with it but how is the Percent Field derived from the values in Table1, then transposed to newtable?

[eskelies]

For the sake of absolute clarity, kindly post the Field data from a single Record in Table1, then a display of how the Transposed data will appear in newtable along with the Field assignments. I understand that a given Account Number has 6 Classes (1, 2, 3, 7, 8, and 9) associated with it but how is the Percent Field derived from the values in Table1, then transposed to newtable?

Table1:

Date AccountNum 1 2 3 7 8 9

12/31/9999 20 3% 3.01% 3% 3.17% 3.12%


New Table:

Date AccountNum Class Num Percent


12/31/9999 20 1 3%

12/31/9999 20 2 3.01%

12/31/9999 20 3 3%

12/31/9999 20 7

12/31/9999 20 8 3.17%

12/31/9999 20 9 3.12%


Note: If Class has null value leave null.

[ADezii]

Table1:

Date AccountNum 1 2 3 7 8 9

12/31/9999 20 3% 3.01% 3% 3.17% 3.12%


New Table:

Date AccountNum Class Num Percent


12/31/9999 20 1 3%

12/31/9999 20 2 3.01%

12/31/9999 20 3 3%

12/31/9999 20 7

12/31/9999 20 8 3.17%

12/31/9999 20 9 3.12%


Note: If Class has null value leave null.

Gotcha, I'll try and have the code for you tomorrow.

[eskelies]

Thanks man I appreciate it!

[ADezii]

Thanks man I appreciate it!

Actually, once I got an exact picture of what had to be done, it was fairly simple. Replace all your Function code (Test()) with the following:

Dim MyDB As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim intCounter As Integer
 
Set rs = CurrentDb.OpenRecordset("table1", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("newtable", dbOpenDynaset)
 
Do While Not rs.EOF
  With rs2
    For intCounter = 2 To 7
      .AddNew
        !Date = rs.Fields(0)
        ![AccountNUmber] = rs.Fields(1)
        ![ClassNumber] = rs.Fields(intCounter).Name
        ![Percent] = rs.Fields(intCounter)
      .Update
    Next
  End With
  rs.MoveNext
Loop
 
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing

[eskelies]

AD,

The code works great!

Again I thank you!

[ADezii]

AD,

The code works great!

Again I thank you!

You are quite welcome. What some Members do no realize is that, at least for me, it is sometimes very difficult to solve problems from the other end of a Web Page. Once you posted the data both from before, and after, the Transposition, the solution was quite clear.