Append and/or Update Data to Table created Dynamically...

You created a table on-the-fly...
...and now you need to add data to it.  First thing to do is APPEND data that will serve as a Primary Key to the newly created table, i.e. Account Number, Company Name, etc...  Let's call this one qappFiveYearsData.

Now the code to UPDATE the balance of the table with data.  (Note, the qryServiceCallCount was needed to get total count of Service Calls for every year for each Account (Customer).)
And finally, a command button can added to the Form placing the below in the On_Click event...

Call MakeAYDTable
DoCmdSetWarnings False
DoCmd.OpenQuery "qappFiveYearsData"
DoCmdSetWarnings True
Call UpdateServiceCalls

As you see, not that hard, it probably will take more time to write the code than it will to run it!  Have fun!
This code needs Error Handling!
The difficult I do immediately, the impossible takes a little bit longer.
Queries
Tips (Main)
Home
Using Parameters in a Crosstab Query
Action Query Errors
OrderBy OR Order By
Memo field is truncated when exported to Excel
Query Joins 
Function UpdateServiceCalls()
'From http://www.access-diva.com/
 
    Dim strSQL1, strSQL2, strSQL3, strSQL4, strSQL5 As String
    Dim YearMinus0, YearMinus1, YearMinus2, YearMinus3, YearMinus4 As Long
 
        YearMinus0 = Year(Date)
        YearMinus1 = Year(Date) - 1
        YearMinus2 = Year(Date) - 2
        YearMinus3 = Year(Date) - 3
        YearMinus4 = Year(Date) - 4
 
        strSQL1 = "UPDATE qryServiceCallCount INNER JOIN tblFiveYearsData ON qryServiceCallCount.eAccountNumber= tblFiveYearsData.fydAccountNumber " & _
                    "SET [" & YearMinus0 & "-ServiceCalls" & "] = [qryServiceCallCount]![CountOfscServiceCallID] " & _
                        "WHERE qryServiceCallCount.yYear=Year(Date())"
 
        strSQL2 = "UPDATE qryServiceCallCount INNER JOIN tblFiveYearsData ON qryServiceCallCount.eAccountNumber = tblFiveYearsData.fydAccountNumber " & _
                    "SET [" & YearMinus1 & "-ServiceCalls" & "] = [qryServiceCallCount]![CountOfscServiceCallID] " & _
                        "WHERE qryServiceCallCount.yYear=Year(Date()-1)"
 
        strSQL3 = "UPDATE qryServiceCallCount INNER JOIN tblFiveYearsData ON qryServiceCallCount.eAccountNumber = tblFiveYearsData.fydAccountNumber " & _
                    "SET [" & YearMinus2 & "-ServiceCalls" & "] = [qryServiceCallCount]![CountOfscServiceCallID] " & _
                        "WHERE qryServiceCallCount.yYear=Year(Date()-2)"
 
        strSQL4 = "UPDATE qryServiceCallCount INNER JOIN tblFiveYearsData ON qryServiceCallCount.eAccountNumber = tblFiveYearsData.fydAccountNumber " & _
                    "SET [" & YearMinus3 & "-ServiceCalls" & "] = [qryServiceCallCount]![CountOfscServiceCallID] " & _
                        "WHERE qryServiceCallCount.yYear=Year(Date()-3)"
 
        strSQL5 = "UPDATE qryServiceCallCount INNER JOIN tblFiveYearsData ON qryServiceCallCount.eAccountNumber = tblFiveYearsData.fydAccountNumber " & _
                    "SET [" & YearMinus4 & "-ServiceCalls" & "] = [qryServiceCallCount]![CountOfscServiceCallID] " & _
                        "WHERE qryServiceCallCount.yYear=Year(Date()-4)"
 
        CurrentDb.Execute strSQL1, dbFailOnError
        CurrentDb.Execute strSQL2, dbFailOnError
        CurrentDb.Execute strSQL3, dbFailOnError
        CurrentDb.Execute strSQL4, dbFailOnError
        CurrentDb.Execute strSQL5, dbFailOnError
 
End Function
Function MakeAYDTable()
 
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
 
    'Initialize the table.
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("tblFiveYearsData")
 
    'Specify the fields.
    With tdf
        Set fld = .CreateField("fydAccountNumber", dbLong)
        .Fields.Append fld
        .Fields.Append .CreateField("fydCompanyName", dbText, 75)
        .Fields.Append .CreateField(Year(Date) & "-ServiceCalls", dbDouble)
        .Fields.Append .CreateField(Year(Date) - 1 & "-ServiceCalls", dbDouble)
        .Fields.Append .CreateField(Year(Date) - 2 & "-ServiceCalls", dbDouble)
        .Fields.Append .CreateField(Year(Date) - 3 & "-ServiceCalls", dbDouble)
        .Fields.Append .CreateField(Year(Date) - 4 & "-ServiceCalls", dbDouble)
    End With
 
    db.TableDefs.Append tdf
    Set fld = Nothing
    Set tdf = Nothing
 
    'Clean up
    Application.RefreshDatabaseWindow   'Show the changes
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
 
End Function
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.