Hours Worked

[Pete]

I wish to keep a track of hours worked by employees, these can be for
various reasons e.g

Training
Sickness
Visits
Bereavement
Paternity

I thought a many to many relationship would do, but I am struggling to get
it to accept duplicate values. Tables I have are:

tblEmployee
ClockNo (Primary Key)
Name
Dept
Shift

tblHoursWorked
ClockNo (Foreign Key)
Reason (Foreign Key)
DateWkd
Comments

tblReason
Reason (Primary Key ) (e.g Training, Sickness etc..)

When I try to assign some hours worked to an employee, If they have already
worked some on Training, I get the error about duplicates. Both ClockNo &
Reason are set to accept Duplicates in tblHoursWorked

Any help would be appreciated

Pete

 

[Ken Snell [MVP]]

In tblHoursWorked, are you using ClockNo and Reason as a composite primary
key, or is there no primary key in that table?

The warning about duplicates suggests that you may have these two keys as a
composite primary key, which means that, even though each can accept
duplicates, there cannot be two records where the values of both fields in
combination are the same in both records (e.g., ClockNo value is 1 and
Reason value is 1 in one record, so no other record can have that same
combination).

What is DateWkd in tblHoursWorked table? I don't see a field that has a name
that seems to correspond with "hours worked" that you say you're entering
into this table?

 

[Pete]

If I'm honest, I don't know, I am fairly new to Access
and just struggling along.

ClockNo & Reason are the only "Keys" in that table, I'm
not sure what a composite key is.

DateWkd is the Date the additional hours were worked.

Can you help me set the tables up to make this work, even
if I have to start again, I'm not bothered.

Thanks

Pete

 

[Ken Snell [MVP]]

Let's start with tblHoursWorked table. Open it in design view.

Do any of the fields have a Key icon at the left side of the field column at
far left of the screen? If yes, which one or ones?

Click on the Index icon on toolbar (looks like it has a lightning bolt on
right side of vertical column of lines). Post in your reply the information
that you see there (there will be three columns, along with the possibility
of a Key icon on far left side of one or more field columns).

 

[Pete]

Sorted it, I have removed the foreign keys from
tblHoursWorked, and assigned an autonumber primary key.

The error message was coming because I was trying to
create a duplicate "Reason" which when I think about it
you cannot do. Couldn't see the wood for the trees.

Thanks for your help

Peter