Ms-access – How to Prevent Timeslot Overlap

ms access

SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about:

BookDate
BookStart
BookEnd

I'm trying to prevent the timeslots from overlapping. To do so, I've managed to bind the 3 attributes together into a composite key, which prevents identical entries from being entered. However, if 2 records like so are entered:
```
24/05/18 10:00am 10:15am

24/05/18 10:00am 10:30am
```

The records are accepted into the system. How do I prevent this? I'm trying to use data validation field right now (Help with preventing double bookings with variable lengths), but I'm not quite sure how to link 2 attributes together within it.

Thanks in advance!

Best Answer

You can only achieve such validation at the database level (when not using forms and VBA) using a CHECK constraint

ALTER TABLE Bookings 
ADD CONSTRAINT NoDoubleBookings CHECK(
       NOT EXISTS(
             SELECT 1 
             FROM Bookings b1, Bookings b2 
             WHERE b1.BookDate + b1.BookStart < b2.BookDate + b2.BookStart 
             AND b1.BookDate + b1.BookEnd > b2.BookDate + b2.BookStart
       )
)

More details on adding check constraints to Access databases can be found here

Related Solutions

Ms-access – How to shift a value one record ‘down’

This will do the trick in recent versions of SQL Server and Oracle:

SELECT ID, Flag,
  LAG(Flag) OVER(ORDER BY id) AS ShiftFlag
FROM tab
ORDER BY id;

If you don't have LAG(), for example in MS SQL Server 2008, you can use ROW_NUMBER() like this:

;WITH num AS (
  SELECT ID, Flag, ROW_NUMBER() OVER(ORDER BY id) AS n FROM tab
)
SELECT t1.ID, t1.Flag, t2.Flag AS ShiftFlag
FROM num t1
LEFT OUTER JOIN num t2 ON t2.n = t1.n - 1
ORDER BY t1.ID;

Tested on SQLFiddle.

Microsoft Access – Defining Destination for Append Query

If MainTable has more that one colum (I guess it does), then you need to specify it like that:

INSERT INTO MainTable(ColumnName)

Also you have to specify the columns in the SELECT STATEMENT:

SELECT Column1, Column2
FROM TABLE2....

Best Answer

Related Solutions

Ms-access – How to shift a value one record ‘down’

Microsoft Access – Defining Destination for Append Query

Related Question