No duplicate date and time

[Guest]

I have on first two columns in table date and time.
How to prevent duplicate entry for data and time?

Example:
Date Time Data
25.7.2006 12:30 ...
26.7.2006 10:00 ...
26.7.2006 10:00 ...

I want in this case massage box with warning message if it's possible.
Thanks!

 

[Allen Browne]

1. In table design view, open the Indexes dialog (View menu.)

2. On a fresh row in the dialog, enter a name for the index, and the first
column name.

3. In the lower pane of the dialog, set Unique to Yes.

4. On the next row of the dialog, leave the Name column blank (since it's
part of the index named on the previous line), and enter the 2nd field name.
The dialog will now look like this:
UniqueDateTime Date
Time

That will give you a unique index on the combination of the 2 fields, so
Access won't allow duplicates.

However, there are several things that can go wrong here:
a) Date and Time are both reserved words, so not good field names. If that's
the real field names, consider renaming them.

b) In most cases, it would be more efficient to use a single date/time field
instead of the 2. In the lower pane of table design, you can set the field's
Indexed property to Yes (No Duplicates). Don't use the name DateTime: that's
a reserved word too.

c) A unique index on a date/time field may not work as expected. Times are
stored as floating point values, so it is possible for 2 times that display
the same to actually be different under the surface. If this happens, the
unique index won't work.

 

[Jamie Collins]

A unique index on a date/time field may not work as expected. Times are
stored as floating point values, so it is possible for 2 times that display
the same to actually be different under the surface. If this happens, the
unique index won't work.

For Access/Jet, the smallest granule of time is one second. I would
recommend the OP (and everyone else) ignores the finer points of the
Jet implementation. The fact that Jet (in the last/latest version)
stores datetime data as a double float is unimportant, as is the fact
UNIQUE constraints are implemented via indexes.

Always using Jet's temporal functions (DATEADD etc) should ensure the
one second granularity is not compromised.

If granularity of DATETIME values is an issue then there should be
constraints in the database accordingly e.g.

CREATE TABLE Test (
test_date NOT NULL UNQIUE,
CONSTRAINT test_date__one_second_granularity
CHECK (
test_date =
DATESERIAL(YEAR(test_date), MONTH(test_date), DAY(test_date))
+ TIMESERIAL(HOUR(test_date), MINUTE(test_date), SECOND(test_date))
)
);

The check should prevent sub-second values getting into the table e.g.
this

INSERT INTO Test VALUES (NOW() + 24/60/60/5);

should cause the CHECK to bite.

Procedures (parameter queries) can be employed to remove sub-second
elements e.g.

CREATE PROCEDURE TestProcAdd (
arg_date DATETIME
) AS
INSERT INTO Test (test_date)
SELECT DISTINCT
DATESERIAL(YEAR(arg_date), MONTH(arg_date), DAY(arg_date))
+ TIMESERIAL(HOUR(arg_date), MINUTE(arg_date), SECOND(arg_date))
AS test_date
FROM Test;

EXECUTE TestProcAdd #2006-07-28 10:40:56# + 24/60/60/5

does not cause the CHECK to bite.

Ensuring bad data cannot be present will mean the UNIQUE constraint
will be reliable.

However, for a time-valid state ('history') table, value-equivalence is
a weak form of duplication. There are three other flavours of
duplicates, 'sequenced duplicates', 'nonsequenced duplicates' and
'current duplicates'. See Snodgrass, 'Developing Time-Oriented Database
Applications in SQL', 5.5 P121
(http://www.cs.arizona.edu/~rts/tdbbook.pdf).

Jamie.

--