Transposing Data Tables...Need Help!

eskelies · #1 March 27th, 2008, 03:29 PM

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.

Expand|Select|Wrap|Line Numbers

 
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 · #2 March 27th, 2008, 05:03 PM

Quote:

Originally Posted by 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.

Expand|Select|Wrap|Line Numbers

 
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 · #3 March 27th, 2008, 06:20 PM

Quote:

Originally Posted by 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.

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
Table1.Date ==> newtable.Date

Table1.AccountNumber ==> newtable.AccountNumber

Expand|Select|Wrap|Line Numbers

 
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 · #4 March 27th, 2008, 07:24 PM

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 · #5 March 28th, 2008, 12:34 AM

Quote:

Originally Posted by 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!

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 · #6 March 28th, 2008, 01:02 AM

Quote:

Originally Posted by ADezii

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 · #7 March 28th, 2008, 01:32 AM

Quote:

Originally Posted by eskelies

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 · #8 March 28th, 2008, 02:12 AM

Thanks man I appreciate it!

ADezii · #9 March 28th, 2008, 03:11 AM

Quote:

Originally Posted by eskelies

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:

Expand|Select|Wrap|Line Numbers

 
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 · #10 March 28th, 2008, 01:37 PM

AD,

The code works great!

Again I thank you!

ADezii · #11 March 28th, 2008, 04:58 PM

Quote:

Originally Posted by eskelies

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.

What is Bytes?

Popular Articles