Access Question - Preventing Duplicate values in table based onmulitple data columns

[cmccluskey]

Hello,

I have created a timesheet for a small business. The timesheet works
well, except we occasionally have problems with employees entering
their time twice for the same day. The configuration is basically
date worked, employee name, total hours and then several sections with
sub categories for hours worked (IT/Retail/Etc.)

The problem I am having is figuring out how to prevent duplicate
entries. If I try to make the date or employee filed unique, I have
problems as several employees may work the same date and employees
work more than one date. Does that make sense at all?

I am wanting to prohibit duplicate entries for a given employee on a
given date only. Can anyone help with this?

Thanks!
Chad

(e-mail address removed)

 

[Tom van Stiphout]

On Mon, 22 Jun 2009 14:35:10 -0700 (PDT), cmccluskey

If you have a correct db design, you should be able to come up with a
unique index that works. Your design appears to have some problems,
and that's what's biting you now. You have to think in relational
terms; Access is NOT Excel on steroids.

Some of the problems I see:
* EmployeeName in the table with dates worked. That should be
EmployeeID, linked to an Employees table.
* TotalHours is a calculated field; they don't belong in a relational
database, but rather should be calculated using a Totals query.
* It appears you have additional fields for HoursWorkedInIT,
HoursWorkedInRetail, etc. That creates a "Repeating Group" that should
be spun off in its own table.

Once you have the correct design, the unique index will fall in place
automatically. At Kinetik IT we have a timesheet application where we
added BeforeUpdate logic to check for overlapping time. It prevents
someone from entering for example 6/22/2009 10:00 - 11:00 for one
task, and 10:30 - 11:00 for another task. That was actually a fair
amount of work; essentially you need to work out all the permutations
on paper, then write code for each.

-Tom.
Microsoft Access MVP