Query to re-arrange table contents

[fboehlandt]

Hi everyone,
I have a table in my access database that looks as follows:

Fname Fcode Date1 Date2 Date3 Date4 ...... DateN
FundA 10001 30000 20000 12000 14000 ...... xxxxx
FundB 10002 40000 50000 60000 70000 ...... xxxxx
FundC 10003 99999 88888 77777 66666 ...... xxxxx
.......
FundXYZ

The table contains the name of an investment fund and the code of the fund as primary key. In addition, the table contains the fund performance for (200) different dates sorted into columns. I would like to use a query to change the table as follows:

FundA 10001 Date1 30000
FundA 10001 Date2 20000
........ ........ ........ ........
FundA 10001 DateN xxxxx
FundB 10001 Date1 40000
FundB 10001 Date2 50000
........ ........ ........ ........
FundB 10001 DateN xxxxx
etc.

Can this be done? Any help is appreciated
thanx

 

[vbaInet]

Your desired output is how your table should have been in the first place. The original table isn't normalised. How many columns are there in this table and how many tables have you got in your db?

 

[G81]

Can you change the table structure? If so, you might want to look at changing it. Is the number (N) in DateN relevant or do you just add the next date into the next available field? If so, you could have a separate table with:

FID | FDate

and change your current one to:

FID (Autonumber) | Fname | Fcode

Then you can link the FID in your query, then order by Fname and FDate

 

[namliam]

A union query will do that for you...

Select .... from yourtable
Union all
Select .... from yourtable
Union all
Select .... from yourtable

You can repeat this as much as you like

 

[vbaInet]

He's actually looking to transpose the table as opposed to merging them.

 

[fboehlandt]

@vbaINet
you are absolutely right, of course, but I'm stuck with the format of the table as is (because I didn't create it). There is a total of six tables. This particular table contains 200 columns (198 dates + Name col + Code ID col). I would like to revert to the (normalized?) table so I can run my relevant query from it.

@G81
N is not the same for all funds (i.e. funds don't report throughout the entire period of 198 months). That should not matter though. I can create extra tables (FID | FDate) but only as a last resort. I shall try a query first...

@namliam
not very sql proficient, I'm afraid, but I do understand Union queries. However, can you be a bit more specific (don't know what Union all does)?
Is this what you had in mind:

Select mytable.Fname as Fname, mytable.Fcode as Fcode, mytable.Date1 as Date1
From mytable
Union all
Select mytable.Fname as Fname, mytable.Fcode as Fcode, mytable.Date2 as Date1
From mytable
Union all
Select mytable.Fname as Fname, mytable.Fcode as Fcode, mytable.Date2 as Date1
From mytable
Union all
...

I think this might work. Will get back to you after testing.

Thanks guys for the quick input. Always a reliable forum community

 

[fboehlandt]

@vbaInet
not quite, I would like to stack the table (reduce it to three columns). The remaining columns are Fname, Fcode, ADate. There are several dates for one Fname/Fcode. Thus the relationship is n : 1

 

[fboehlandt]

Select mytable.Fund_Name as Fname, mytable.Fund_ID as Fcode, mytable.Return_06_2010 as ROI, '01/06/2010' As [Date]
From mytable
Union all
Select mytable.Fund_Name as Fname,mytable.Fund_ID as Fcode, mytable.Return_05_2010 as ROI, '01/05/2010' As [Date]
From mytable
etc.

this seems to work. Although the final query will contain roughl y 200 union statements. Is this a problem?

 

[vbaInet]

Are you conversant with the Tranpose feature in Excel? It may do what you need without needing code. Worth checking out.

Here's some code (untested):

    Dim db As DAO.Database, rstOld As DAO.Recordset, rstNew As DAO.Recordset, i As Integer
    
    Set db = CurrentDb
    Set rstOld = db.OpenRecordset("[COLOR=Blue][B]Name Of unnomarlised table"[/B][/COLOR])
    Set rstNew = db.OpenRecordset("[COLOR=Blue][B]Name of new table[/B][/COLOR]")
    
    With rstOld
        .MoveFirst
        Do While Not .EOF
            For i = [COLOR=Red][B]2[/B][/COLOR] To [COLOR=Red][B]N[/B][/COLOR]
                rstNew.AddNew
                    rstNew![[COLOR=Blue][B]FName[/B][/COLOR]] = ![[COLOR=Blue][B]FName[/B][/COLOR]]
                    rstNew![[COLOR=Blue][B]FCode[/B][/COLOR]] = ![[COLOR=Blue][B]FCode[/B][/COLOR]]
                    rstNew![[COLOR=Blue][B]FDate[/B][/COLOR]] = .Fields(i).Name
                    rstNew![[COLOR=Blue][B]FDateValue[/B][/COLOR]] = .Fields(i).value
                rstNew.Update
            Next
            .MoveNext
        Loop
    End With
    
    MsgBox "Done!"
    
    rstold.close
    rstnew.close
    set rstold = nothing
    set rstnew = nothing

I've highlighted the bits you need to pay attention to. In the For loop, the number 2 stands for the third column so when you substitute N bear in mind that the columns start from 0.

 

[vbaInet]

Select mytable.Fund_Name as Fname, mytable.Fund_ID as Fcode, mytable.Return_06_2010 as ROI, '01/06/2010' As [Date]
From mytable
Union all
Select mytable.Fund_Name as Fname,mytable.Fund_ID as Fcode, mytable.Return_05_2010 as ROI, '01/05/2010' As [Date]
From mytable
etc.

this seems to work. Although the final query will contain roughl y 200 union statements. Is this a problem?

Union queries are not updateable. I would only use them in reports or forms used for display purposes only.

 

[fboehlandt]

@vbaInet
seems you were right. The query proved to be a dead-end. I'm now willing to give the macro a run. I use VBA quite extensively in Excel but need some pointers for Access. I have created a table called 'mytable' and copied your code into a sub procedure. To test the macro I have deactivated the loop for now. This should just reproduce the first two columns of 'mytable_old' in 'mytable' (assuming .EOF is end of field?). Instead it juist gives the error message 'item not found'. Ps check what I'm doing wrong here:

Sub Normalize()
    Dim db As DAO.Database, rstOld As DAO.Recordset, rstNew As DAO.Recordset, i As Integer
    
    Set db = CurrentDb
    Set rstOld = db.OpenRecordset("mytable_old")
    Set rstNew = db.OpenRecordset("mytable")
    
    With rstOld
        .MoveFirst
        Do While Not .EOF
            'For i = 2 To N
                rstNew.AddNew
                    rstNew![FName] = ![FName]
                    rstNew![FCode] = ![FCode]
                    'rstNew![FDate] = .Fields(i).Name
                    'rstNew![FDateValue] = .Fields(i).value
                rstNew.Update
            'Next i
            .MoveNext
        Loop
    End With
    
    MsgBox "Done!"
    
    rstOld.Close
    rstNew.Close
    Set rstOld = Nothing
    Set rstNew = Nothing
End Sub

 

[namliam]

Select mytable.Fund_Name as Fname, mytable.Fund_ID as Fcode, mytable.Return_06_2010 as ROI, '01/06/2010' As [Date]
From mytable
Union all
Select mytable.Fund_Name as Fname,mytable.Fund_ID as Fcode, mytable.Return_05_2010 as ROI, '01/05/2010' As [Date]
From mytable
etc.

this seems to work. Although the final query will contain roughl y 200 union statements. Is this a problem?

That shouldnt be a problem, only problem as VBA indicated, its not updatable.

Now you can build the 200 queries in excel and copy them down or if you just want to "copy" the data into one table... you can do some smart coding in VBA.

That smart coding would be something along the lines of:

Sub Aircode()
    Dim x As Integer
    Dim MySQL As String
    Dim FieldName As String
    ' Change the 0 to how ever many columns you want to skip
    ' Keep in mind that 0 is actually the first column
    For x = 0 To CurrentDb.TableDefs("mytable").Fields.Count - 1
        FieldName = CurrentDb.TableDefs("mytable").Fields(x).Name
        MySQL = ""
        MySQL = MySQL & " Select Fund_Name as Fname "
        MySQL = MySQL & "      , Fund_ID as Fcode "
        MySQL = MySQL & "      , " & FieldName & " as ROI "
        MySQL = MySQL & "      , DateSerial( " & Right(FieldName, 4) & ","
        MySQL = MySQL & "                    " & Mid(FieldName, 8, 2) & ", 1) As [ValueDate]" ' Dont use DATE as a field name it is a reserved word.
        MySQL = MySQL & " From mytable "
        Debug.Print MySQL
    Next x

End Sub

Now you can expand on the SQL as much you like and even change it to a Insert into statement to feed your target table.
Instead of the Debug.print you then want:
Currentdb.execute MySQL

Another alternative would be to write the complete union this way like:

Sub Aircode()
    Dim x As Integer
    Dim MySQL As String
    Dim FieldName As String
    ' Change the 0 to how ever many columns you want to skip
    ' Keep in mind that 0 is actually the first column
    MySQL = ""
    For x = 0 To CurrentDb.TableDefs("mytable").Fields.Count - 1
        FieldName = CurrentDb.TableDefs("mytable").Fields(x).Name
        MySQL = MySQL & " Select Fund_Name as Fname "
        MySQL = MySQL & "      , Fund_ID as Fcode "
        MySQL = MySQL & "      , " & FieldName & " as ROI "
        MySQL = MySQL & "      , DateSerial( " & Right(FieldName, 4) & ","
        MySQL = MySQL & "                    " & Mid(FieldName, 8, 2) & ", 1) As [ValueDate]" ' Dont use DATE as a field name it is a reserved word.
        MySQL = MySQL & " From mytable " & vbNewLine
        If x < CurrentDb.TableDefs("mytable").Fields.Count - 1 Then
            MySQL = MySQL & " UNION ALL " & vbNewLine
        End If
    Next x
        
    Debug.Print MySQL

End Sub

 

[fboehlandt]

@vbaInet
sorry, there was a typo. All sorted now, the macro works fine and is reasonably fast. Many thanks

@namliam
I must admit I did not consider circumvening the UNION statements when building the command text query. Let me play around with your code a little bit to see if it works for me. I think you might have pointed me in the right direction.
With respect to the manual query using 200+ UNION statements, that yields 'query to complex'' at some point. I don't intend to pursue that course of action, however (simply takes too long)...

 

[vbaInet]

@vbaInet
sorry, there was a typo. All sorted now, the macro works fine and is reasonably fast. Many thanks

Ah, I didn't catch your previous post until now.

Glad that's working for you.

 

[fboehlandt]

@vbaInet
Hi there, I was just wondering if you could help me out again with the following request. In my now normalized table there are still fields that contain no values. More specifically, dates that have no performance enry (i.e. the fund did not report for that particular month). I shoud think theses entries ought to be removed from the normalized table?! Her is what I had in mind:

Sub Normalize()
    Dim db As DAO.Database, rstOld As DAO.Recordset, rstNew As DAO.Recordset, i As Integer
    
    Set db = CurrentDb
    Set rstOld = db.OpenRecordset("mytable_old")
    Set rstNew = db.OpenRecordset("mytable")
    
    With rstOld
        .MoveFirst
        Do While Not .EOF
             For i = 2 To N
                rstNew.AddNew
                If .Fields(i).value = "" Then Goto nexti
                    rstNew![FName] = ![FName]
                    rstNew![FCode] = ![FCode]
                    'rstNew![FDate] = .Fields(i).Name
                    'rstNew![FDateValue] = .Fields(i).value
                rstNew.Update
nexti:
             Next i
            .MoveNext
        Loop
    End With
    
    MsgBox "Done!"
    
    rstOld.Close
    rstNew.Close
    Set rstOld = Nothing
    Set rstNew = Nothing
End Sub

However, this does not appear to remove any entries form the new table. One possible reason is that the above test is inappropriate to test for zero-length strings. Any ideas?

 

[Galaxiom]

Test for Nulls. These are different from the ZLS. Use the IsNull function.

Often developers test for both.

If (fieldname & "") = ""

 

[fboehlandt]

Thanks, ISNULL does indeed work

 

[namliam]

Word to the wize, avoid at all cost GOTO constructions.... They are nightmares and obsolete 1980's constructions that tend to cause "spaghetti coding"

                rstNew.AddNew
                If nz(.Fields(i).value, "") = "" Then 
                    rstNew![FName] = ![FName]
                    rstNew![FCode] = ![FCode]
                    'rstNew![FDate] = .Fields(i).Name
                    'rstNew![FDateValue] = .Fields(i).value
                    rstNew.Update
                End If
             Next i

I still believe a set of insert queries here would be faster, but if it is what it is and it works as it works then if it aint broke dont fix it.

 

[fboehlandt]

The key word here is a set of queries. In the OLE DB command text of the data import into Excel there can only be one query. I did actually use the If-else-end if construct rather then GoTo but used Goto intially just to test the macro. Alas, in the context of error handling an trapping I still prefer GoTo

 

[namliam]

Alas, in the context of error handling an trapping I still prefer GoTo

The one and only exception, no error trapping without atleast one GOTO ...