adding a time field

  • Thread starter Thread starter James Griffin
  • Start date Start date
J

James Griffin

I have several time fields in time format such as 15:00 as 3pm for for
timeoutDay1 to timeoutDay5 taken away from timeinDay1 to timeinDay5 total
together to give a hourly summation for a working week hourtotals.

in empression builder code:

=([ClockoutDay1]-[ClockinDay1]+[ClockoutDay2]-[ClockinDay2]+[ClockoutDay3]-[ClockinDay3]+[ClockoutDay4]-[ClockinDay4]+[ClockoutDay5]-[ClockinDay5]+[ClockoutDay6]-[ClockinDay6]+[ClockoutDay7]-[ClockinDay7])

(the above code calculates the total time in days and only appears in some
data of the forms but not in the table and not in hours just 0.125 of a day
meaning 3 hours)

what does the VBA code looklike to save to a form "clockings" or a table
"clockings_table"? the vraiable is total_hours

Any help would be much appreciated
 
James

I'm not clear from your description what the underlying table's field's
datatypes are.

How is that data being stored?

(by the way, given that expression, it looks like you have a spreadsheet,
not a relational database)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff, first of all thanks for your reply - I must admit I haven't used
Access in anger for a very long time so I apologise for anything that is
unclear.

Basically I have a relational databse linked by different table indexes. The
main part of the database is a clockings database to keep track of employees
total hours so for table "clock_table" I wish to store all the data - timein1
timeoff1 (time entered fields)...... timein7 timeoff7 are all entered in
"clockings" form with the fields linked to "clock_table". The totalhours is a
double in the form clockings which is required to add the difference between
all the weekly times.

Any help would be most welcome as I'm producing this database as a present
for my father-in-law in Chile and to that end I can send images for more
clarification if you wish ?

Kind Regards

James



Jeff Boyce said:
James

I'm not clear from your description what the underlying table's field's
datatypes are.

How is that data being stored?

(by the way, given that expression, it looks like you have a spreadsheet,
not a relational database)

Regards

Jeff Boyce
Microsoft Office/Access MVP


James Griffin said:
I have several time fields in time format such as 15:00 as 3pm for for
timeoutDay1 to timeoutDay5 taken away from timeinDay1 to timeinDay5 total
together to give a hourly summation for a working week hourtotals.

in empression builder code:

=([ClockoutDay1]-[ClockinDay1]+[ClockoutDay2]-[ClockinDay2]+[ClockoutDay3]-[ClockinDay3]+[ClockoutDay4]-[ClockinDay4]+[ClockoutDay5]-[ClockinDay5]+[ClockoutDay6]-[ClockinDay6]+[ClockoutDay7]-[ClockinDay7])

(the above code calculates the total time in days and only appears in some
data of the forms but not in the table and not in hours just 0.125 of a
day
meaning 3 hours)

what does the VBA code looklike to save to a form "clockings" or a table
"clockings_table"? the vraiable is total_hours

Any help would be much appreciated
Click to expand...
Click to expand...
 
James

My comment about a spreadsheet was due to the listing of what appears to be
"repeating names" (e.g. xxxx1, xxxx2, ... xxxxy7).

This is how a spreadsheet would be set up, but a relational database doesn't
work that way (or at least, as you are discovering, doesn't work easily that
way).

You've describe "how" you are trying to do something (using multiple,
repeating columns, then trying to do spreadsheet-like actions on them). If
you'll describe a bit more about "what" you are trying to do, you could get
the folks here thinking about other ways to do it, maybe even ways that
would take better advantage of Access' strengths. (hint: Access is NOT a
spreadsheet!<g>).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


James Griffin said:
Hi Jeff, first of all thanks for your reply - I must admit I haven't used
Access in anger for a very long time so I apologise for anything that is
unclear.

Basically I have a relational databse linked by different table indexes.
The
main part of the database is a clockings database to keep track of
employees
total hours so for table "clock_table" I wish to store all the data -
timein1
timeoff1 (time entered fields)...... timein7 timeoff7 are all entered in
"clockings" form with the fields linked to "clock_table". The totalhours
is a
double in the form clockings which is required to add the difference
between
all the weekly times.

Any help would be most welcome as I'm producing this database as a present
for my father-in-law in Chile and to that end I can send images for more
clarification if you wish ?

Kind Regards

James



Jeff Boyce said:
James

I'm not clear from your description what the underlying table's field's
datatypes are.

How is that data being stored?

(by the way, given that expression, it looks like you have a spreadsheet,
not a relational database)

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
I have several time fields in time format such as 15:00 as 3pm for for
timeoutDay1 to timeoutDay5 taken away from timeinDay1 to timeinDay5
total
together to give a hourly summation for a working week hourtotals.

in empression builder code:

=([ClockoutDay1]-[ClockinDay1]+[ClockoutDay2]-[ClockinDay2]+[ClockoutDay3]-[ClockinDay3]+[ClockoutDay4]-[ClockinDay4]+[ClockoutDay5]-[ClockinDay5]+[ClockoutDay6]-[ClockinDay6]+[ClockoutDay7]-[ClockinDay7])

(the above code calculates the total time in days and only appears in
some
data of the forms but not in the table and not in hours just 0.125 of a
day
meaning 3 hours)

what does the VBA code looklike to save to a form "clockings" or a
table
"clockings_table"? the vraiable is total_hours

Any help would be much appreciated
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff,

I see your point in terms of the comments, umm I am trying to recreate a
time entry sheet system I use at work as present for my father-in-law. The
repeating names are simply to add time in and time out for the 7 days of the
week.

Why I posted on the macros newgroup is I have a "totalhours" (numeric
double) field which I am adding all differences of timeout1 and timein1 to
timeout7 and timein7 to give the weekly totals and save to the bound table
"clock_table."

My expression at the moment is getting a totalhours in terms of days i.e 1
is equivalent to 24 hours and 0.125 is 3 hours etc. Btw the repeating fields
of timein1/timeout1 to timein7/timeout7 are all in form "clockings" which
also the totalhours field.

All I want to do is calculate the total hours an employee has worked in 1
week and I thought this could be done via a before update event macro ?


Jeff Boyce said:
James

My comment about a spreadsheet was due to the listing of what appears to be
"repeating names" (e.g. xxxx1, xxxx2, ... xxxxy7).

This is how a spreadsheet would be set up, but a relational database doesn't
work that way (or at least, as you are discovering, doesn't work easily that
way).

You've describe "how" you are trying to do something (using multiple,
repeating columns, then trying to do spreadsheet-like actions on them). If
you'll describe a bit more about "what" you are trying to do, you could get
the folks here thinking about other ways to do it, maybe even ways that
would take better advantage of Access' strengths. (hint: Access is NOT a
spreadsheet!<g>).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


James Griffin said:
Hi Jeff, first of all thanks for your reply - I must admit I haven't used
Access in anger for a very long time so I apologise for anything that is
unclear.

Basically I have a relational databse linked by different table indexes.
The
main part of the database is a clockings database to keep track of
employees
total hours so for table "clock_table" I wish to store all the data -
timein1
timeoff1 (time entered fields)...... timein7 timeoff7 are all entered in
"clockings" form with the fields linked to "clock_table". The totalhours
is a
double in the form clockings which is required to add the difference
between
all the weekly times.

Any help would be most welcome as I'm producing this database as a present
for my father-in-law in Chile and to that end I can send images for more
clarification if you wish ?

Kind Regards

James



Jeff Boyce said:
James

I'm not clear from your description what the underlying table's field's
datatypes are.

How is that data being stored?

(by the way, given that expression, it looks like you have a spreadsheet,
not a relational database)

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
I have several time fields in time format such as 15:00 as 3pm for for
timeoutDay1 to timeoutDay5 taken away from timeinDay1 to timeinDay5
total
together to give a hourly summation for a working week hourtotals.

in empression builder code:

=([ClockoutDay1]-[ClockinDay1]+[ClockoutDay2]-[ClockinDay2]+[ClockoutDay3]-[ClockinDay3]+[ClockoutDay4]-[ClockinDay4]+[ClockoutDay5]-[ClockinDay5]+[ClockoutDay6]-[ClockinDay6]+[ClockoutDay7]-[ClockinDay7])

(the above code calculates the total time in days and only appears in
some
data of the forms but not in the table and not in hours just 0.125 of a
day
meaning 3 hours)

what does the VBA code looklike to save to a form "clockings" or a
table
"clockings_table"? the vraiable is total_hours

Any help would be much appreciated
Click to expand...
Click to expand...
Click to expand...
 
James

If you truly wish to "recreate a time entry ... system", and are "welded" to
using repeating fields, have you considered building it in Excel instead of
in Access?

To get the best use of Access' relationally-oriented features/functions, you
need to feed it well-normalized data.

Long before you start working on the "how" (which forms, which macros,
etc.), you need to focus on/start with "what" (what data, what data
structure). If "normalization" and "relational" aren't familiar terms, plan
to spend some time learning about them before you try to build in Access.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


James Griffin said:
Hi Jeff,

I see your point in terms of the comments, umm I am trying to recreate a
time entry sheet system I use at work as present for my father-in-law. The
repeating names are simply to add time in and time out for the 7 days of
the
week.

Why I posted on the macros newgroup is I have a "totalhours" (numeric
double) field which I am adding all differences of timeout1 and timein1 to
timeout7 and timein7 to give the weekly totals and save to the bound table
"clock_table."

My expression at the moment is getting a totalhours in terms of days i.e 1
is equivalent to 24 hours and 0.125 is 3 hours etc. Btw the repeating
fields
of timein1/timeout1 to timein7/timeout7 are all in form "clockings" which
also the totalhours field.

All I want to do is calculate the total hours an employee has worked in 1
week and I thought this could be done via a before update event macro ?


Jeff Boyce said:
James

My comment about a spreadsheet was due to the listing of what appears to
be
"repeating names" (e.g. xxxx1, xxxx2, ... xxxxy7).

This is how a spreadsheet would be set up, but a relational database
doesn't
work that way (or at least, as you are discovering, doesn't work easily
that
way).

You've describe "how" you are trying to do something (using multiple,
repeating columns, then trying to do spreadsheet-like actions on them).
If
you'll describe a bit more about "what" you are trying to do, you could
get
the folks here thinking about other ways to do it, maybe even ways that
would take better advantage of Access' strengths. (hint: Access is NOT
a
spreadsheet!<g>).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


James Griffin said:
Hi Jeff, first of all thanks for your reply - I must admit I haven't
used
Access in anger for a very long time so I apologise for anything that
is
unclear.

Basically I have a relational databse linked by different table
indexes.
The
main part of the database is a clockings database to keep track of
employees
total hours so for table "clock_table" I wish to store all the data -
timein1
timeoff1 (time entered fields)...... timein7 timeoff7 are all entered
in
"clockings" form with the fields linked to "clock_table". The
totalhours
is a
double in the form clockings which is required to add the difference
between
all the weekly times.

Any help would be most welcome as I'm producing this database as a
present
for my father-in-law in Chile and to that end I can send images for
more
clarification if you wish ?

Kind Regards

James



:

James

I'm not clear from your description what the underlying table's
field's
datatypes are.

How is that data being stored?

(by the way, given that expression, it looks like you have a
spreadsheet,
not a relational database)

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
I have several time fields in time format such as 15:00 as 3pm for
for
timeoutDay1 to timeoutDay5 taken away from timeinDay1 to timeinDay5
total
together to give a hourly summation for a working week hourtotals.

in empression builder code:

=([ClockoutDay1]-[ClockinDay1]+[ClockoutDay2]-[ClockinDay2]+[ClockoutDay3]-[ClockinDay3]+[ClockoutDay4]-[ClockinDay4]+[ClockoutDay5]-[ClockinDay5]+[ClockoutDay6]-[ClockinDay6]+[ClockoutDay7]-[ClockinDay7])

(the above code calculates the total time in days and only appears
in
some
data of the forms but not in the table and not in hours just 0.125
of a
day
meaning 3 hours)

what does the VBA code looklike to save to a form "clockings" or a
table
"clockings_table"? the vraiable is total_hours

Any help would be much appreciated
Click to expand...
Click to expand...
Click to expand...
 
Back
Top