jacksonlord
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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):
tblWindData.jpg
tblWindData.jpg
Scott and I were thinking (and posting) the same solution!
ASKER
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?
@Helen,
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. ;-)
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.
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.
ASKER
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?
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?
ASKER
*Why can't
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
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.
HTH
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.
HTH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Andrew,
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. ;-)
@/gustav,
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.
@Cactus_Data:
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. ;-)
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. ;-)
@/gustav,
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.
@Cactus_Data:
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. ;-)
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.
/gustav
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.
/gustav
@gustav,
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.
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...!
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...!
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.
/gustav
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.
/gustav
Cactus_Data,
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.
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.
Scott C