best table design for date time intervals

[MeredithS]

I need to create a table that will be part of a larger intake form for
Admissions into our hospital. This particular table will hold information
about a series of events that take place from the time the 1st call is
received to the time the pt is admitted onto the unit -- including, but not
limited to, items such as Time Nursing Assessment Starts, Time Nursing
Assessment Ends, Time Psych Assessment Starts, etc.

Intervals will need to be calculated at some point, either in Access or
later in Excel, and I've had problems with the time/date/calculations in both
programs. Some of these intervals will be > 24 hours; some will even be
negative, as in the difference between a new patient's scheduled arrival time
and the time they actually arrive...

I'd like to start out with a good design that will allow me to make the
interval calculations I'll need to do in the future, but that will be easy
for Admissions intake to use. Any suggestions?

Thanks,

Meredith

 

[John W. Vinson]

I'd like to start out with a good design that will allow me to make the
interval calculations I'll need to do in the future, but that will be easy
for Admissions intake to use. Any suggestions?

In general, store the time points as Date/Time fields, and use the DateDiff()
and other date handling functions to dynamically calculate intervals. You can
calculate in any units from seconds to years; negative intervals and intervals
exceeding 24 hours are not a problem, though you may need a bit of code if you
want to see 3679 seconds as 1:01:19.


John W. Vinson [MVP]

 

[MeredithS]

Thanks, John. I had read an earlier response of yours on this thread same
topic and I had a couple of further questions, if OK?

1. How do you enter the date and time in the same field -- would that be the
General Date format, I'm assuming?

2. I'd also seen some code on how to use the Date functions, but honestly I
don't know where to put that kind of code if I even need it. All I think I
really need is to be able to calculate in minutes; I could then convert to
hours/minutes easily in Excel which is where this data will end up anyway. Is
that correct and, if so, could I just use the DateDiff function you mention?

Many thanks!!

Meredith

 

[John W. Vinson]

Thanks, John. I had read an earlier response of yours on this thread same
topic and I had a couple of further questions, if OK?

1. How do you enter the date and time in the same field -- would that be the
General Date format, I'm assuming?

The format is irrelevant. A Date/Time value is actually stored as a double
float number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. You can set the default value of a field to Now() to have
the current date and time from the computer's system clock filled in when the
record is created, or you can simply type 8/24 1:45pm into a textbox bound to
the field. General Date format will *display* the stored value reasonably
well, or you can come up with your own custom format if you wish.

2. I'd also seen some code on how to use the Date functions, but honestly I
don't know where to put that kind of code if I even need it. All I think I
really need is to be able to calculate in minutes; I could then convert to
hours/minutes easily in Excel which is where this data will end up anyway. Is
that correct and, if so, could I just use the DateDiff function you mention?

I'd put it in a calculated field in a Query. For instance, if you have two
fields AdmitTime and ProcessBTime, you can type

TimeToProcessB: DateDiff("n", [AdmitTime], [ProcessBTime])

in a vacant Field cell in a query. The "n" means miNutes ("m" is Months) and
the function will return an integer number of minutes between the two field
values. This query can be exported to Excel if that's where you're doing your
analyses.

John W. Vinson [MVP]

 

[Steve]

FYI in case you are interested ---

A service I provide is to design the table structure of a database for a
customer. I have done this for numerous customers. My fee is very
reasonable. I provide a map of the tables that shows all the tables in the
database, all the fields in each table, all the relationships between the
tables and the type of relationship for each relationship. The tables are
arranged on the map generally as the flow of information in the database. I
create a map of the tables for every database I do. The map visually shows
what forms and subforms are needed for data entry, shows what special forms
and subforms can be created for dispaying data in the database and shows
what reports and subreports can be created from the data in the database.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Marshall, MVP]

Steve you need to get a life, the one you have is not working.

These newsgroups are for FREE peer to peer support, not a venue for you to
peddle your snake oil.

John... Visio MVP