Weekly update to TechHours

[cyberpyxi via AccessMonster.com]

I have a table, Techs, that lists the FirstName (Primary Key), other info,
and HoursThisWeek. There is a form used to enter job visits. On that form,
when you pick the FirstName, it fills in HoursThisWeek from the Tech table.
No problem, got that. My issue runs into the fact that I have to store it by
week. I have it set up so that it fills in HoursThisWeek, adds the current
job hours to that field, and even updates the Techs table. However, I need it
to reset to zero every monday. I can go in and manually set it to zero on
Mondays, but the problem is that when I run my update query after entering
jobs for the new week, the techs that did not have a job put in yet... their
hours update to the last call they DID have hours for.
I.E. If it's Monday, and Jeff went on 2 calls for 3 hours each, running this
update hours query puts him to 6 hours (after I manually set it to 0), but
since Robert did not have any jobs yet, it changes his 0 to 40 from last week.


Any way to address this?

 

[John W. Vinson]

I have a table, Techs, that lists the FirstName (Primary Key),

Ummmmmm... bad choice. Suppose you have a tech named Mary, and another
topnotch candidate applies and her name is also Mary. Do you fire the first
Mary, or not hire the second?!

Names are *NOT* suitable for primary keys. You need some unique TechID
instead.

other info,

and HoursThisWeek.

This should *not* be stored in the techs table at all, since each tech will
(hopefully) work for more than one week! You have a one (tech) to many (weeks)
relationship; use a second Table with a foreign key to the unique TechID, the
date, and the hours worked.

There is a form used to enter job visits. On that form,
when you pick the FirstName, it fills in HoursThisWeek from the Tech table.
No problem, got that. My issue runs into the fact that I have to store it by
week. I have it set up so that it fills in HoursThisWeek, adds the current
job hours to that field, and even updates the Techs table. However, I need it
to reset to zero every monday.

And permanently and irrevokably lose the previous information? Ouch.

I can go in and manually set it to zero on
Mondays, but the problem is that when I run my update query after entering
jobs for the new week, the techs that did not have a job put in yet... their
hours update to the last call they DID have hours for.

I.E. If it's Monday, and Jeff went on 2 calls for 3 hours each, running this
update hours query puts him to 6 hours (after I manually set it to 0), but
since Robert did not have any jobs yet, it changes his 0 to 40 from last week.


Any way to address this?

Correct the error in your query to select only those records that you actually
want to update... or, better, *normalize your tables* so this exercise is not
necessary.

 

[Lord Kelvan]

as a add on to the above about primary keys the primary key should not
be relevant data at all it should just be any old random number that
all it does is identify the record and not have any meaning on the
record. ie name is meaning full data the number 32435 is not. a
serial number is meaningful data the number 1 is not.

hope this helps

Regards
Kelvan

 

[cyberpyxi via AccessMonster.com]

Thank you. I will separate hours and go from there.
Your assistance is appreciated!

Ambyr

 

[John Spencer]

Now we are arguing natural keys versus artificial keys. If a value that
uniquely identifies a record is stable, unique, and unchanging then
there is no problem using it as a primary key.

For instance, the state codes for the US are stable and unique. They
are not necessarily unchanging but they are stable over long periods.
When I was much younger the code for Nebraska was Neb and it is now and
has been for an extended period NE. This code could be used as a
primary key as it is unlikely to ever change in the future - or at least
in the lifetime of databases I will develop.

Names are not good candidates for primary keys - too much chance of
duplication even in small populations.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[cyberpyxi via AccessMonster.com]

Quite understood. I have revamped the DB per your suggestions. Honestly, I
almost always use a unique autonumber or the like for my primary... can't
really recall my reasoning for deviating this time.

I do have another question, if you don't mind. How would you suggest pulling
the Techs table and the Hours table? Or I guess the better question is how
would you set up the hours table? What would your columns be? As I'm going
through, I've changed mine about a dozen times.

I'd love to know your thoughts.

Now we are arguing natural keys versus artificial keys. If a value that
uniquely identifies a record is stable, unique, and unchanging then
there is no problem using it as a primary key.

For instance, the state codes for the US are stable and unique. They
are not necessarily unchanging but they are stable over long periods.
When I was much younger the code for Nebraska was Neb and it is now and
has been for an extended period NE. This code could be used as a
primary key as it is unlikely to ever change in the future - or at least
in the lifetime of databases I will develop.

Names are not good candidates for primary keys - too much chance of
duplication even in small populations.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

as a add on to the above about primary keys the primary key should not
be relevant data at all it should just be any old random number that

[quoted text clipped - 6 lines]

Regards
Kelvan

 

[Lord Kelvan]

tbltech
techid (autonum)
techname (text)
etc...

tblwork
workid (autonum)
techid (number)
hoursdone (number format double)
workdate (date/time format shortdate)
workdescription (text) (if you want to describe the job)

hope this helps

Regards
Kelvan

 

[cyberpyxi via AccessMonster.com]

You know, I was looking at the issue totally backwards. Thank you so much!
That is perfectly suited for the DB I'm working on.

You're awesome