Accumulate Time

This is probably a simple question for most of you, but I really appreciate
the help!
I have a table named "Issues" with a primary key of "IssuesID" (autonumber)
what I created a form based off this table.
For a given issue I may work 15 minutes on it today and half an hour
tomorrow. I need a way to enter and track my time worked each day for this
issue.

So my question is would it be best to make a seperate table named say
"Hours" to track this? If so how would l link the "Issues" and "Hours" table
together?

Sorry i am new to access so I apologize if this is a silly question.

Thank you!!!!

On Sat, 2 Feb 2008 19:43:01 -0800, zonk2439
wrote:

This is probably a simple question for most of you, but I really appreciate
the help!
I have a table named "Issues" with a primary key of "IssuesID" (autonumber)
what I created a form based off this table.
For a given issue I may work 15 minutes on it today and half an hour
tomorrow. I need a way to enter and track my time worked each day for this
issue.

So my question is would it be best to make a seperate table named say
"Hours" to track this?

Yes, certainly. A fifteen-minute bout of work on an issue is NOT the issue
itself; each issue will have zero, one, or more work bouts.

If so how would l link the "Issues" and "Hours" table
together?

Put a Long Integer field (I'd name it IssuesID, but in fact it can have any
name you like). Open the Relationships window, add both tables, and drag
IssuesID from the Issues table to the IssuesID field in the Hours table. Check
the "Enforce referential integrity" button - this will ensure that you can't
log hours on a nonexistant issue!

I would suggest that you do NOT use a Date/Time field for storing the hours.
Date/Time fields work best for storing exact points in time, and don't work
well for durations (for instance, if you add 1 hour to 23:30 you DON'T get
24:30; you get December 31, 1899, 12:30am, just because of the way date/time
values are stored).

Instead, you can store a Long Integer number of minutes. If you wish, you can
use a Form based on your Issues table, with a Subform based on the Hours
table. I'd suggest that the Hours table have fields:

HoursID autonumber, primary key
TimeOfWork Date/Time, default value Now(), record when the work was done
Duration Long Integer
Comments Text or Memo, free text to describe what was done

You can put two textboxes, HoursWorked and MinutesWorked, on the subform; put
code in the subform's Beforeupdate event to multiply the value in HoursWorked
by 60 and add the value in MinutesWorked and store the result in Duration.

John W. Vinson [MVP]

John,
Thank you so much for your answer!!! I do have a couple of follow up
questions if you don't mind.
I made the relationship just as you said between the "Issues" and "Hours"
tables, using the "IssuesID" from the "Issues" table which is a Autonumber
and the "IssuesID" in the "Hours" table which is a long inte Integer.

I create a subform that has the"IssuesID" "Date Worked", Hours (textbox),
Minutes (textbox) and "Total Hours" fields. All of these except for the
textbox's come from the "Hours" table.

I have two question based on the information above:

1) On the subform the "IssuesID" field is a drop down box that list all the
"IssuesID" that were created in the "Issues" table. So you have to select
the proper "IssuesID" before putting in the time worked. Did I do something
wrong to cause this?

2) I an unsure of how to do the coding for the Hours and minutes. Could you
point me in the right direction of how to do this?

Thank you very much for your time!!!




"John W. Vinson" wrote:

On Sat, 2 Feb 2008 19:43:01 -0800, zonk2439
wrote:

This is probably a simple question for most of you, but I really appreciate
the help!
I have a table named "Issues" with a primary key of "IssuesID" (autonumber)
what I created a form based off this table.
For a given issue I may work 15 minutes on it today and half an hour
tomorrow. I need a way to enter and track my time worked each day for this
issue.

So my question is would it be best to make a seperate table named say
"Hours" to track this?

Yes, certainly. A fifteen-minute bout of work on an issue is NOT the issue
itself; each issue will have zero, one, or more work bouts.

If so how would l link the "Issues" and "Hours" table
together?

Put a Long Integer field (I'd name it IssuesID, but in fact it can have any
name you like). Open the Relationships window, add both tables, and drag
IssuesID from the Issues table to the IssuesID field in the Hours table. Check
the "Enforce referential integrity" button - this will ensure that you can't
log hours on a nonexistant issue!

I would suggest that you do NOT use a Date/Time field for storing the hours.
Date/Time fields work best for storing exact points in time, and don't work
well for durations (for instance, if you add 1 hour to 23:30 you DON'T get
24:30; you get December 31, 1899, 12:30am, just because of the way date/time
values are stored).

Instead, you can store a Long Integer number of minutes. If you wish, you can
use a Form based on your Issues table, with a Subform based on the Hours
table. I'd suggest that the Hours table have fields:

HoursID autonumber, primary key
TimeOfWork Date/Time, default value Now(), record when the work was done
Duration Long Integer
Comments Text or Memo, free text to describe what was done

You can put two textboxes, HoursWorked and MinutesWorked, on the subform; put
code in the subform's Beforeupdate event to multiply the value in HoursWorked
by 60 and add the value in MinutesWorked and store the result in Duration.

John W. Vinson [MVP]

On Sat, 2 Feb 2008 22:06:01 -0800, zonk2439
wrote:

John,
Thank you so much for your answer!!! I do have a couple of follow up
questions if you don't mind.
I made the relationship just as you said between the "Issues" and "Hours"
tables, using the "IssuesID" from the "Issues" table which is a Autonumber
and the "IssuesID" in the "Hours" table which is a long inte Integer.

I create a subform that has the"IssuesID" "Date Worked", Hours (textbox),
Minutes (textbox) and "Total Hours" fields. All of these except for the
textbox's come from the "Hours" table.

I have two question based on the information above:

1) On the subform the "IssuesID" field is a drop down box that list all the
"IssuesID" that were created in the "Issues" table. So you have to select
the proper "IssuesID" before putting in the time worked. Did I do something
wrong to cause this?

The IssuesID field need not - indeed SHOULD not - be displayed on the subform
at all. Just delete the combo box. If it's a Lookup Field in the table, view
its Properties in table design view and change its Lookup property from "combo
box" to "textbox". While you're at it, remove the 0 default value that Access
"helpfully" puts in.

The Subform's Master Link Field and Child Link Field properties should be
IssuesID - this will cause the mainform's autonumber value to automatically
fill in on the subform. You don't need to see it on either form, frankly -
autonumbers are intended to work behind the scenes, linking things together.

2) I an unsure of how to do the coding for the Hours and minutes. Could you
point me in the right direction of how to do this?

You could put code in the Form's BeforeUpdate event. View the form's (the
subform's) properties; click the ... icon by the Before Update line on the
Events tab, and choose Code Builder. Access will put you into the VBA editor
with the Sub and End Sub lines filled in. Update this to

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me![Total Hours] = NZ(Me![Hours]) * 60 + NZ(Me![Minutes])
End Sub


John W. Vinson [MVP]