MSAccess Table: subtract 2 rows (get time difference [minutes] be.

[Guest]

Using Access XP on Win2k.
Note: I am NOT a programmer!!! I am mildly familiar with programming (trace
memories of java from 4 years ago) and not an avid user of Access.

My query is simple- I want an access TABLE to automatically calculate the
time difference between 2 times.

ID Time Start Time End Elapsed Time
1 1:30 2:10 ?????<---
2 12:24 12:49
3 10:46 11:55

?????: This is where I want access to automatically fill in the difference
in minutes.
Is this possble to have in a Table? Or will it have to be in a form? I am
still new to Access having only used tables, so I do not really know the
difference between the 2 yet. Currently the table design is as such:

Field Name Data Type
Date Date/Time
Time Start Date/Time
Time End Date/Time
and my other data follows...

Thank you for your time.

 

[Douglas J. Steele]

What you're asking for isn't possible using an Access table, but then, it
isn't required. (Relational Database Theory explicitly states that no field
in a table should be completely derivable from other fields in the same row
in the table).

What you can do, though, is just store Time Start and Time End in your
table. Then, create a query that has the three fields in it, plus add a
fourth computed field to the query. You do this by typing in an empty column
on the row labeled "Fields" in the query builder. In your case, you'd want
to type:

ElapsedTime: DateDiff("m", [Time Start], [Time End])

Save the query, and use it wherever you would otherwise have used the table.

 

[Charlie Tame]

If I may ask a related question Douglas, is it legit to then write that
computed value back to an empty field (reserved for the job of course) in
the same table, for the sake of posterity or should every subsequent report
calculate it each run?

To be honest I don't see why derivation from within would always be a bad
thing although I can see some cases where it could be, is there a simple
explanation for that? I'm thinking of say joining firstname and lastname
into one field for indexing, allowing duplicates of course in case there are
two charlietames or whatever. Of course I suppose one should do this in the
entry form if at all, not in the tables. Did I just answer my own question?

Charlie

What you're asking for isn't possible using an Access table, but then, it
isn't required. (Relational Database Theory explicitly states that no
field in a table should be completely derivable from other fields in the
same row in the table).

What you can do, though, is just store Time Start and Time End in your
table. Then, create a query that has the three fields in it, plus add a
fourth computed field to the query. You do this by typing in an empty
column on the row labeled "Fields" in the query builder. In your case,
you'd want to type:

ElapsedTime: DateDiff("m", [Time Start], [Time End])

Save the query, and use it wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Using Access XP on Win2k.
Note: I am NOT a programmer!!! I am mildly familiar with programming
(trace
memories of java from 4 years ago) and not an avid user of Access.

My query is simple- I want an access TABLE to automatically calculate the
time difference between 2 times.

ID Time Start Time End Elapsed Time
1 1:30 2:10 ?????<---
2 12:24 12:49
3 10:46 11:55

?????: This is where I want access to automatically fill in the
difference
in minutes.
Is this possble to have in a Table? Or will it have to be in a form? I
am
still new to Access having only used tables, so I do not really know the
difference between the 2 yet. Currently the table design is as such:

Field Name Data Type
Date Date/Time
Time Start Date/Time
Time End Date/Time
and my other data follows...

Thank you for your time.

 

[DL]

You can index on multiple fields, eg Firstname + Lastname without having a
specific field (FirstnameLastname)

If I may ask a related question Douglas, is it legit to then write that
computed value back to an empty field (reserved for the job of course) in
the same table, for the sake of posterity or should every subsequent report
calculate it each run?

To be honest I don't see why derivation from within would always be a bad
thing although I can see some cases where it could be, is there a simple
explanation for that? I'm thinking of say joining firstname and lastname
into one field for indexing, allowing duplicates of course in case there are
two charlietames or whatever. Of course I suppose one should do this in the
entry form if at all, not in the tables. Did I just answer my own question?

Charlie

What you're asking for isn't possible using an Access table, but then, it
isn't required. (Relational Database Theory explicitly states that no
field in a table should be completely derivable from other fields in the
same row in the table).

What you can do, though, is just store Time Start and Time End in your
table. Then, create a query that has the three fields in it, plus add a
fourth computed field to the query. You do this by typing in an empty
column on the row labeled "Fields" in the query builder. In your case,
you'd want to type:

ElapsedTime: DateDiff("m", [Time Start], [Time End])

Save the query, and use it wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Using Access XP on Win2k.
Note: I am NOT a programmer!!! I am mildly familiar with programming
(trace
memories of java from 4 years ago) and not an avid user of Access.

My query is simple- I want an access TABLE to automatically calculate the
time difference between 2 times.

ID Time Start Time End Elapsed Time
1 1:30 2:10 ?????<---
2 12:24 12:49
3 10:46 11:55

?????: This is where I want access to automatically fill in the
difference
in minutes.
Is this possble to have in a Table? Or will it have to be in a form? I
am
still new to Access having only used tables, so I do not really know the
difference between the 2 yet. Currently the table design is as such:

Field Name Data Type
Date Date/Time
Time Start Date/Time
Time End Date/Time
and my other data follows...

Thank you for your time.

 

[Douglas J. Steele]

It's not recommended that you store values that can be easily computed
unless there's a legitimate need for them to be calculated (i.e.: that you
have a need to be able to show what the value calculated at the time was, in
case it changes). I don't see this example falling into that category.

It's generally faster to do an arithmetic calculation than it is to retrieve
the field from the database. The real reason for not storing the value, in
my opinion, though, is to ensure that you don't accidentally change one of
the numbers involved in the calculation and forget to recalculate the other
value(s). In that case, how do you know which number is correct if, for
example, you'd changed Time End on row 1 from 2:10 to 2:20, but Elapsed Time
is still 40 minutes?

And, as was pointed out elsewhere in this thread, there's definitely no need
to join fields for the purposes of indexing. An index in Access can contain
up to 10 separate fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If I may ask a related question Douglas, is it legit to then write that
computed value back to an empty field (reserved for the job of course) in
the same table, for the sake of posterity or should every subsequent
report calculate it each run?

To be honest I don't see why derivation from within would always be a bad
thing although I can see some cases where it could be, is there a simple
explanation for that? I'm thinking of say joining firstname and lastname
into one field for indexing, allowing duplicates of course in case there
are two charlietames or whatever. Of course I suppose one should do this
in the entry form if at all, not in the tables. Did I just answer my own
question?

Charlie

What you're asking for isn't possible using an Access table, but then, it
isn't required. (Relational Database Theory explicitly states that no
field in a table should be completely derivable from other fields in the
same row in the table).

What you can do, though, is just store Time Start and Time End in your
table. Then, create a query that has the three fields in it, plus add a
fourth computed field to the query. You do this by typing in an empty
column on the row labeled "Fields" in the query builder. In your case,
you'd want to type:

ElapsedTime: DateDiff("m", [Time Start], [Time End])

Save the query, and use it wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Using Access XP on Win2k.
Note: I am NOT a programmer!!! I am mildly familiar with programming
(trace
memories of java from 4 years ago) and not an avid user of Access.

My query is simple- I want an access TABLE to automatically calculate
the
time difference between 2 times.

ID Time Start Time End Elapsed Time
1 1:30 2:10 ?????<---
2 12:24 12:49
3 10:46 11:55

?????: This is where I want access to automatically fill in the
difference
in minutes.
Is this possble to have in a Table? Or will it have to be in a form? I
am
still new to Access having only used tables, so I do not really know the
difference between the 2 yet. Currently the table design is as such:

Field Name Data Type
Date Date/Time
Time Start Date/Time
Time End Date/Time
and my other data follows...

Thank you for your time.

 

[Guest]

Well that's a bummer. Thank you for such a quick response. I"m wondering,
then, is this something that can be done w/ Excel? I tried
adding/subtracting times and it didn't quite work out.... Thanks much.
Cheers,
Henry

It's not recommended that you store values that can be easily computed
unless there's a legitimate need for them to be calculated (i.e.: that you
have a need to be able to show what the value calculated at the time was, in
case it changes). I don't see this example falling into that category.

It's generally faster to do an arithmetic calculation than it is to retrieve
the field from the database. The real reason for not storing the value, in
my opinion, though, is to ensure that you don't accidentally change one of
the numbers involved in the calculation and forget to recalculate the other
value(s). In that case, how do you know which number is correct if, for
example, you'd changed Time End on row 1 from 2:10 to 2:20, but Elapsed Time
is still 40 minutes?

And, as was pointed out elsewhere in this thread, there's definitely no need
to join fields for the purposes of indexing. An index in Access can contain
up to 10 separate fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If I may ask a related question Douglas, is it legit to then write that
computed value back to an empty field (reserved for the job of course) in
the same table, for the sake of posterity or should every subsequent
report calculate it each run?

To be honest I don't see why derivation from within would always be a bad
thing although I can see some cases where it could be, is there a simple
explanation for that? I'm thinking of say joining firstname and lastname
into one field for indexing, allowing duplicates of course in case there
are two charlietames or whatever. Of course I suppose one should do this
in the entry form if at all, not in the tables. Did I just answer my own
question?

Charlie

What you're asking for isn't possible using an Access table, but then, it
isn't required. (Relational Database Theory explicitly states that no
field in a table should be completely derivable from other fields in the
same row in the table).

What you can do, though, is just store Time Start and Time End in your
table. Then, create a query that has the three fields in it, plus add a
fourth computed field to the query. You do this by typing in an empty
column on the row labeled "Fields" in the query builder. In your case,
you'd want to type:

ElapsedTime: DateDiff("m", [Time Start], [Time End])

Save the query, and use it wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using Access XP on Win2k.
Note: I am NOT a programmer!!! I am mildly familiar with programming
(trace
memories of java from 4 years ago) and not an avid user of Access.

My query is simple- I want an access TABLE to automatically calculate
the
time difference between 2 times.

ID Time Start Time End Elapsed Time
1 1:30 2:10 ?????<---
2 12:24 12:49
3 10:46 11:55

?????: This is where I want access to automatically fill in the
difference
in minutes.
Is this possble to have in a Table? Or will it have to be in a form? I
am
still new to Access having only used tables, so I do not really know the
difference between the 2 yet. Currently the table design is as such:

Field Name Data Type
Date Date/Time
Time Start Date/Time
Time End Date/Time
and my other data follows...

Thank you for your time.

 

[Charlie Tame]

Answering your post Douglas but thanks to DL as well. Yes I do see the point
however with systems I have used before the indexing situation would have
been valid as it kept a separate index file for each field indexed, but this
was back when 25MHz was considered fast enough and the whole of Windows
would fit in what is now considered an "Update"

Thanks again, these common knowledge things (to you guys) are invaluable to
anyone not familiar with programs.

Charlie

It's not recommended that you store values that can be easily computed
unless there's a legitimate need for them to be calculated (i.e.: that you
have a need to be able to show what the value calculated at the time was,
in case it changes). I don't see this example falling into that category.

It's generally faster to do an arithmetic calculation than it is to
retrieve the field from the database. The real reason for not storing the
value, in my opinion, though, is to ensure that you don't accidentally
change one of the numbers involved in the calculation and forget to
recalculate the other value(s). In that case, how do you know which number
is correct if, for example, you'd changed Time End on row 1 from 2:10 to
2:20, but Elapsed Time is still 40 minutes?

And, as was pointed out elsewhere in this thread, there's definitely no
need to join fields for the purposes of indexing. An index in Access can
contain up to 10 separate fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If I may ask a related question Douglas, is it legit to then write that
computed value back to an empty field (reserved for the job of course) in
the same table, for the sake of posterity or should every subsequent
report calculate it each run?

To be honest I don't see why derivation from within would always be a bad
thing although I can see some cases where it could be, is there a simple
explanation for that? I'm thinking of say joining firstname and lastname
into one field for indexing, allowing duplicates of course in case there
are two charlietames or whatever. Of course I suppose one should do this
in the entry form if at all, not in the tables. Did I just answer my own
question?

Charlie

What you're asking for isn't possible using an Access table, but then,
it isn't required. (Relational Database Theory explicitly states that no
field in a table should be completely derivable from other fields in the
same row in the table).

What you can do, though, is just store Time Start and Time End in your
table. Then, create a query that has the three fields in it, plus add a
fourth computed field to the query. You do this by typing in an empty
column on the row labeled "Fields" in the query builder. In your case,
you'd want to type:

ElapsedTime: DateDiff("m", [Time Start], [Time End])

Save the query, and use it wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using Access XP on Win2k.
Note: I am NOT a programmer!!! I am mildly familiar with programming
(trace
memories of java from 4 years ago) and not an avid user of Access.

My query is simple- I want an access TABLE to automatically calculate
the
time difference between 2 times.

ID Time Start Time End Elapsed Time
1 1:30 2:10 ?????<---
2 12:24 12:49
3 10:46 11:55

?????: This is where I want access to automatically fill in the
difference
in minutes.
Is this possble to have in a Table? Or will it have to be in a form?
I am
still new to Access having only used tables, so I do not really know
the
difference between the 2 yet. Currently the table design is as such:

Field Name Data Type
Date Date/Time
Time Start Date/Time
Time End Date/Time
and my other data follows...

Thank you for your time.

 

[Guest]

While you received a lot of valuable instruction in database development, I
don't think that your original question was actually ever answered fully.
You can do what you are asking simply by creating a query based on your
table. In your query put all the fields you want plus one for your Elapsed
time. Use the Datediff() formula for it. Then you can base your form off of
this query.

Now you have your elapsed time AND you're obeying all database development
rules.

 

[Douglas J. Steele]

Isn't that what I said in my post?

 

[Douglas J. Steele]

Why not create a query as I suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well that's a bummer. Thank you for such a quick response. I"m
wondering,
then, is this something that can be done w/ Excel? I tried
adding/subtracting times and it didn't quite work out.... Thanks much.
Cheers,
Henry

It's not recommended that you store values that can be easily computed
unless there's a legitimate need for them to be calculated (i.e.: that
you
have a need to be able to show what the value calculated at the time was,
in
case it changes). I don't see this example falling into that category.

It's generally faster to do an arithmetic calculation than it is to
retrieve
the field from the database. The real reason for not storing the value,
in
my opinion, though, is to ensure that you don't accidentally change one
of
the numbers involved in the calculation and forget to recalculate the
other
value(s). In that case, how do you know which number is correct if, for
example, you'd changed Time End on row 1 from 2:10 to 2:20, but Elapsed
Time
is still 40 minutes?

And, as was pointed out elsewhere in this thread, there's definitely no
need
to join fields for the purposes of indexing. An index in Access can
contain
up to 10 separate fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If I may ask a related question Douglas, is it legit to then write that
computed value back to an empty field (reserved for the job of course)
in
the same table, for the sake of posterity or should every subsequent
report calculate it each run?

To be honest I don't see why derivation from within would always be a
bad
thing although I can see some cases where it could be, is there a
simple
explanation for that? I'm thinking of say joining firstname and
lastname
into one field for indexing, allowing duplicates of course in case
there
are two charlietames or whatever. Of course I suppose one should do
this
in the entry form if at all, not in the tables. Did I just answer my
own
question?

Charlie

message
What you're asking for isn't possible using an Access table, but then,
it
isn't required. (Relational Database Theory explicitly states that no
field in a table should be completely derivable from other fields in
the
same row in the table).

What you can do, though, is just store Time Start and Time End in your
table. Then, create a query that has the three fields in it, plus add
a
fourth computed field to the query. You do this by typing in an empty
column on the row labeled "Fields" in the query builder. In your case,
you'd want to type:

ElapsedTime: DateDiff("m", [Time Start], [Time End])

Save the query, and use it wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Using Access XP on Win2k.
Note: I am NOT a programmer!!! I am mildly familiar with programming
(trace
memories of java from 4 years ago) and not an avid user of Access.

My query is simple- I want an access TABLE to automatically calculate
the
time difference between 2 times.

ID Time Start Time End Elapsed Time
1 1:30 2:10 ?????<---
2 12:24 12:49
3 10:46 11:55

?????: This is where I want access to automatically fill in the
difference
in minutes.
Is this possble to have in a Table? Or will it have to be in a form?
I
am
still new to Access having only used tables, so I do not really know
the
difference between the 2 yet. Currently the table design is as such:

Field Name Data Type
Date Date/Time
Time Start Date/Time
Time End Date/Time
and my other data follows...

Thank you for your time.