Question Preventing Duplicate data

[SteveGMC]

My normal access helper is away at the mo, so he's told me to ask here!

I have a training database that stores our employee's training records.

The main courses table has various fields that store data, two significant ones are CourseID and EmployeeID.

Now the little issue I have is when users are entering training data into the form, they have no way of telling easily if the Employee has taken that course before.

Currently the user selects the employee from a list then selects the courses they wish to add on a continous form. Before each update is there a way of checking that this isn't a duplicate entry?

 

[jdraw]

My normal access helper is away at the mo, so he's told me to ask here!

I have a training database that stores our employee's training records.

The main courses table has various fields that store data, two significant ones are CourseID and EmployeeID.

Now the little issue I have is when users are entering training data into the form, they have no way of telling easily if the Employee has taken that course before.

Currently the user selects the employee from a list then selects the courses they wish to add on a continous form. Before each update is there a way of checking that this isn't a duplicate entry?

Steve,

Do you have an Employee table, Course table and CousesTaken table?
If so, then in the CoursesTaken table, there should be a Primary Key of
EmployeeID + CourseId. A Primary Key will prevent the storage of duplicate records. In this case the Primary Key would be a "composite primary key".

When you have a Primary key, any attempt to store a duplicate record will cause Access to give a message indicating same, and won't allow the duplicate to be stored.

 

[SteveGMC]

Thanks!

Yes I do have Employee Table, Course and Taken table.

In the CousesTaken Table I have the fields EmployeeID and CourseId.

Looking around to find out what a composite primary key is, I see that I need to make the EmployeeID and CourseId fields in the Courses table both Primary Keys?

At the moment, the CourseTakenID field is a primary key with autonumber, does this mean that I remove this primary key and make the other two Primary keys, or just have all three as Primary keys?

 

[jdraw]

Thanks!

Yes I do have Employee Table, Course and Taken table.

In the CousesTaken Table I have the fields EmployeeID and CourseId.

Looking around to find out what a composite primary key is, I see that I need to make the EmployeeID and CourseId fields in the Courses table both Primary Keys?

At the moment, the CourseTakenID field is a primary key with autonumber, does this mean that I remove this primary key and make the other two Primary keys, or just have all three as Primary keys?

NO!
Don't change things at this point. A table can only have 1 primary key. However a primary key can be composed of multiple fields. If the normal access person is away, and that is the person who is in charge of design, then I'd say let him/her do the design change as appropriate.

The CourseTaken table is considered a junction table - a junction between Employee and Course. Normally the primary key would be a composite of EmployeeID and CourseId. For some reason, the "designer" has not done that, perhaps for good reason.

Can you tell us more about the form? Perhaps, we can add some code to check for the existence of that Employee Course combination before the record is saved to the table.

How many records in the CousesTaken table?

 

[boblarson]

I would add a DCount to check to see if the combination exists and then cancel if it does. You can either put it in the form's Before Update event or create a function in the form module and then call it in each of the two control's Before Update event. I lean to doing it on the control's before update events so that you catch it quickly before the user tries to save the record.

So, something like this:

Function Test4Dups(lngEmpID As Long, lngCourseID As Long) As Boolean
   Test4Dups = (DCount("*", "CourseTaken", "[EmployeeID]=" & lngEmpID & " AND [CourseID] = " & lngCourseID) > 0)
End Function

And then you call it like this:

Private Sub txtEmployeeID_BeforeUpdate(Cancel As Integer)
   If Test4Dups(Me!EmployeeID, Me!CourseID) Then
      Cancel = True
      MsgBox "Duplicate value", vbExclamation, "Error"
      Me.txtEmployeeID.Undo
   End If

and you would do the same in the CourseID text box or combo box, etc.

 

[SteveGMC]

Cheers all. The helper doesn't work with me, I just ask here and there when I can't work out how to do something. The current database isn't live as such, just started it to see if I could do it.

Can you explain what a 'composite' field is and how and where it gets the data from. I've searched around and I really can't find anything in simpler terms.

I have the employeeID and the courseID that as said before get stored to seperate fields in the CousesTaken table. Is the Composite field stored in the Coursestaken Junction Table (Thats a new term for me)

 

[Poppa Smurf]

You may need to include course date in your primary key if the employee is required to recertify in a qualification course e.g. first aid.

 

[jdraw]

Cheers all. The helper doesn't work with me, I just ask here and there when I can't work out how to do something. The current database isn't live as such, just started it to see if I could do it.

Can you explain what a 'composite' field is and how and where it gets the data from. I've searched around and I really can't find anything in simpler terms.

I have the employeeID and the courseID that as said before get stored to seperate fields in the CousesTaken table. Is the Composite field stored in the Coursestaken Junction Table (Thats a new term for me)

Steve, Seeing as this is new database; not yet in place; you can

1. Remove the CourseTakenId field from CourseTaken Table
2. Make the combination of EmployeeId and CourseId the primary key.

See Set or change the primary key (MDB) in MS Access help

Note: There is no composite field to add to the table. Composite just means there is more than one field making up the primary key.

As Pappa Smurf said - if people have to be retested, or could take the course multiple times, then you shoud include DateTaken in the primary key.

The code from Bob could be used to get immediate feedback for a potential duplicate while keying data on the Form.

 

[SteveGMC]

Thanks all for you help

I think I understand the concept of the composite field as I use something similar in Excel to vlookup a incoming DDI against a specific date.

What I really dont get is the concept of a composite field in an Access Table. The MS help file in 2007 explains a composite field in pretty much the same way as the posts above.

What I dont understand how the database see's two fields as being a primary key? Are they joined together somehow to create a the unique reference?

I know the field properties of say my CourseID field can be set to Unique Indexed etc, so how do I get two seperate fields to do this?

Searching the web, one poster said select two fields in the Table, then press the Key icon, this does appear to work and puts the key icon next to both fields but jdraw said No!

Is there a example database from Microsoft I can take a ponder at anywhere?

 

[jdraw]

Thanks all for you help

I think I understand the concept of the composite field as I use something similar in Excel to vlookup a incoming DDI against a specific date.

What I really dont get is the concept of a composite field in an Access Table. The MS help file in 2007 explains a composite field in pretty much the same way as the posts above.

What I dont understand how the database see's two fields as being a primary key? Are they joined together somehow to create a the unique reference?

I know the field properties of say my CourseID field can be set to Unique Indexed etc, so how do I get two seperate fields to do this?

Searching the web, one poster said select two fields in the Table, then press the Key icon, this does appear to work and puts the key icon next to both fields but jdraw said No!

Is there a example database from Microsoft I can take a ponder at anywhere?

Steve,

Searching the web, one poster said select two fields in the Table, then press the Key icon, this does appear to work and puts the key icon next to both fields but jdraw said No!


I said NO don't change anything before I found out their was no formal designer, and that you were just creating the database.

Do it as the other poster suggested.

Yes, a separate index is created in which the combination of field values are recorded.

 

[SteveGMC]

OK, cheers Jdraw. So doing that creates an index of the two fields, that in theory must be unique to every record.

Probably my screwup, but I think I know what has happened. Here's the current table setup.

courseID - autonumber
CourseName - Text

EmployeeID - Autonumber
EmpoyeeName - Text

Now i have some test data that was imported from an excel sheet which therefore generated the autonumber to start from 1, 2, 3, 4 etc on both tables.

So with the test data in the courses taken table I have entries such as this

CourseID | EmployeeID
---------   -----------
1                13
11               3

I'm taking a punt but Would this now be classed as a duplicate entry as both equal 113?

 

[jdraw]

OK, cheers Jdraw. So doing that creates an index of the two fields, that in theory must be unique to every record.

Probably my screwup, but I think I know what has happened. Here's the current table setup.

courseID - autonumber
CourseName - Text

EmployeeID - Autonumber
EmpoyeeName - Text

Now i have some test data that was imported from an excel sheet which therefore generated the autonumber to start from 1, 2, 3, 4 etc on both tables.

So with the test data in the courses taken table I have entries such as this

CourseID | EmployeeID
---------   -----------
1                13
11               3

I'm taking a punt but Would this now be classed as a duplicate entry as both equal 113?

No. As you can see (this is CoursesTaken table) there are 2 records. These are not duplicates.

If you create a query with tables
Employee
CourseTaken and Courses

And join the tables
Employee to CoursesTaken on EmployeeId
Course to CoursesTaken on CourseID

Then select for display

EmployeeName, CourseName you'll see the records in a more "readable form".

People don't usually talk in terms of EmployeeId and CourseId. They usually say some thing like "MIke Smith took the Introductory Programming Course".

And if you added the DateTaken to the CoursesTaken table, you should also display that.

 

[SteveGMC]

jdraw and everybody else, thank you.

I just spotted the Indexes button button in Design View that seemed to make things click.

Poppa Smurf suggested adding the date as part of the primary key? Wouldn't this then allow a duplicate course and employee to be added the next day?

The coursestaken table does already have a date field and the details of the trainer.

 

[boblarson]

You don't need to create a composite key to keep duplicates out. I normally use (and advocate the use of) a surrogate key (autonumber) and then if you want to ensure no duplicates at table level you can set a Multi-Field Index with the No Dups set.

 

[jdraw]

jdraw and everybody else, thank you.

I just spotted the Indexes button button in Design View that seemed to make things click.

Poppa Smurf suggested adding the date as part of the primary key? Wouldn't this then allow a duplicate course and employee to be added the next day?

The coursestaken table does already have a date field and the details of the trainer.

Steve,

If DateCourseTaken is part of the primary key, then if a person took exactly the same Course the next day, the record would not be a duplicate---
because the Date would be different.

If only EmployeeId and CourseId make up the primary key, AND if a personn took exactly the same Course the next day (or any day), the record would be a duplicate -- because EmployeeId and CourseID combination is already in the database.

If an Employee could take a given Course multiple times, then the Date field in your CoursesTaken table should be made part of the primary key.
Hopefully the name of the Date field in the table is not Date. make it something like DateTaken or SessionDate. Date is a reserved word in Access and could lead to other issues.

 

[SteveGMC]

You don't need to create a composite key to keep duplicates out. I normally use (and advocate the use of) a surrogate key (autonumber) and then if you want to ensure no duplicates at table level you can set a Multi-Field Index with the No Dups set.

Perfect That's makes everything very clear!

Steve,

If DateCourseTaken is part of the primary key, then if a person took exactly the same Course the next day, the record would not be a duplicate---
because the Date would be different.

If only EmployeeId and CourseId make up the primary key, AND if a personn took exactly the same Course the next day (or any day), the record would be a duplicate -- because EmployeeId and CourseID combination is already in the database.

If an Employee could take a given Course multiple times, then the Date field in your CoursesTaken table should be made part of the primary key.
Hopefully the name of the Date field in the table is not Date. make it something like DateTaken or SessionDate. Date is a reserved word in Access and could lead to other issues.

Thanks - I'll see how this plays out in the next few days.

As a side note, I've learn't more in this thread, than the whole of last week reading the Access book sitting on my desk. Cheers guys!

As for the Date field name, its got a unique name!