Access No duplicates on Start time, End time and Time in between

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I created an access database for car reservation and am new at this.

How can I avoid the booking of the same car on the same day at the same
time, I have two fields "start" and "end" but if I create an index to not
allow duplicate on the index (car, date, start, and end) it doesn't count the
times in between start and end, how can I fix this.

Sorry about my ignorance, I'm self-taught and am trying to put this together
for my employer.

Thanks in advance for your help!
 
You can't just use a index to prevent this type of problem.

You will need your booking form to simply "look" for a collsions.

And, to prevent collisions, the logic here is quite simple:

A collision occurs when:

RequestStartDate <= EndDate
and
RequestEndDate >= StartDate

The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. Howver, what is nice here is that
the simple condistion above does return ANY collsion....
 
Florence said:
I have two fields "start" and "end" but if I create an index to not
allow duplicate on the index (car, date, start, and end) it doesn't count the
times in between start and end
Click to expand...

<quote>
There are four kinds of duplicates [in a time-valid state tables]...
[1] Two rows are 'value-equivalent' is the values of their
non-TIMESTAMP columns are identical. Value equivalence is a weak form
of duplication [for a time-valid state table]...
[2] Two rows are 'sequenced duplicates' if they are duplicates at some
instant...the values of the non-TIMESTAMP columns...are value
equivalent and the periods of validity overlap... As with primary keys,
the adjective 'sequenced' means that the operation or constraint is
applied independently at every point in time....
[3] A variant of sequenced duplicate is a 'current duplicate', in which
there are duplicate rows in the current state [current instant]...
Interestingly, whether a table contains current duplicate rows can
change over time, even if no modifications are made to the table...
[4] Two rows are 'nonsequenced duplicates' if the values of all columns
are identical... This adjective emphasizes that the property (in this
case, duplicates) is not applied independently at each point in time,
but rather is applied to the table as a whole, ignoring its temporal
nature.

</quote>

Snodgrass, 'Developing Time-Oriented Database Applications in SQL'
(http://www.cs.arizona.edu/~rts/tdbbook.pdf), 5.5 P121-122 (PDF
P145-146)

You can use a Jet 4.0 table-level CHECK constraint to ensure there are
no overlapping periods. I posted an example recently (before I'd read
the above book!), using salary history, which you should be able to
adapt:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (0 = (
SELECT *
FROM EarningsHistory, EarningsHistory AS E2
WHERE EarningsHistory.employee_nbr = E2.employee_nbr
AND EarningsHistory.start_date < E2.start_date
AND
(
E2.start_date
< IIF(EarningsHistory.end_date IS NULL, NOW(),
EarningsHistory.end_date)
OR IIF(E2.end_date IS NULL,
NOW(),
E2.end_date) < IIF(EarningsHistory.end_date IS NULL,
NOW(), EarningsHistory.end_date)
)
)
);

I posted a challenge yesterday to see if anyone could implement the
primary key without a table-level CHECK constraint, as yet no takers...

Jamie.

--
 
Back
Top