How to Create a table where fields are days of the year in Access 2010?

Question

So the question is pretty much all in the title. I was just wondering if there is an easy way to create a table and have a field for each day of the year without doing 365 entries.

The reason I want this is to make it easier for weekly, monthly and year to date (YTD) calculations. Ideally what I would like to have is a set of tables to represent Days of the Year, Months and then a Year table. Each field would be filled automatically immediately following the date they represent.

Example: In the Days Table we see the adjacent field 7/9/2012 and 7/10/2012. Once the internal clock hits 7/10/2012, the 7/9/2012 filed would populate with the data calc'd from other fields.

The same would go for months as well as years.

Answer 1

I was just wondering if there is an easy way to create a table and have a field for each day of the year without doing 365 entries.

Despite the fact that I'm uncertain about exactly what you have in mind, I am fairly certain the correct answer is "No, there is no easy way". :-)

Creating an Access table with 365 fields (what about leap years?!) is beyond not easy. It's impossible because the maximum number of fields in an Access table is 255.

If you were willing to settle for a portion of a year which fits within that limit, a table with a column for each date still seems like a monumentally bad idea to me.

OTOH, a calendar table with one row per date ("columns are expensive; rows are cheap") can be very useful. I don't understand why you've apparently rejected that approach.

The size of the table shouldn't be a big concern: <= 366 rows per year, but those can be narrow rows.

If the issue is the burden to load all the dates for a given year, that burden can be quite trivial. Just run this LoadCalendar procedure. Without any parameters, it will load the dates for the current year into a Date/Time field named the_date in a table named tblCalendar. You can use the parameters to substitute different year, table, or field.

Public Sub LoadCalendar(Optional ByVal pYear As Integer, _
        Optional ByVal pTable As String = "tblCalendar", _
        Optional ByVal pField As String = "the_date")
    Dim db As DAO.Database
    Dim dte As Date
    Dim dteLast As Date
    Dim intYear As Integer
    Dim rs As DAO.Recordset
    Dim strMsg As String

On Error GoTo ErrorHandler

    intYear = IIf(pYear = 0, Year(Date), pYear)
    dte = DateSerial(intYear, 1, 1)
    dteLast = DateSerial(intYear, 12, 31)

    Set db = CurrentDb
    Set rs = db.OpenRecordset(pTable, dbOpenTable, dbAppendOnly)

    Do While dte <= dteLast
        rs.AddNew
        rs.Fields(pField).value = dte
        rs.Update
        dte = dte + 1
    Loop
    rs.Close

ExitHere:
    On Error GoTo 0
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure LoadCalendar"
    MsgBox strMsg
    GoTo ExitHere
End Sub

If you want to load multiple years in one go, simply do something like this in the Immediate window:

for yr = 2000 to 2012 : LoadCalendar yr : next