Preventing duplicate numbers between two fields

  • Thread starter Thread starter WilliamE
  • Start date Start date
W

WilliamE

I have two fields that are a range of numbers. I want to prevent any of the
numbers in the range from happening twice in the same month. How can i do
this.
This is Access 2003.

Example:

200 250 happens on Feb 1st
190 240 Happens on Feb 28th

In this example 200 through 240 have happened twice.
 
The ranges in two records overlap if both:
Low value of A is less than the high value of B, AND
Low value of B is less than the high value of A.
In your case, you also want to restrict it to records where year and month
are the same.

Let's assume the table is named tblEvent, with fields EventMin, EventMax,
and EventDate.

1. Create a table with 2 copies of tblEvent.
Access will alias the 2nd copy as tblEvent_1

2. Drag tblEvent.EventMin into the grid.
In the Criteria row under this field, enter:
< tblEvent_1.EventMax

3. Drag tblEvent_1.EventMin into the grid.
In the Criteria row under this field, enter:
< tblEvent.EventMax

4. In the upper pane of table design, drag tblEvent.EventDate and drop it
onto tblEvent_1.EventDate. Access will create a line joining the 2 tables.

5. Switch the query to SQL View (View menu.)
You will see the query statment like this:
SELECT ...
FROM ...
WHERE ...

6. Change the FROM clause so it looks like this:
FROM tblEvent.EventDate INNER JOIN tblEvent1_Event
ON Year(tblEvent.EventDate) = Year(tblEvent1_Event)
AND Month(tblEvent.EventDate) = Month(tblEvent1_Event)

After step 6, you will not be able to switch back to design view. There are
other alternatives, but I think this will be the most efficient way to
execute the query.
 
Back
Top