remove duplicate

[abraham30]

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.

[ranman256]

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.

[CJ_London]

Something like

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

[abraham30]

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

[CJ_London]

not sure what you mean. If you mean, can you add it to the same statement, then no, it is a separate query

[abraham30]

Thank you Ajax. I understood. It should be separate query