Prevent Duplicates Under Certain Conditions

[rn5a]

An Access DB table has the following columns:

CID - int
DateAvail - date/time
StartTime - date/time
EndTime - date/time
Seats - int

What I want is something like:

I want the table to be designed in such a way so that there can be
multiple same date records under the DateAvail column but the
corresponding StartTime & EndTime values shouldn't be multiple. For
e.g. the following records under the DateAvail, StartTime & EndTime
columns are permissible (dates are in mm/dd/yyyy format):

DateAvail StartTime EndTime

05/15/2007 7:00:00 AM 9:00:00 AM
05/15/2007 9:00:00 AM 11:00:00 AM
05/15/2007 11:00:00 AM 1:00:00 PM

No duplicate StartTime & EndTime values on 15th May but the following
shouldn't be permissible:

DateAvail StartTime EndTime

05/15/2007 7:00:00 AM 9:00:00 AM
05/15/2007 7:00:00 AM 10:00:00 AM
05/15/2007 7:00:00 AM 11:00:00 PM

since 7:00:00 AM under the StartTime column repeats for 15th May 2007
but if the date under DateAvail column is different, then duplicate
values should be allowed under the StartTime & EndTime columns. For
e.g.the following records are permissible:

DateAvail StartTime EndTime

05/15/2007 7:00:00 AM 9:00:00 AM
05/15/2007 9:00:00 AM 11:00:00 AM
05/15/2007 11:00:00 AM 1:00:00 PM

05/17/2007 7:00:00 AM 9:00:00 AM
05/17/2007 9:00:00 AM 11:00:00 AM
05/17/2007 11:00:00 AM 1:00:00 PM

(left the blank space to distinguish between 15th May & 17th May
easily). The above records are permissible because though there are
duplicate StartTime & EndTime values, the DateAvail values
corresponding to the StartTiime & EndTime values are different..

So how do I design the DB table to ensure that there aren't duplicate
values under the StartTime & EndTime columns if the corresponding
DateAvail dates are the same but if the DateAvail dates corresponding
to the StartTime & EndTime values are different, then the DB table
should allow duplicates under the StartTime & EndTime columns?

In other words, the period between 7:00:00 AM to 9:00:00 AM occurs
only once each day; hence 7:00:00 AM under the StartTime column &
9:00:00 AM under the EndTime column should not be duplicated for the
same date (say, 15th May) in the DateAvail column but each day has a
period between 7:00:00 AM to 9:00:00 AM. So if the dates under the
DateAvail column are different, then StartTime & EndTime values can be
duplicated.

 

[EeOr]

Not sure exactly of the different permutations you are talking about, lets
say:

You cant have 2 records that have the same date and start time:
Create a multi field index on the table, based on the DateAvail field and
the StartTime fields
(In table design view go to view, indexes - on the first blank row under
index name enter a name for the index, on the same row select DateAvail
field and set unique to yes under index properties on the row under this
select only the StartTime field - leave the rest of the columns blank)

If you test this you will not be allowed to enter duplicate start dates
where the date field is the same.

If you want it so you are not allowed the same date and end time do the same
as above starting on the next blank row and selecting the EndTime field
where you selected StartTime above.

Your index table should now look something like this:

Index Name Field Name Sort Order
Primary Key ID Ascending if you
have a PK on your table
Date and start DateAvail Ascending
StartTime Ascending
Date and End DateAvail Ascending
EndTime Ascending



If you test this again you should not be allowed to enter either a duplicate
start or end time on any given date.

Hope this is clear enough for you.

Jon

 

[rn5a]

Not sure exactly of the different permutations you are talking about, lets
say:

You cant have 2 records that have the same date and start time:
Create a multi field index on the table, based on the DateAvail field and
the StartTime fields
(In table design view go to view, indexes - on the first blank row under
index name enter a name for the index, on the same row select DateAvail
field and set unique to yes under index properties on the row under this
select only the StartTime field - leave the rest of the columns blank)

If you test this you will not be allowed to enter duplicate start dates
where the date field is the same.

If you want it so you are not allowed the same date and end time do the same
as above starting on the next blank row and selecting the EndTime field
where you selected StartTime above.

Your index table should now look something like this:

Index Name Field Name Sort Order
Primary Key ID Ascending if you
have a PK on your table
Date and start DateAvail Ascending
StartTime Ascending
Date and End DateAvail Ascending
EndTime Ascending

If you test this again you should not be allowed to enter either a duplicate
start or end time on any given date.

Hope this is clear enough for you.

Jon





















- Show quoted text -

Thanks a lot, Jon, for your elaborate explanation. Your suggestion
does what I was looking out for but honestly speaking, I am getting a
bit confused with multi-field index.

You created a multi-field index with the DateAvail & StartTime
columns. Does that mean if date records under the DateAvail column is
the same, then records under the StartTime column can't be same? Only
if date records under DateAvail is different, then only duplicates
will be allowed in the StartTime column by Access.

RON

 

[rn5a]

Not sure exactly of the different permutations you are talking about, lets
say:

You cant have 2 records that have the same date and start time:
Create a multi field index on the table, based on the DateAvail field and
the StartTime fields
(In table design view go to view, indexes - on the first blank row under
index name enter a name for the index, on the same row select DateAvail
field and set unique to yes under index properties on the row under this
select only the StartTime field - leave the rest of the columns blank)

If you test this you will not be allowed to enter duplicate start dates
where the date field is the same.

If you want it so you are not allowed the same date and end time do the same
as above starting on the next blank row and selecting the EndTime field
where you selected StartTime above.

Your index table should now look something like this:

Index Name Field Name Sort Order
Primary Key ID Ascending if you
have a PK on your table
Date and start DateAvail Ascending
StartTime Ascending
Date and End DateAvail Ascending
EndTime Ascending

If you test this again you should not be allowed to enter either a duplicate
start or end time on any given date.

Hope this is clear enough for you.

Jon





















- Show quoted text -

Jon,

I would like to add 3 other columns named ClassID, TeacherID & VenueID
along with the index you have suggested so that duplicates aren't
allowed.

For e.g. TeacherID=4 takes a class whose ClassID=1 at a venue whose
VenueID=2 on 15th May 2007 between 8 AM to 10 AM. This record is
present in the Access DB table in this way:

-------------------------------------------------------------------------------------------------------------------------
ClassID TeacherID VenueID DateAvail
StartTime EndTime
-------------------------------------------------------------------------------------------------------------------------
1 4 2
5/15/2007 8:00:00 AM 10:00:00 AM
-------------------------------------------------------------------------------------------------------------------------

Now it's quite obvious that TeacherID=4 cannot teach any class other
than ClassID=1 at some venue apart from VenueID=2 on 15th May 2007
between 8 AM to 10 AM. For e.g. this isn't permissible:

-------------------------------------------------------------------------------------------------------------------------
ClassID TeacherID VenueID DateAvail
StartTime EndTime
-------------------------------------------------------------------------------------------------------------------------
1 4 2
5/15/2007 8:00:00 AM 10:00:00 AM
2 4 3
5/15/2007 8:00:00 AM 10:00:00 AM
-------------------------------------------------------------------------------------------------------------------------

but this should be permissible since TeacherID=4 can teach ONLY ONE
CLASS at ONE VENUE at any given date & time.:

-------------------------------------------------------------------------------------------------------------------------
ClassID TeacherID VenueID DateAvail
StartTime EndTime
-------------------------------------------------------------------------------------------------------------------------
1 4 2
5/15/2007 8:00:00 AM 10:00:00 AM
2 4 3 5/15/2007
11:00:00 AM 1:00:00 PM
-------------------------------------------------------------------------------------------------------------------------

In the above table (with the permissible records), TeacherID=4 will
teach ClassID=2 at VenueID=3 on 15th May 2007 but since the StartTime
value & EndTime value is different from the 1st record, the above
records should be allowed.

Now how do I modify the multi-field index to ensure that such
duplicate records aren't accepted?

Thanks,

Regards,

RON

 

[rn5a]

Jon,

I would like to add 3 other columns named ClassID, TeacherID & VenueID
along with the index you have suggested so that duplicates aren't
allowed.

For e.g. TeacherID=4 takes a class whose ClassID=1 at a venue whose
VenueID=2 on 15th May 2007 between 8 AM to 10 AM. This record is
present in the Access DB table in this way:

---------------------------------------------------------------------------­----------------------------------------------
ClassID TeacherID VenueID DateAvail
StartTime EndTime
---------------------------------------------------------------------------­----------------------------------------------
1 4 2
5/15/2007 8:00:00 AM 10:00:00 AM
---------------------------------------------------------------------------­----------------------------------------------

Now it's quite obvious that TeacherID=4 cannot teach any class other
than ClassID=1 at some venue apart from VenueID=2 on 15th May 2007
between 8 AM to 10 AM. For e.g. this isn't permissible:

---------------------------------------------------------------------------­----------------------------------------------
ClassID TeacherID VenueID DateAvail
StartTime EndTime
---------------------------------------------------------------------------­----------------------------------------------
1 4 2
5/15/2007 8:00:00 AM 10:00:00 AM
2 4 3
5/15/2007 8:00:00 AM 10:00:00 AM
---------------------------------------------------------------------------­----------------------------------------------

but this should be permissible since TeacherID=4 can teach ONLY ONE
CLASS at ONE VENUE at any given date & time.:

---------------------------------------------------------------------------­----------------------------------------------
ClassID TeacherID VenueID DateAvail
StartTime EndTime
---------------------------------------------------------------------------­----------------------------------------------
1 4 2
5/15/2007 8:00:00 AM 10:00:00 AM
2 4 3 5/15/2007
11:00:00 AM 1:00:00 PM
---------------------------------------------------------------------------­----------------------------------------------

In the above table (with the permissible records), TeacherID=4 will
teach ClassID=2 at VenueID=3 on 15th May 2007 but since the StartTime
value & EndTime value is different from the 1st record, the above
records should be allowed.

Now how do I modify the multi-field index to ensure that such
duplicate records aren't accepted?

Thanks,

Regards,

RON- Hide quoted text -

- Show quoted text -

Can someone please help me out with my follow-up? I need a solution
desperately. Please I beg you please help me please.....

RON

 

[EeOr]

A multi field index will ensure that you cannot have the same combination of
values in a specified set of fields in any table. Therefore for the
purposes you are listing below it looks as though you will need to remove or
alter the indexes I have listed, you will need to set up various indexes as
follows:

1: DateAvail, StartTime, TeacherID
2: DateAvail, StartTime, VenueID
3: DateAvail, StartTime, ClassID

I don't think that you need to index the EndTime at all if the blocks are
always in 2 hours, infact do you even need to store them in your table at
all, can these not be calculated fields on your forms, reports or even
queries?

If you create the 3 indexes listed above and set each of their properties so
that nulls are not allowed you should be allowed the following:

Date Start Teacher Class Venue
15/01/07 08:00 4 1 1
15/01/07 08:00 3 2 3
15/01/07 10:00 4 2 3
16/01/07 08:00 4 1 3

However the following would not be allowed

Date Start Teacher Class Venue Reason
15/01/07 08:00 4 1 1
15/01/07 08:00 3 1 3 Class booked
at same time/date
15/01/07 08:00 1 2 1 Venue booked
at same time/date
16/01/07 08:00 4 1 3 Teacher and
class booked out.

I hope this now makes more sense to you, if not then try Google for multi
fields indexes. Although if it is getting any more complicated you may need
to start trying to validate this at the form level using VBA as just now
your user will be given a standard error message if a user try's to enter a
duplicate but this will not specify what fields are duplicated.

Hth

Jon

 

[rn5a]

A multi field index will ensure that you cannot have the same combinationof
values in a specified set of fields in any table. Therefore for the
purposes you are listing below it looks as though you will need to removeor
alter the indexes I have listed, you will need to set up various indexes as
follows:

1: DateAvail, StartTime, TeacherID
2: DateAvail, StartTime, VenueID
3: DateAvail, StartTime, ClassID

I don't think that you need to index the EndTime at all if the blocks are
always in 2 hours, infact do you even need to store them in your table at
all, can these not be calculated fields on your forms, reports or even
queries?

If you create the 3 indexes listed above and set each of their propertiesso
that nulls are not allowed you should be allowed the following:

Date Start Teacher Class Venue
15/01/07 08:00 4 1 1
15/01/07 08:00 3 2 3
15/01/07 10:00 4 2 3
16/01/07 08:00 4 1 3

However the following would not be allowed

Date Start Teacher Class Venue Reason
15/01/07 08:00 4 1 1
15/01/07 08:00 3 1 3 Class booked
at same time/date
15/01/07 08:00 1 2 1 Venue booked
at same time/date
16/01/07 08:00 4 1 3 Teacher and
class booked out.

I hope this now makes more sense to you, if not then try Google for multi
fields indexes. Although if it is getting any more complicated you may need
to start trying to validate this at the form level using VBA as just now
your user will be given a standard error message if a user try's to entera
duplicate but this will not specify what fields are duplicated.

Hth

Jon














- Show quoted text -

Jon, I am running short of words to express my gratitude to you. You
have hit the nail on the head. It's now working exactly as how I
wanted but a conflict still exists. Consider the following 2 records:

TeacherID ClassID VenueID DateAvail StartTime EndTime
1 1 1 5/15/2007 6:00 AM
8:00 AM
2 1 1 5/15/2007 7:00 AM
9:00 AM

The 1st row says that TeacherID=1 will teach ClassID=1 at VenueID=1 on
15th May, 2007 from 6AM to 8AM. The 2nd row says that TeacherID=2 will
teach ClassID=1 at VenueID=1 on 15th May 2007 from 7AM to 9AM. Now
ClassID=1 is already booked on 15th May 2007 from 6AM to 8AM which
means at 7AM, ClassID=1 will be taught by TeacherID=1. So how can
TeacherID=2 start a class from 7AM?

How do I overcome this conflict?

Also Jon, what logic did you apply to come up with the modified
indexes? If I create just 1 multi-field index using all the columns,
then it doesn't work but your indexes work fine. Why?

Since last 2 days, I have gone through innumerable articles on multi-
field indexes in MS-Access after googling but none of them explained
how indexes work in Access, all they explained was how to create multi-
field indexes.

Jon, you have put in a lot of efforts & your invaluable time to help
me out & I really appreciate it from the bottom of my heart. Thanks a
lot of doing so much favor on me.

Regards,

RON