Solved

Avoid Duplicate entries

Posted on 2013-06-21
9
458 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now