append query for dates and hour

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

I need to create a table filled with the followin data
HOURID DATE HOUR
1 Jan 1 06 1
2 Jan 1 06 2
...... ..... ....
24 Jan 1 06 24
25 Jan 2 06 1

If I name the fields HOURID, DATE, HOUR can anyone help me with the append
structure? The dates need to go from Jan 1 06 to Dec 31 08. Thanks Ian.
 
I need to create a table filled with the followin data
HOURID DATE HOUR
1 Jan 1 06 1
2 Jan 1 06 2
..... ..... ....
24 Jan 1 06 24
25 Jan 2 06 1

If I name the fields HOURID, DATE, HOUR can anyone help me with the append
structure? The dates need to go from Jan 1 06 to Dec 31 08. Thanks Ian.
Click to expand...

This can benefit from a little general-purpose table I call Num, with one Long
Integer field N. Fill it with values from 0 to 10000 or so (it's still a tiny
table); you can do so quickly by opening Excel, putting 0 in A1, 1 in A2 and
selecting A1-A10001 and using Insert... Fill Series. Copy and paste this into
Num.

Given that, a query

INSERT INTO yourtable([HourID], [Date], [Hour])
SELECT A.N*24 + B.N, DateSerial(2006, 1, A.N+1), B.N+1
FROM Num AS A, Num AS B
WHERE A.N <= 1095 AND B.N <24;

(or other variants on this) should do what you want.

I have to wonder what benefit this table would provide - you can get the same
data from a Date/Time field using DateDiff.

Note also that DATE and HOUR are reserved words and bad choices of fieldnames;
Access can and will get confused (with the builtin Date() and Hour()
functions). I'd choose different fieldnames.
 
Thanks John -- each discrete hour has a unique set of variables assoicated
with it depending on time of day, seasonality, etc.... Creating the dates
and hours was the first (I thought laborious) step. Next I add in the new
fields and create a set of update statements. totally forgot to change the
hour, date fields. Thanks. ian.

John W. Vinson said:
I need to create a table filled with the followin data
HOURID DATE HOUR
1 Jan 1 06 1
2 Jan 1 06 2
..... ..... ....
24 Jan 1 06 24
25 Jan 2 06 1

If I name the fields HOURID, DATE, HOUR can anyone help me with the append
structure? The dates need to go from Jan 1 06 to Dec 31 08. Thanks Ian.
Click to expand...

This can benefit from a little general-purpose table I call Num, with one Long
Integer field N. Fill it with values from 0 to 10000 or so (it's still a tiny
table); you can do so quickly by opening Excel, putting 0 in A1, 1 in A2 and
selecting A1-A10001 and using Insert... Fill Series. Copy and paste this into
Num.

Given that, a query

INSERT INTO yourtable([HourID], [Date], [Hour])
SELECT A.N*24 + B.N, DateSerial(2006, 1, A.N+1), B.N+1
FROM Num AS A, Num AS B
WHERE A.N <= 1095 AND B.N <24;

(or other variants on this) should do what you want.

I have to wonder what benefit this table would provide - you can get the same
data from a Date/Time field using DateDiff.

Note also that DATE and HOUR are reserved words and bad choices of fieldnames;
Access can and will get confused (with the builtin Date() and Hour()
functions). I'd choose different fieldnames.
--

John W. Vinson [MVP]

.
Click to expand...
 
Needed some minor mods as it started on Jan 2nd and stopped an hour short
each day: In hindsight - it's because I started the NUM table at 1 instead of
0.

Here is the final SQL statement

INSERT INTO HOURIDDATA ( HourID, POWERDate, POWERHour )
SELECT A.N*24+B.N, DateSerial(2006,1,A.N), B.N
FROM Num AS A, Num AS B
WHERE A.N<=1096 And B.N<25;

Thanks again John.

John W. Vinson said:
I need to create a table filled with the followin data
HOURID DATE HOUR
1 Jan 1 06 1
2 Jan 1 06 2
..... ..... ....
24 Jan 1 06 24
25 Jan 2 06 1

If I name the fields HOURID, DATE, HOUR can anyone help me with the append
structure? The dates need to go from Jan 1 06 to Dec 31 08. Thanks Ian.
Click to expand...

This can benefit from a little general-purpose table I call Num, with one Long
Integer field N. Fill it with values from 0 to 10000 or so (it's still a tiny
table); you can do so quickly by opening Excel, putting 0 in A1, 1 in A2 and
selecting A1-A10001 and using Insert... Fill Series. Copy and paste this into
Num.

Given that, a query

INSERT INTO yourtable([HourID], [Date], [Hour])
SELECT A.N*24 + B.N, DateSerial(2006, 1, A.N+1), B.N+1
FROM Num AS A, Num AS B
WHERE A.N <= 1095 AND B.N <24;

(or other variants on this) should do what you want.

I have to wonder what benefit this table would provide - you can get the same
data from a Date/Time field using DateDiff.

Note also that DATE and HOUR are reserved words and bad choices of fieldnames;
Access can and will get confused (with the builtin Date() and Hour()
functions). I'd choose different fieldnames.
--

John W. Vinson [MVP]

.
Click to expand...
 
Thanks John -- each discrete hour has a unique set of variables assoicated
with it depending on time of day, seasonality, etc.... Creating the dates
and hours was the first (I thought laborious) step. Next I add in the new
fields and create a set of update statements. totally forgot to change the
hour, date fields. Thanks. ian.
Click to expand...

Add the new fields FIRST - create the table with all the fields that you'll
want - and then use my suggested Num query as an Append query to populate it,
would be my suggestion.
 
Back
Top