Re: Employee hrs worked

Pete:

Can I make one general observation first. The normal convention is to use
plural nouns for table names and plural or collective nouns for column names,
so table names like tblCategories would be better, though Categories would be
even better in my view as the SQL of queries becomes more intuitive when the
names are as close as possible to real English. CategoryID and Category as
column names are fine. Some developers recommend lower case names for
columns, e.g. category, but I use CamelCase myself, e.g. TransactionDate
rather than transactiondate.

You don't have a Projects table as yet. I assume that's to be built in.

The Comments column is fine so long as that is what it is. Be careful not
to include things here which properly should go in related tables, however.
Its not uncommon for people to use text for what is in reality a data
structure. Querying the database on such data becomes very difficult. Again
its a question of giving plenty of thought to the logical model at the outset.

Turning to your question, you have two options here, both of which are valid:

1. Include CategoryID and SubcategoryID columns in the tblHoursWorked table
and create a relationship between tblHoursWorked and tblSubCategory on both
columns, enforcing referential integrity. This means only valid combinations
of CategoryID and SubcategoryID can be entered in tblHoursWorked.

2. Include only SubCategoryID in tblHours Worked as this maps to the
category via the relationships.

Whichever way you do it you set up the correlated combo boxes by having the
SubCategoryID's RowSource reference the Category combo box:

SELECT SubCategoryID, Subcategory
FROM tblSubCategory
WHERE CategoryID = Form!cbocategoryID
ORDER BY Subcategory;

where cboCategoryID is the name of the combo box bound to the CategoryID
column. Notice the use of the Form property in the SQL rather than a fully
qualified reference to the form. As both controls are in the same from you
can do this here.

In the AfterUpdate event procedure of cboCategoryID you requery the other
combo box so it lists only the relevant subcategories with:

Me.cboSubcategoryID.Requery

With option 2 above the cboCategoryID control would be unbound of course as
there is no CategoryID column in the underlying table. This is fine in
single form view but in continuous view won't work on its own. Instead you
can use a hybrid control made up of a combo box with a text box superimposed
on it so it appears to be a single control to the user. I've posted a demo
of how to do this at:


http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=23626&webtag=ws-msdevapps


If you download the demo you'll see that I'm not very keen on using hybrid
controls in this way and suggest an alternative, using a bound multi-column
combo box and unbound text boxes which reference the columns of the combo
box. My demo has three levels of categories (County, District and Parish)
however, whereas you have just two, so a hybrid control would be simpler and
have less effect on performance in your case.

Although option 2 is a purer model, option 1 is simpler to implement. The
choice is yours.

BTW if my demo had used option 1, including ParishID, DistrictID and
CountyID columns in the Locations table, the relationship between Parishes
and Districts would have been on three columns and that between Districts and
Counties on 2 columns, which is manageable, but with a longer chain of
hierarchically related entity types things would begin to get a bit out of
hand.

Ken Sheridan
Stafford, England

"Pete" wrote:

Ken,

I've done as you & Larry suggested, and laid out my tables on paper.
Here is what I have so far:

tblEmployee
ClockNo
Firstname
Surname
DeptID
ShiftID

tblShift
ShiftID
Shift

tblDept
DeptID
Dept

tblType
TypeID
Type

tblCategory
CategoryID
Category

tblSubCategory
SubcategoryID
CategoryID
Subcategory

tblHoursWkd
HoursWkdID
ClockNo
TypeID
DateWkd
HoursWkd
Comments

Somewhere in tblHoursWkd I need to record the Category & Subcategory of
what the hours were worked on. Do I need the CategoryID & SubcategoryID
in tblHoursworked. I would like to have 2 Combo's whereby the first
decides what appears in the second. I have done this in the past so
know how to do it, but can you tell me what I need to do to get a list
of the Employees names from tblEmployee (ClockNo) if Absence (Category)
and Sickness (Subcategory). I will need another entry to store this as
well.

For the purpose of this Database, all I want to do is record what Hours
an Employee has worked.

Your help would be greatly appreciated

thanks

Peter


On Dec 13, 7:00 pm, Ken Sheridan
<KenSheri...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Pete:

You need to consider the appropriate logical model for this. In the
database relational model tables represent entity types and columns represent
attributes of the entity type in question, e.g. Employees is an entity type
and FirstName and LastName are attributes of that entity type. So a table
Employees would have those columns, plus others, and an EmployeeID primary
key column, which can be an autonumber, as names can be duplicated (I worked
with two Maggie Taylors once). Each non-key column in a table must be
'functionally dependent' on the whole key and solely on the key;
'functionally dependent' means that wherever a value of EmployeeID is
encountered the same values of columns such as FirstName and LastName are
inferred. This is known as 'normalization' and if the rules of normalization
are broken update anomalies can result.

Tables can also represent relationship types. Assuming each employee might
work on many projects and each project will involve more than one employee
the many-to-many relationship between Employees and Projects would be
modelled by a table with foreign key columns EmployeeID and ProjectID
referencing the primary keys of Employees and Projects. This table will
involve other columns such as the start and end dates on a project by the
employee. These date columns in fact form part of the primary key (or at
least of a 'candidate' key) of this table as the same employee might work on
the same project at different times. Other columns in this table might
represent functionally dependent attributes of the relationship type, e.g.
the location at which the employee worked on the project in each instance.
From this you'll see that a relationship type is really a special type of
entity type, so its true to say that all tables model entity types.

Turning to your problem, you are trying to force two different attribute
types into one column in the case of Reason. In the case of 'sickness cover'
this differs radically in concept from the project on which the employee is
working, and a project name is not a valid value of this attribute type. You
might have a value 'project work', however. 'Sickness cover' and 'project
work' would be values in a table, WorkReasons say, referenced from your
TimeLog table.

When an employee is working on a project there would be a separate column in
the Timelog table to record this. This would be a foreign key ProjectID
referencing the key of a Projects table. You need to consider whether this
and 'sickness cover' are mutually exclusive or not, i.e. is an employee
covering another sick employee nevertheless working on a project, into which
the hours in question should be factored when determining hours worked (and I
assume costs) per project.

To record which sick employee is being covered you could have an foreign key
CoveredEmployeeID in the TimeLog table referencing the primary key of the
Employees table. This would have a default value of zero indicating that no
employee is being covered unless a non-zero value referencing a particular
employee is entered. An alternative approach would be to model this would be
to model the relationship by another table with TimeLogID and
CoveredEmployeeID columns referencing the primary keys of TimeLog and
Employees; this is similar to a table modelling a many-to-many relationship
described above, but in this case models a one-to-many relationship. This
table would only have rows for each TimeLog record where one employee is
covering for another. Using a table to model a one-to-many relationship like
this is unusual but is a perfectly valid approach in this sort of situation.

HoursType is a simple foreign key in TimeLog referencing an HoursTypes table
with two rows for CM Hours and Banked Hours respectively.

With a logical model along these lines reporting is then a matter of joining
the relevant tables in queries for use as the RecordSources of reports or
forms. data input should be via forms, the principal one relating to your
problem being one bound to the TimeLog table. This can include validation so
that you can only select a Covered Employee for instance where the WorkReason
is 'Sickness cover' or similar, and where the HoursType is 'Banked'.

You'll find understanding the above, and fine tuning it to suit your model,
a lot easier if you draw it out on paper with boxes representing the tables
and lines the relationships between them. Spend as much time as necessary
doing this before you think about setting up the tables themselves. Getting
the logical model right at the start is the key to a successful database and
will save you many headaches later in the development of the application.

Ken Sheridan
Stafford, England



"Pete" wrote:
I am hoping someone can give me some advice on how to set up an access
database to record the Hours worked on my shift.

We have 2 types of hours, CM Hours & Banked Hours I would like to
record when they were worked and for what reason e.g

Employee = John Smith
Date worked = 11/12/06
Type of hours = Banked
Reason = Sickness Cover - <employee>
Hours = 8

In this example I would like to record whom the Sickness cover was for
as well and be able to see how many hours were used to cover an
employee whilst they've been off ill.

another example

Employee = Fred Bloggs
Date worked = 8/12/06
Type of Hours = CM Hours
Reason = WCM Activities - <project name>
Hours = 8

In this example I would like to record the name of the project on which
the hours were worked, and like above be able to view from a report how
many hours have been worked on that project and by whom.

I hope you can help

Regards

Pete- Hide quoted text -- Show quoted text -



.

Relevant Pages

  • Re: Employee hrs worked
    ... ProjectName (SubCategory) ... attributes of the entity type in question, e.g. Employees is an entity type ... work on many projects and each project will involve more than one employee ... referencing the primary keys of Employees and Projects. ...
    (microsoft.public.access.gettingstarted)
  • Re: Employee hrs worked
    ... attributes of the entity type in question, e.g. Employees is an entity type ... Assuming each employee might ... referencing the primary keys of Employees and Projects. ... the Timelog table to record this. ...
    (microsoft.public.access.gettingstarted)
  • RE: Employee hrs worked
    ... attributes of the entity type in question, e.g. Employees is an entity type ... Assuming each employee might ... referencing the primary keys of Employees and Projects. ... the Timelog table to record this. ...
    (microsoft.public.access.gettingstarted)