Access Diva Create a Table with Dynamic Field Names

Create a Table with Dynamic Field Names...

You need to create an UNnormalzed table with, let's say, 5 (five) rotating years worth of data (see bolded section)... either to export to Microsoft Excel or just for review. For this scenario, you want to review all your Clients' Service Calls and whether they were Under Contract or not for the past 5 years. Whatever your reason the issue is not creating the Table it's APPENDING the data.

1. Create your table... You can call from a button with Call MakeFYDTable

Function MakeFYDTable()
'From http://www.access-diva.com/
 
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("AccountNumber", dbText, 50)
    .Fields.Append fld
    .Fields.Append .CreateField("PrimaryName", dbText, 100)
    .Fields.Append .CreateField("DBA", dbText, 100)
    .Fields.Append .CreateField("Address1", dbText, 50)
    .Fields.Append .CreateField("Address2", dbText, 50)
    .Fields.Append .CreateField("CityName", dbText, 50)
    .Fields.Append .CreateField("StateID", dbText, 2)
    .Fields.Append .CreateField("ZipCode", dbText, 10)
    .Fields.Append .CreateField("PhoneAreaCode", dbLong)
    .Fields.Append .CreateField("PhoneNumber", dbText, 8)
    .Fields.Append .CreateField("PhoneExtension", dbText, 10)
    .Fields.Append .CreateField("FirstName", dbText, 50)
    .Fields.Append .CreateField("LastName", dbText, 50)
    .Fields.Append .CreateField("County", dbText, 25)
    .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)
    .Fields.Append .CreateField(Year(Date) & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 1 & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 2 & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 3 & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 4 & "-OnContract", dbBoolean)
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