Append Query (Dates)

Neal · November 16th, 2007, 07:15 AM

Hi,

I have a 'dbo_Week_Commencing' table which contains 3 columns:

ID (Long) Primary Key
Date (Short Date)
Week Commencing (Short Date)

The 'Date' field contains sequential records by date.
The 'Week Commencing' field contains records where the 'Date' column FirstDayOfTheWeek is Sunday.

For Example:

Code:

ID     Date         Week Commencing
6631    20/12/2009    20/12/2009
6632    21/12/2009    20/12/2009
6633    22/12/2009    20/12/2009
6634    23/12/2009    20/12/2009
6635    24/12/2009    20/12/2009
6636    25/12/2009    20/12/2009
6637    26/12/2009    20/12/2009
6638    27/12/2009    27/12/2009
6639    28/12/2009    27/12/2009
6640    29/12/2009    27/12/2009
6641    30/12/2009    27/12/2009
6642    31/12/2009    27/12/2009
6643    01/01/2010    27/12/2009
6644    02/01/2010    27/12/2009

The above extraction shown the last 14 (2 week) records in the table.
Note the 1st and 8th rows contain the Sunday (FirstDayOfTheWeek) for both the 'Date' and 'Week Commencing' Columns.

Question
How do I write an append query to add another consecutive 365 (1 year) records for both columns?

Thanks in advance,

Neal

A Northern Soul

mmcdonal · November 16th, 2007, 09:10 AM

I don't understand what you are asking here? Do you have 365 records for 2009 and you want 365 records for 2010? If this is what you want, I can code it for you, but I am not sure how to do an append query with this.

mmcdonal

Look it up at: http://wrox.books24x7.com

Neal · November 18th, 2007, 12:44 PM

Yes, I do want to add another 365 records (to both columns). I thought that there might be a way of appending by a query.

But if not then any help would be appreciated.

Many Thanks,

Neal

A Northern Soul

mmcdonal · November 19th, 2007, 10:19 AM

Here is how I would do this (assuming your ID is autonumber):

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim dtStart As Date
Dim dtEnd As Date
Dim dtWeek As Date
Dim i As Integer 'check for runaway loop

i = 0
dtStart = (input start date here like "01/01/2009")
dtEnd = (input end date here, plus one day, like "01/01/2010")

sSQL = "SELECT * FROM tblYourTableName"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until dtStart = dtEnd Or i = 400
    If DatePart("w", dtStart) = 1 Then
        dtWeek = dtStart
    End If

    rs.AddNew
        rs("Date") = dtStart
        rs("Week Commencing") = dtWeek
        rs.Update

    dtStart = DateAdd("d", 1, dtStart)
    i = i + 1
Loop

rs.Close

This will add all but the first 6 or less Week Commencing dates unless 2009 starts on a Sunday. You will only have to fill those in by hand.

Also, "Date" is a reserved name, and should not be used for a field name.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Neal · November 19th, 2007, 01:17 PM

Many thanks, that is basically it.

I put it into a sub-routine and gave it a spin.

Neal

A Northern Soul

SerranoG · November 20th, 2007, 09:08 AM

Just some observations.

If your field is called DATE, change the name. It will intefere with the Access function Date() in calculations and statements.

Even if there is more data besides date and date commencing in your table. You should not actually storing date commencing. You should be just recalculating it on the fly when you need it based on the date. You're storing extra data that's bloating your dB size.

The Sunday prior to any date is simply

Date Commencing = DateAdd("d", -1 * Weekday([YourDateField]) + 1, [YourDateField])

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division