Solved

Avoid Duplicate entries

Posted on 2013-06-21
9
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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