Index based on 2 fields (No Duplicates)

In the table there are 2 fields in which I wish to limit (i.e. No
Duplicates)

Although I do not want to limit them to "No Duplicates" separately.
I need them to be limited to "No Duplicates" as if they were one
field.

The 2 fields are "Employee_Name" & "Training_Course".
*(There is another table for Employees & another for Training Courses,
both of which are related to this table.)

Each employee can be enrolled multiple courses, hence I have to allow
duplicates for both fields.
Although if someone "accidently" adds an employee to a course they are
already enrolled in, I end up getting a duplicate record.

Example:

John Doe - Course 1 ....
John Doe - Course 2 (name - duplicated) (OK)
Mary Jane - Course 1 (course - duplicated) (OK)
Mary Jane - Course 2 (name or course - duplicated) (OK)
Mary Jane - Course 2 (name & course - duplicated) (BAD)

Can I somehow add a "No Duplicates" clause to these 2 fields as if
they were 1?
I am using Access 2000 SP2.
Thanks

Re: Index based on 2 fields (No Duplicates)

First off you do NOT want to store the EmployeeName or the CourseName
in the '3rd' table. That defeats the whole purpose of relational
databases. You want to use a primary key in each of those tables
(often a random autonumber) and then store that primary key value from
each of the main tables in the "3rd" table. Your data is not
normalized doing things in the manner you have it. Fix that before
moving forward.



On Jan 8, 2:14=A0pm, "ryan.paque...@gmail.com"
wrote:
> In the table there are 2 fields in which I wish to limit (i.e. No
> Duplicates)
>
> Although I do not want to limit them to "No Duplicates" separately.
> I need them to be limited to "No Duplicates" as if they were one
> field.
>
> The 2 fields are "Employee_Name" & "Training_Course".
> *(There is another table for Employees & another for Training Courses,
> both of which are related to this table.)
>
> Each employee can be enrolled multiple courses, hence I have to allow
> duplicates for both fields.
> Although if someone "accidently" adds an employee to a course they are
> already enrolled in, I end up getting a duplicate record.
>
> Example:
>
> John Doe - Course 1 =A0 =A0 ....
> John Doe - Course 2 =A0 =A0 (name - duplicated) (OK)
> Mary Jane - Course 1 =A0 (course - duplicated) (OK)
> Mary Jane - Course 2 =A0 (name or course - duplicated) (OK)
> Mary Jane - Course 2 =A0 (name & course - duplicated) (BAD)
>
> Can I somehow add a "No Duplicates" clause to these 2 fields as if
> they were 1?
> I am using Access 2000 SP2.
> Thanks

Re: Index based on 2 fields (No Duplicates)

So, on the subforms, that display this "3rd" table, how would I show
the employee names if they are not on the table?
Also the reports generated from this table.?. they would all display
ID fields that mean nothing to the user.
That is why I added the actual name as opposed to the autonumber ID
fields...

Once I do make the change you requested, how would I limit the records
to no duplicates?
(for both fields together as mentioned below)

On Jan 8, 2:20=A0pm, frogste...@yahoo.com wrote:
> First off you do NOT want to store the EmployeeName or the CourseName
> in the '3rd' table. =A0That defeats the whole purpose of relational
> databases. =A0You want to use a primary key in each of those tables
> (often a random autonumber) and then store that primary key value from
> each of the main tables in the "3rd" table. =A0Your data is not
> normalized doing things in the manner you have it. =A0Fix that before
> moving forward.
>
> On Jan 8, 2:14=A0pm, "ryan.paque...@gmail.com"
> wrote:
>
>
>
> > In the table there are 2 fields in which I wish to limit (i.e. No
> > Duplicates)
>
> > Although I do not want to limit them to "No Duplicates" separately.
> > I need them to be limited to "No Duplicates" as if they were one
> > field.
>
> > The 2 fields are "Employee_Name" & "Training_Course".
> > *(There is another table for Employees & another for Training Courses,
> > both of which are related to this table.)
>
> > Each employee can be enrolled multiple courses, hence I have to allow
> > duplicates for both fields.
> > Although if someone "accidently" adds an employee to a course they are
> > already enrolled in, I end up getting a duplicate record.
>
> > Example:
>
> > John Doe - Course 1 =A0 =A0 ....
> > John Doe - Course 2 =A0 =A0 (name - duplicated) (OK)
> > Mary Jane - Course 1 =A0 (course - duplicated) (OK)
> > Mary Jane - Course 2 =A0 (name or course - duplicated) (OK)
> > Mary Jane - Course 2 =A0 (name & course - duplicated) (BAD)
>
> > Can I somehow add a "No Duplicates" clause to these 2 fields as if
> > they were 1?
> > I am using Access 2000 SP2.
> > Thanks- Hide quoted text -
>
> - Show quoted text -

Re: Index based on 2 fields (No Duplicates)

On Jan 8, 2:39=A0pm, "ryan.paque...@gmail.com"
wrote:
> So, on the subforms, that display this "3rd" table, how would I show
> the employee names if they are not on the table?
> Also the reports generated from this table.?. they would all display
> ID fields that mean nothing to the user.
> That is why I added the actual name as opposed to the autonumber ID
> fields...
>
> Once I do make the change you requested, how would I limit the records
> to no duplicates?
> (for both fields together as mentioned below)
>

Your forms should draw data from queries. Build a query that grabs
the data from where it is stored.


Assume this:
tEmployee---
EmployeeID
EmployeeName

tCourse---
CourseID
CourseName

tEmployeeCourse
EmployeeID
CourseID

Build the obvious relationships. Build a query that uses all three
tables if you want to 'show' the EmployeeName and CourseName fields.
Use SQL to create the multi-field unique key. Use combo boxes to
select a valid EmployeeName and a valid CourseName. Once you have the
multi-field key defined Access will give you the standard warning if
you attempt to add a duplicate record.