adding date records

G

Giz

Hi,

I have a table that has data by date. One of my fields is a Date field that
has corresponding data for each date. Specifically, I have climate data for
each day of the year from jan 1, 1990 to dec 31, 2004. However, there are
"missing" days in the Date field, i.e feb 2 to feb 20, 1995 may be absent
from the table. Is there a way that I could "add" these days to the Date
field, therefore increasing the records in my table by the missing days, and
then populate the corresponding data with a "-99" value, without having to
create other tables (i.e. a complete date table) first?
 
6

'69 Camaro

Hi.
Is there a way that I could "add" these days to the Date
field, therefore increasing the records in my table by the missing days, and
then populate the corresponding data with a "-99" value, without having to
create other tables (i.e. a complete date table) first?
Click to expand...

Yes. You can. And you shouldn't, because it's irrelevant data. I suspect
you are having trouble with queries involving this table, which aren't
showing data for certain dates because these dates aren't in this specific
table. That's because you are using the default INNER JOIN, instead of an
OUTER JOIN between the two tables. Modify these queries in SQL View by
changing INNER JOIN to LEFT JOIN or RIGHT JOIN between this table and the
other table and you'll get the rest of the records for the "missing" dates.
The direction depends upon which table is on the left and right of the other,
but you may easily experiment to find the correct one.

Alternatively, you may open the query in Design View and right click on the
join line and select "Join Properties" on the pop-up menu. There are three
choices, and the first one is already selected. Change that to either the
second or third choice, depending upon which table you need to see all of the
records for. Experiment if you need to. After doing so, you may open the
query in SQL View and see whether you got the LEFT JOIN or RIGHT JOIN between
these two tables.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
M

[MVP] S.Clark

You could add data to the table with an Append Query. Finding all of the
missing date ranges is a little tricky, but can be done.

The way that I would do it, is with VBA code. I'd step through every day.
If the day is not found, write the -99's. Maybe 15 lines of code.


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
G

Giz

Hi,

It is, actually, relevant data. I think maybe I didn't explain myself really
well. I am not running queries on this table, rather I need all the dates
populated from 01/01/1990 to 12/31/2004 with data because this data is going
to be used in a time-step climate model. I can't have any gaps in the dates.
So even for days where there is no data, i.e. where the -99 is going to be
populated, I need those days in the data table.
 
G

Giz

Thanks for the idea, now I just have to figure out the code..

Alternatively, I suppose I could create a "dummy" table with just a field of
dates, with all dates populated. Then I would join this table with the data
table on the date field, including all records from the dummy table and only
those records from the data table that match (I think this is called a left
join) and populate data fields with -99 if records are null (i.e. where new
dates are added). I was just wondering if there was a quicker way around
this...
 
6

'69 Camaro

Hi.
this data is going
to be used in a time-step climate model. I can't have any gaps in the dates.
Click to expand...

A legitimate reason. First make a backup copy of your table in case
something goes wrong. Then here's what to do:

1.) Create a new table called tblDates. Create an Autonumber primary key
and the TestDate field of type Date. Save the table and name it.

2.) Create a new module and paste the following code into it:

Public Sub insertDates()

On Error GoTo ErrHandler

Dim dtTest As Date

dtTest = #1/1/1990#

Do While dtTest <= #12/31/2004#
CurrentDb().Execute "INSERT INTO tblDates (TestDate) " & _
"VALUES (#" & dtTest & "#);", dbFailOnError
dtTest = DateAdd("d", 1, dtTest)
Loop

Exit Sub

ErrHandler:

MsgBox "Error in insertDates( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

3.) Save the module and compile the code.

4.) Run this procedure to fill the table with incremental dates.

5.) Create a new query and open it in SQL View and paste the following into
it:

UPDATE tblModel RIGHT JOIN tblDates ON tblModel.TestDate = tblDates.TestDate
SET tblModel.TestDate = tblDates.TestDate, Data = -99
WHERE ((ISNULL(tblDates.TestDate) = FALSE) AND (ISNULL(tblModel.Data) = TRUE))

Replace tblModel with the name of your table. Replace TestDate with the
name of the date field (please don't tell me it's "Date" because you can't
use this Reserved word if you want your date data to be correct) in your
table. Replace Data with the name of your field that is going to hold the
-99 value.

6.) Run this query. There are now no date gaps in your table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Top