By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Index based on 2 fields (No Duplicates)

P: n/a
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
Jan 8 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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*pm, "ryan.paque...@gmail.com" <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 * * ....
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
Jan 8 '08 #2

P: n/a
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*pm, frogste...@yahoo.com wrote:
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*pm, "ryan.paque...@gmail.com" <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 * * ....
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- Hide quoted text -

- Show quoted text -
Jan 8 '08 #3

P: n/a
On Jan 8, 2:39*pm, "ryan.paque...@gmail.com" <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.
Jan 8 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.