Solved

Avoid Duplicate entries

Posted on 2013-06-21
9
465 Views
Last Modified: 2013-06-26
Hello,

I have a table with the following fields to monitor Employee leave within my organization:
ID, Employee,dateLeaveStart, DateLeaveFinish, Days, LeaveType, [attachememt]

When I enter new records (i'm using the datasheet view for now), I'd like to restrict entering new records where EMPLOYEE and (dateLeaveStart OR DateLeaveFinish) match.

The idea is that if I have already marked one employee as sick for a particular day (or range of dates), I would not be able to enter another record for the same dates.

appreciate any help you can give!
0
Comment
Question by:colin911
  • 4
  • 3
  • 2
9 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39266496
Create two unique indexes for the table.
The first index will include the Employee column and the dateLeaveStart column.  Mark it as unique.
The second will include the Employee column and the dateLeaveFinish column.  Mark it as uniqe.
This will prevent entries where the Employee and dateLeaveStart OR Employee and dateLeaveFinish already exist.

OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39266503
Here's what the dialog should look like.
OM Gang

Indexes
0
 

Author Comment

by:colin911
ID: 39266714
Omgang,

thank you for that.  Everything works as you described, however I was hoping to keep new entries for dates between the start and finish dates.

so if an employee was sick from june 1 to june 10, an entry could not be made for a start date of june 1, and end date of june 10, or any start or finish days between june1 and june 10.

Also, is there a way to customize the error message when this error occurs?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 28

Expert Comment

by:omgang
ID: 39266738
You can't do what you're wanting from a table datasheet view.  If you are willing to use a form datasheet, bound to the table, then you can perform validation on the records before they are updated and display custom messages to the user.
OM Gang
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39266759
<so if an employee was sick from june 1 to june 10, an entry could not be made for a start date of june 1>

so this entry from jun1 to june 10 is classified as sick leave, is that correct?
if so, add another field to the index, say the field where you enter "sick leave" or "vacation"
This field can be named "Description"
0
 

Author Comment

by:colin911
ID: 39266810
I am willing to use a form but not very familiar with them (I want to learn though)

The entry from June1 to June 10 has the example of sick leave, but it should not matter the leave type because if the leave was sick leave, it can;t ALSO be vacation leave...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39266882
<but it should not matter the leave type because if the leave was sick leave, it can;t ALSO be vacation leave... >
off course, but that was just an explanation..


if you have a third field in the index, you can be able to enter records with dates from june 1 to june 10 with a different description..
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39266901
@colin911, I think I understand what you are wanting.  Please take a look at the attached sample database.  Open Form1 and attempt to add a record for you (or me) with a start data between the StartDate or EndDate of an existing record.  I've added a BeforeUpdate event to the form that will display a custom message.  Let me know if you have any questions or if this isn't what you're after.
OM Gang
EmpLeave.mdb
0
 

Author Closing Comment

by:colin911
ID: 39279810
thank you!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question