473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Jan 8 '08 #1
3 2813
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: woody at nfri dot com | last post by:
I have a database, about 12 fields per record and roughly 100,000 records per month. I would do the following query: select count(*), type from TABLENAME where field0='23-23-23' group by...
1
by: A.M. de Jong | last post by:
Can I dynamically (from a stored procedure) generate a create table script of all tables in a given database (with defaults etc) a create view script of all views a create function script of all...
2
by: Mansoor Azam | last post by:
I have the following table with indexes CREATE TABLE dbo.Scratch ( ItemID int IDENTITY (1, 1) NOT NULL , Login varchar (12) NOT NULL , StartDate datetime NULL , PayDate datetime NULL ,...
6
by: WindAndWaves | last post by:
Should I index fields that are linked to another table??? Note: - all links are ref. enforced - all links are one-to-many - I have some links of type A small list with a byte ID (of which...
11
by: Jerry | last post by:
Say a table has four fields: ID (PK) Field1 Field2 Field3 Field 1 is indexed as Yes(No Duplicates) When a duplicate is entered in Field1, nothing happens until you try to save the record then...
3
by: lauren quantrell | last post by:
In a table design, properties window there is the Indexes/Keys tab. I want to create a few indexes. Row myID is the PK. I also want indexes on rows myOne, myTwo, myThree. In the selected...
2
by: bobby_b_ | last post by:
I have a table where fields 1 and 2 make up the primary key. Because of this, I have a unique composite index on fields 1 and 2 (as required by DB2). Now my question is: Fields 1 and 2 are also...
1
by: cefrancke | last post by:
I'm trying to insert records in a table that has a "no duplicates" index on two columns. The following snippet shows where I started off... INSERT INTO Table_A (ID_A, ID_B) VALUES (334, 2057)...
22
by: chromis | last post by:
Hi, I'm trying to display a table of highscores, but I don't want to display more than one highscore from the same person aka duplicates in the name column. I tryed this: $sql = "SELECT...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.