Results 1 to 6 of 6
  1. #1
    abraham30 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    3

    remove duplicate

    Hello Everyone

    I am totally new to the Access database process.

    In my project, I import two file (excel and html) generated from SAS application into access database (based on some requirement) on a daily basis and after that export the data into excel format.

    The main problem is that the exported excel sheet contain same records with different download date. Below example

    EID ENAME job download_date
    101 ABC clerk 12/01/2015 15:41:57
    101 ABC clerk 13/01/2015 15:41:57
    101 ABC clerk 18/01/2015 15:41:57

    I want to remove those duplicate records only if data in three fields (eid, ename and job) are same. For the above scenario, I want data with old download_date.




    Can anyone help me how to fix it.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,285
    Build a keyed table just like this one Except no date fld.
    key on all 3 fields.
    make an append query and add all records to the new tbl.
    then the new tbl will be duplicate free.

  3. #3
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,279
    Something like

    Code:
    DELETE *
    FROM myData LEFT JOIN (SELECT EID, ENAME, JOB, MIN(DownLoadDate) as MinDate FROM myData GROUP BY EID, ENAME, JOB) As M 
        ON myData.EID=M.EID AND myData.ENAME=M.ENAME AND mtData.JOB=M.JOB AND myData.DownloadDate=M>DownloadDate
    WHERE M.Downloaddate IS NULL

  4. #4
    abraham30 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    3
    Thank you both for your help.

    The database already contain SQL program (update statement) . Can I add the program below that and save it so that duplicate will be removed.
    Sorry! I ask because i have zero knowledge on access.

    e.g.

    update <myData> inner join <dept> on myData.deptno=dept.deptno and myData.stno=dept.stno set myData.control=dept.control .....where myData.control=2;


    DELETE *
    FROM myData LEFT JOIN (SELECT EID, ENAME, JOB, MIN(DownLoadDate) as MinDate FROM myData GROUP BY EID, ENAME, JOB) As M
    ON myData.EID=M.EID AND myData.ENAME=M.ENAME AND mtData.JOB=M.JOB AND myData.DownloadDate=M.DownloadDate
    WHERE M.Downloaddate IS NULL


    Thanks in advance

  5. #5
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,279
    not sure what you mean. If you mean, can you add it to the same statement, then no, it is a separate query

  6. #6
    abraham30 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    3
    Thank you Ajax. I understood. It should be separate query

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

Similar Threads

  1. Replies: 1
    Last Post: 03-10-2015, 11:47 AM
  2. Remove Duplicate Entries in Query Design
    By samanthaM in forum Access
    Replies: 1
    Last Post: 10-27-2013, 11:26 AM
  3. Remove duplicate but keep the latest record
    By Compufreak in forum SQL Server
    Replies: 2
    Last Post: 06-12-2013, 11:55 AM
  4. Replies: 5
    Last Post: 05-24-2013, 11:55 AM
  5. Remove Duplicate
    By rgparpaccessforums in forum Access
    Replies: 3
    Last Post: 04-24-2011, 07:24 PM

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 - Senior Forums