Results 1 to 5 of 5
  1. #1
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25

    Identiffy duplicates before copying to table

    Hi,
    I have looked on the site but can't find any other posts that can help.
    I have a form to collect data for a table which includes a subform to collect for another linked table.


    The form collects information about training within company.
    The main form collects the training course details while the subform collects the names of the employees that are doing the training
    What I need is to be able to identify if someone has already done this training before it updates the tables.
    I have looked at trying to index but not too sure about this as none of the records could be seen as unique within the fields
    Employees can have more than one training record, training records have different revision numbers and levels ( eg. Fred B trained on latest fork lift training version 3 at level 2 of competency)
    I have looked at DCount to find where the whole record is unique but that does not seem to give me anything.
    I hope what I have said makes sense.
    Honey2wood

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make a temp table that is keyed on your field you don't want duplicates.
    add the data to the table, the dupliates will be ignored.

    add the temp table to the main table OR use an identifier to delete items NOT in temp (the dupes) to remove them from the main table.

  3. #3
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Quote Originally Posted by ranman256 View Post
    Make a temp table that is keyed on your field you don't want duplicates.
    add the data to the table, the dupliates will be ignored.

    add the temp table to the main table OR use an identifier to delete items NOT in temp (the dupes) to remove them from the main table.
    Hi
    Thanks for getting back to me. thats very kind. Im not quite sure about what you are saying. The temp table to be included in the query?
    Sorry for being so thick. Im not a newbie but sometimes I just cant quite understand.
    Cheers
    Graham

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Graham,

    You haven't described your underlying table design(s) in detail. Based on your post, I have attached a draft model of the tables typically involved in Employee Training. Your set up may be different depending on the details in your company.
    Click image for larger version. 

Name:	Employee_Courses.PNG 
Views:	44 
Size:	14.3 KB 
ID:	40047
    In your data entry routine, you could check and see if this Employee has already taken this Course.
    Code:
     If Dcount("*","EmployeeTookCourse","CourseID=" & yourCourseFormControl & " AND EmployeeID =" & yourEmployeeFormControl ) >0 Then
    msgBox "That Employee - Course combination already exists", vbOKOnly
    Also, you can place a unique composite index on fields CourseID, EmployeeID in table "EmployeeTookCourse". The database will check for duplicates when this combination is being stored. You can check for error 3022 and reject the storage attempt.
    Good luck with your project.

  5. #5
    honey2wood is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    25
    Hi Orange,
    Yes this is something like what I have. I have the table of Procedures which give the Procedure number and Revision number. I also have the Employee table with their number.
    At the moment details are entered into a form that gives Procedure and Revision no. etc with a sub form for the Employees doing that particular training.
    They are combined together in a 'makeTable' query which gives a full training table with names against procedures. It was done this way as there can be more than one employee per training course.
    I have yet to try the indexing.
    You say at the beginning that you give 'typical Employee Training' tables. I have tried to find a 'standard' employee training database to give me inspiration but this is the first time someone has included a competency level which I have been trying to include in my database. But that's a different issue Graham

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copying records from one table to another
    By willster88 in forum Access
    Replies: 2
    Last Post: 03-22-2014, 01:57 PM
  2. Copying tabular data into a table
    By Jennifer Murphy in forum Access
    Replies: 3
    Last Post: 02-02-2014, 05:42 PM
  3. Copying Table Headers
    By djohnson1 in forum Access
    Replies: 1
    Last Post: 01-09-2014, 10:25 AM
  4. Replies: 5
    Last Post: 12-08-2011, 10:52 AM
  5. Replies: 1
    Last Post: 06-08-2011, 02:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums