creating a table with time values

[Devon]

Hello

I work with the Server logs for my company. I run weekly reports of this
data, and send the results to people throughout the company. I am working on
enhancing some of the reports to show trends based on the time of the day.

One of the tables that contains the server data contains one column with the
date, and a separate column with the time (date is Long Date, and time is
Long Time).

I would like to create a separate table within the database that contains
time. I am thinking that the first column in this table would contain Time
in Long Time format (column would be used to link up to the Server table),
the second column would contain Time in Short Time format, and the final
column would contain Time in (hh AM/PM) format. The reason for this table
would be for doing an equi-join to the server table where I would be able to
show in a report how many Server logs come in at any given hour to look for
trends or potential problems.

My question involves how to create the Time in Long Time format where it
shows every second of a 24 hour period with AM/PM. Initially, I tried to
create the table in MS Excel, so that I could use the autofill option. This
appeared to work, however, when I imported to MS Access, none of the data in
this table works in the equi-join query with the Server table. My guess is
that it is somehow due to the format, however, on the surface, everything
looks the same. If I manually enter the data into MS Access, it works, but I
would like to find a way to programmatically accomplish the time table. The
thought of manually entering 80,000+ rows of data doesn't sound too exciting.

Any ideas?

For an example, please use the following:

S Table for Server Table
LTime for Long Time column
STime for Short Time column
HTime for Hours Time column.

Thanks in advance

Devon

 

[Jeff Boyce]

Devon

First of all, don't bother storing separate "formats" of the same underlying
data. You can use a query in Access to generate those different formats on
the fly, then join to that query.

Next, your definition of Long and Short Time may differ from what Access
uses. Please provide examples of the data itself.

Finally, Access offers a Date/Time data type. Rather than try to maintain
the separate fields, consider combining date and time together and storing
in a single field. You can use the Format() function for display and the
DatePart() function to grab pieces of the whole.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Devon]

Jeff

For Long Time, I am referring to HH:NN:SS AM/PM format (07:15:35 AM). For
Short time, HH:NN AM/PM (07:15 AM), and for Hours Time HH AM/PM (7 AM).

What would be the benefit of combining the date and time? I was trying to
keep things as simple as possible. Once the 'Times' table is complete, I
think that it will be pretty simple.

For the end result, I am looking for a report (which I know how to create)
will have a summary query behind the scenes that states for example:

On January 6, 2009 during the 7am hour, there were 'x' server log instances.
During the 8am hour, there were 'x' server log instances. Etc.....

Hope that makes sense.

Thanks

Devon

 

[Douglas J. Steele]

Jeff

For Long Time, I am referring to HH:NN:SS AM/PM format (07:15:35 AM). For
Short time, HH:NN AM/PM (07:15 AM), and for Hours Time HH AM/PM (7 AM).

Don't confuse Format with Value Stored. Format strictly impacts what's
displayed, not what's stored.

You definitely do NOT need multiple columns in a table. Instead, create a
query that has computed fields that use the Format function (not the Format
property of the field) to create the additional views of the data you want.

What would be the benefit of combining the date and time? I was trying to
keep things as simple as possible. Once the 'Times' table is complete, I
think that it will be pretty simple.

Having Date and Time combined makes most queries easier. If you want to see
the data between 6:00 PM, 4 Jan, 2009 and noon, 6 Jan, 2009, the WHERE
clause would be the following if date and time are combined:

WHERE EventDateTime BETWEEN #2009-01-04 18:00:00# AND #2009-01-06 12:00:00#

If they're not combined, it beccomes far more complicated:

WHERE (EventDate = #2009-01-04# AND EventTime >= #18:00:00#)
OR (EventDate = #2009-01-05#)
OR (EventDate = #2009-01-06# AND EventTime <= 12:00:00#)

If all you want is the date portion, you use the DateValue function. If all
you want is the time portion, you use the TimeValue function.

Behind the scenes, the Date data type is an eight-byte floating point
number, where the integer portion represents the date as the number of days
relative to 29 Dec, 1899, and the decimal portion represents the time as a
fraction of a day.

 

[Devon]

Jeff and Doug

Thank you very much for this information. It works great and has saved me a
ton of time and space on the database.

Thanks

Devon

 

[Michael Gramelspacher]

WHERE EventDateTime BETWEEN #2009-01-04 18:00:00# AND #2009-01-06 12:00:00#

Yes, but wouldn't this work and be not too much more complicated?

WHERE EventDate+EventTime BETWEEN #2009-01-04 18:00:00# AND #2009-01-06 12:00:00#

 

[Douglas J. Steele]

Yeah, you're right: that would work, but it does involve unnecessary
additions for every record in the table.