FIll in missing date gaps in Access table

I have a list of wind data in a table which is listed by date in the format 'dd/mm/yyyy hh:mm', and each row increases by an increment of 1 hour, so first row is "09/01/1995 15:00", next is "09/01/1995 16:00" all the way to "31/12/1999 22:00". There are some gaps in the data which skip stretches of dates, which is creating problems. How can I fill in these gaps so that the data is continuous and doesn't skip a single hour between these periods?

I'm thinking that I need a new table with a single column of the continuous dates, then join it to the main table and set all blank values to null. How do I do this?
Who is Participating?
Gustav BrockCIOCommented:
There is no need to create a new table. Just fill in (add) the missing records.

Replace table name and field name with those of yours.

Public Function FillWindLog()

  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim datMin  As Date
  Dim datMax  As Date
  Dim datLog  As Date
  Dim datNew  As Date
  Dim datOld  As Date
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * From tblWindLog Order By DateLog")
  With rst
    ' Find first and last logging date.
    datMax = !DateLog.value
    datMin = !DateLog.value
    ' Set initial low value of empty interval.
    datOld = datMin
    ' Set initial high value of current empty interval.
    datLog = datMin
    ' Locate empty intervals.
    ' Stop when reaching the last of the old records.
    While .EOF = False And datLog < datMax
      ' Set high value of a possible empty interval.
      datLog = !DateLog.value
      ' Increment low value of possible empty interval by one hour.
      datNew = DateAdd("h", 1, datOld)
      ' Fill empty intervals with hourly recordings of Null values.
      Do While DateDiff("h", datNew, datLog) >= 1 And datNew < datMax
          !DateLog.value = datNew
        datNew = DateAdd("h", 1, datNew)
      ' Set low value of the next possible empty interval.
      datOld = datLog
  End With
  Set rst = Nothing
  Set dbs = Nothing
End Function

Open in new window

Why not build the table first, fill in the date/times, and then populate the proper record as data comes in.
Scott C
Helen FeddemaCommented:
What I would do is make a structure-only copy of your current table, write code to fill that table with all the records you need (each hour, each day in the desired date range), and then write an append query to append data to the records that have data in the current table, leaving the other records with blank date/time data.
You can use the DateAdd function to create the records, as in the code sample below, which makes 25 records starting with today's date (modify it as needed):
Public Sub FillTableWithDates()

   Dim i As Integer
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim dteRecordDate As Date
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblWindData")
   dteRecordDate = Date
   For i = 1 To 25
      dteRecordDate = DateAdd("h", 1, dteRecordDate)
      rst![DateRecorded] = dteRecordDate
   Next i
End Sub

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Helen FeddemaCommented:
Here is a screen shot of the test table I made, after running the code (the formatting is taken care of in the field's Format property):
Helen FeddemaCommented:
Scott and I were thinking (and posting) the same solution!
jacksonlordAuthor Commented:
This is great. Two questions: 1- How do we get it to run over the entire date period that I'm working with (1995-1999), and 2- How does it get formatted the proper way? Through the code or just with the field type and format?
If the key in the table you are suggesting is the date and time, then you cannot append the existing tables data to that table.  
@ jacksonlord,
However, you could reverse that and append the blank records for the missing date time entries to the existing table.  
One thing I don't quite understand is, how is your process going to react any better to having NULL in the various columns instead of having wind data?  NULL doesn't play well with many things, including some aggregate functions. ;-)
@ jacksonlord,
When you create the table, set the columns other than the date so that they can be NULL and then just insert into the DateTime column (not into any others) and they will be NULL.
jacksonlordAuthor Commented:
The software that analyzes this data is old and fickle. It needs every date column to exist and populated with values. The missing date columns need to be inserted into this main table and all of it's values will be NULL, which the software accepts as such.

Diver, do you mean that I should set the "AllowZeroLength" property to be No for the other fields?

If I do append from the new table to the old (just appending the missing date/times rows), I need to find out exactly what the gaps are that I'm missing in the old table. Not sure how to do that.

Why can we use the newly-created, continuous date/time field as the join field when appending the old table to the new?
jacksonlordAuthor Commented:
*Why can't
do you mean that I should set the "AllowZeroLength" property to be No for the other fields?
No, set that to Yes and set the Required to No.
I need to find out exactly what the gaps are that I'm missing in the old table. Not sure how to do that.
Actually, if you append from the new table to the old one, as long as you set the datetime as a Unique Index, only the missing rows will append (the others will fail because of the Unique Index conflict ;-).
Why can we use the newly-created, continuous date/time field as the join field when appending the old table to the new?
Well, you can . . . but then you have to deal with making sure you know which ones to select and that query can get a bit messy. ;-)
From what you have indicated, this is something of a "one-time" action, so just inserting/appending the missing rows will accomplish the task in a way that doesn't complicate future queries.
By the way, as a passing comment, thanks for noticing that it is "Diver" and not "Driver". ;-)

I've dealt with exactly this problems for an environmental engineering company, and what's been described here is what I did, to the letter!

1. Use Helen's idea to create a table full of all the dates possible.
2. Make sure that the existing table has a) a unique index on the DateRecorded field and b) valid defaults for all the other columns to handle aggregates etc.  (Only use Null for a legitimate "Unknown at the time the records is created" reason)
3.  From memory, I ran into trouble running a straight append query, so I wrote a query that SELECTed the entire second table, put a WHERE DateRecorded NOT IN (SELECT DateRecorded FROM tblMyOriginalTable) and appended that.

I, too, have done this but I used the straight append.  I did it manually, i.e. I initiated the append query by double clicking it, and Access did whine about not being able to append all of the rows.  However, when I clicked teh "go ahead and do what you can" choice, it wound up appending all of the missing entries.  (The ones it whined about were the collisions with the existing data. ;-)
Yes, it is possible to do this on the fly and with the existing data.  However, being a "belt and suspenders" sort of guy, I tend to prefer to use the Calendar table approach.  Among other things, I generally create the Calendar table anyway, so that I'll have a variety of preformated dates and flags, e.g. which pay period, month, quarter, and year the date is in and the day of the week  according to the business calendar in use.
By the way, before you start doing any of these changes to your database, make a copy of it and put that copy in a very safe place.  There is a possibility that you will accidentally screw the whole thing up, in which case, you will be very happy that you have that copy. ;-)
Gustav BrockCIOCommented:
Except that you should always maintain backup, this is not correct.
Nothing can go wrong here as existing data are not touched.
If added records are regretted, just select these (easy, all other fields are Null) and delete.

I think that "nothing can go wrong here" is the rough equivalent of the last words commonly spoken before a pickup truck crash (i.e. "Here, hold my beer and watch this!" ;-).
Also, while there should always be a backup, my most common experience is that, until there has been a catastrophic loss of data, a backup will be considered irrelevent and, therefore, not done.
@8080 Diver,

Or, the backup is "done" and it's only when the aforementioned truck crashes into the servers that they find out that none of the backups worked...!
Gustav BrockCIOCommented:
If so, it would be "dangerous" to work with the database at all. This is, of course, not the case, and to bring a pickup truck into the picture makes no sense; there is no relation between the operation of a database and driving a car.

Backing up an Access database is really easy. Close the file and copy.
Our clients run regular backups. These have never failed.

Adding records to a table is a safe action. Even if the plug is pulled, only (some of) the new records may get lost after a repair of the database.

For a novice (or relative novice), unless working in a controlled environment and through a controlled access to the database, I would contend that working with a database at all is dangerous to the health and well being of the database.  Also, no matter how easy it is to do a back up, if a novice (or relative novice) is involved, then it is not necessarily a given that the database will be backed up on a regular basis or that it will be backed up prior to performing "surgery" on the database (even if you may consider it to be "minor surgery").
The analogy was obviously lost on you so I'll put it in plain English.  IMHO, stating that "Nothing can go wrong here as existing data are not touched." , when dealing with someone who is doing something for the first time is overly optomistic.  In spite of your statement, there are possibilities for things to go wrong.  For instance, if, as you put it, "added records are regretted" and the delete is not done correctly, then the entire table could be cleared out or just the previously existing data could be deleted (instead of the newly inserted data).
Any time I start working with manipulating data in this manner, I either back up the data or I copy it to a test environment before performing the task in the production environment.  Call me paranoid but it has saved my data on occasion.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.