Help me, need to delete from table....

[arifmasum]

Dear Members,

I am stuck here, Please help me to get out of this.

I need to delete duplicate attendance date with records from the attached table.

Which method I should use to get the best result?

Thanks in advance for your time and reply.

Best regards,


Arif Masum

 

[jal]

Dear Members,

I am stuck here, Please help me to get out of this.

I need to delete duplicate attendance date with records from the attached table.

Which method I should use to get the best result?

Thanks in advance for your time and reply.

Best regards,


Arif Masum

I don't know the "best" method. But a standard solution is something like this (add an autoNum column)

DELETE FROM tblAttendance
WHERE autoNum NOT IN
(
SELECT MIN(AutoNum) FROM tblAttendance GROUP BY IdNo, Date
)

You can add the autoNum column like this:

ALTER TABLE tblAttendance ADD COLUMN autoNum COUNTER

 

[arifmasum]

Thank you very much for your time and answer. Can you please send me the sample file with the solution. I could understand that better then. Thanks a lot for helping me dear jal..

Best regards

Arif Masum

 

[arifmasum]

Please look at attached table

Please have a look at attached table. There you will see duplicate entry for date 16/05/08 to 18/05/08...

I need to keep the leave informaiton on that day...

Is it possible? then how?

Please help me out.

Thanks and best regards

Arif Masum

 

[jal]

Please have a look at attached table. There you will see duplicate entry for date 16/05/08 to 18/05/08...

I need to keep the leave informaiton on that day...

Is it possible? then how?

Please help me out.

Thanks and best regards

Arif Masum

In other words these are not really duplicate records. The dates are dup, but there may be other fields which are not duplicated. Therefore, now you are talking about combining any number of non-dup records into one summary record.

That's a heck of a lot more complicated than what you originally asked. I normally would do this sort of thing in VB.Net since I don't know VBA. I don't have time to help you with this right now.

 

[jal]

Well, just thought of something. Don't know if it will work.
Why not do a self Join on IdNO and DAte and have the Where clause check for nulls on any desired fields? In the case of a null, set the value of that field to the value of the "other table's field" (i.e. the copy of the table being joined).

 

[arifmasum]

Thanks

Thanks my dear friend Jal...

you helped me a lot to get ideas related to access...

Hope i will be able to solve this problem.

Best regards.

Arif Masum