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:
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

__________________
Neal

A Northern Soul

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

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

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

Many thanks, that is basically it.

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



Neal

A Northern Soul

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