Data validation involving 2 tables

[Jody]

Hi

I have a challenge involving 2 tables - 'tblcompetitions' and 'tblEnrollment'. tblcompetitions has 3 fields in question - CompetitionID, Date and Session. tblEnrollment has 2 fields in question - CompetitionID and EntrantID. The tables are related - 1 competition can have many enrollments. A session can be 1 of 3 values - morning, afternoon, evening

An entrant cannot be scheduled for more than 1 competition on the same date and session

I envision entrant selection being a subform with the main form showing the tblcompetition fields. Under the hood, in tblEnrollment, When an EntrantID data element is about to be entered, I need to check to make sure the entrantID is not already associated with a competitionID on the same date and in the same session

Any suggestions

Thanks
Jody

 

[Jeff Boyce]

Jody

Are you saying that the combination of an entrant and competition must be
unique?

If your "competitions" are unique (e.g., you can't have more than one
competition on the same Date&Session combination), it would seem that you
could add a Unique index to the tblEnrollment over the combination of the
two fields.

By the way, if your competition's date field is named "Date", consider
changing the name - "Date" is a reserved word in Access and could confuse
you or Access.

 

[Jeff Boyce]

Jody

It sounds as if the "date" and "session" may be misplaced, then. If
date/time and session are characteristics of the entrant's choice of
competition, then they belong with the enrollment.

If these are characteristics of a specific competition, then they belong
where you have them (but I didn't see any "description" or "title" field in
tblCompetition).

If you need to ensure that someone isn't already signed up for a
date/time/session, you'll need to have a way to make that combination
unique, no duplicates (or, I suppose you could also write a query that looks
up the proposed person/date/session info -- if the query returns 0 rows,
proceed).

Or another, more kludgey approach might be to provide a list of all the
currently-signed-up-for competitions when you pick a person. I'm not
suggesting this one, though, because it would force your user to read a list
to see if it would be OK to sign up.

Just ideas -- you are closer to your data and needs than I am.

 

[Jody]

Hi Jeff

Thanks again for your suggestions. Please see below for some more comments

It sounds as if the "date" and "session" may be misplaced, then. I
date/time and session are characteristics of the entrant's choice o
competition, then they belong with the enrollment

If these are characteristics of a specific competition, then they belon
where you have them (but I didn't see any "description" or "title" field i
tblCompetition)

Date and Session are characteristics at the competition level - entrants will have to fit into morning, afternoon or evening competitions

If you need to ensure that someone isn't already signed up for
date/time/session, you'll need to have a way to make that combinatio
unique, no duplicates (or, I suppose you could also write a query that look
up the proposed person/date/session info -- if the query returns 0 rows
proceed)

From what I can see, this suggestion could only work if date and session were at the entrant level, which they are not. So I don't see a way of using a composite primary key mechanism in tblenrollment to enforce this business rule.

Keeping in mind that each record in the enrollment table is made up of only CompetitionID and EntrantID, (a junction table), the steps of the code would be something like this

When entering an entrantID in the tblenrollment table

(1) determine the competitionID in the same record just being recorded in tblenrollmen
(2) check the tblenrollment table for all previous occurences of the entrantID in questio
(3) determine the competitionID's associated with these entrantID
(4) and then check the tblcompetition table to verify that the competitionID from step 1 does not have a conflicting date and session with any other of the discovered competitionIDs from step 4

The problem is I don't know how to turn this into code that can be called when attempting to select an entrantID. That is what I need help to do

Any idea how I could use code or some other mechanism to achieve this

Thanks
Jody

 

[Jeff Boyce]

Jody

(see in-line)

Date and Session are characteristics at the competition level - entrants

will have to fit into morning, afternoon or evening competitions.
From my (albeit limited) understanding, your competitions can have more than
one "session". To make this a little more concrete, I'll invent a
situation...

I support 50+ Senior Games. The 50 meter dash has so many entrants that I'm
going to offer this event (?competition) in three "flights", morning, noon,
afternoon.

My competition/event is the "50 meter dash". Specific instances of it (in a
1-many relationship from the competition) are "50 meter
dash-in-the-morning", "50 meter dash-at-noon", "50 meter dash-in-afternoon".

My job for scheduling is to sign people up for one of these "sessions".

So, I either need to create a competiton (50 meter dash), three competition
sessions (50-am, 50-noon, ...) and a entrant (Jeff-in-50-afternoon), OR

I can have a competition (50 m), participant (Jeff), and entrant (putting 50
m, Jeff, and time-of-session together).

Which will work better for you?

From what I can see, this suggestion could only work if date and session

were at the entrant level, which they are not. So I don't see a way of
using a composite primary key mechanism in tblenrollment to enforce this
business rule.

Notice that the second method I outlined gives you a way to ensure that no
participant has more than one competition at the same time, by the simple
matter of creating a unique index on participant AND session.

Keeping in mind that each record in the enrollment table is made up of

only CompetitionID and EntrantID, (a junction table), the steps of the code
would be something like this:

When entering an entrantID in the tblenrollment table:

(1) determine the competitionID in the same record just being recorded in tblenrollment
(2) check the tblenrollment table for all previous occurences of the entrantID in question
(3) determine the competitionID's associated with these entrantIDs
(4) and then check the tblcompetition table to verify that the

competitionID from step 1 does not have a conflicting date and session with
any other of the discovered competitionIDs from step 4

The problem is I don't know how to turn this into code that can be called

when attempting to select an entrantID. That is what I need help to do.
The second structure requires no code. Access ensures there are no
participants scheduled for more than one session simultaneously, via the
unique index.

Or am I still missing something...?

 

[Jody]

Hi Jeff,

Thanks very much for your suggestions. I think I'll be able to rework my design based on this info.

Cheers,
Jody