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


"James Griffin" <James (E-Mail Removed)> wrote in message
news:9A8F82E2-D298-4B58-B7F3-(E-Mail Removed)...
>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

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" wrote:

> 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" <James (E-Mail Removed)> wrote in message
> news:9A8F82E2-D298-4B58-B7F3-(E-Mail Removed)...
> >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

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" <(E-Mail Removed)> wrote in message
news:76389B70-7127-4C7B-BE67-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <James (E-Mail Removed)> wrote in
>> message
>> news:9A8F82E2-D298-4B58-B7F3-(E-Mail Removed)...
>> >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
>>
>>
>>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:76389B70-7127-4C7B-BE67-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <James (E-Mail Removed)> wrote in
> >> message
> >> news:9A8F82E2-D298-4B58-B7F3-(E-Mail Removed)...
> >> >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

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" <(E-Mail Removed)> wrote in message
news:F2B49E48-5A53-4706-93DB-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:76389B70-7127-4C7B-BE67-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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" <James (E-Mail Removed)> wrote in
>> >> message
>> >> news:9A8F82E2-D298-4B58-B7F3-(E-Mail Removed)...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>